エクセルの神髄
エクセル関数応用

Excelの関数解説、関数サンプルと高等テクニック、記事の目次です
最終更新日:2024-01-19

エクセル関数応用


エクセルの関数の応用技の解説。
関数サンプルと必須の基本技術から応用・高等テクニックまでを紹介しています。


1年後の日付、○か月後の日付
起算日からの一年間の最後の日の求め方です、起算日が2011/4/1なら、2012/3/31の求め方です。セルA1に起算日が入っており、セルA2に一年間の最終日を設定する場合。一年後の日付 よくみかけるのは、=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 DATEは、DATE(年,
複数条件の合計・件数
・サンプルデータ ・複数条件の合計 ・複数条件の件数 ・スピルと新関数
入力規則のリストを、追加・削除に自動対応で作成
一覧リストを使用して、「入力規則」の「リスト」を作成し、さらに、一覧リストへの追加・削除に自動対応させるものです。シート「リスト」の部署を使用して、シート「入力」に入力規則のリストを範囲可変で作成します。名前を定義します。
入力規則のリストを、2段階の絞り込みで作成1
一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、さらに、一覧リストへの追加・削除に自動対応させるものです。入力規則のリストを、追加・削除に自動対応で作成 を理解した上でお読みください。
入力規則のリストを、2段階の絞り込みで作成2
一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、「入力規則のリストを、2段階の絞り込みで作成1」こちらのリストの縦横を入れ替えたパターンになります。入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リ…
ピポットテーブルの参照範囲を、追加・削除に自動対応で作成
ピボットテーブルの参照範囲を可変に設定し、データの追加・削除に自動で対応させます。以下の表で説明します。名前を定義します。シート「リスト」を選択します。Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。
関数を使って行列を入れ替える方法
行列を入れ替える方法として、一般には、「形式を選択して貼り付け」ですが、これでは、元のデータを更新しても入れ替え先にデータ反映されません。そこで、関数を使って行列を入れ替えることにより、直接データが反映するようにします。以下の表で説明します。
複数条件で検索し、複数データを取得する方法
各種条件でデータを絞る場合は、オートフィルタが一般的に使用されますが、元のデータがあるシートで作業する為、間違ってデータを消してしまったり、使用する上で制約があります。また、VLOOKUPでは、単一条件のみであり、しかも1件しか取得できません。
関数で銀行型丸め(最近接偶数への丸め)を行う方法
エクセルの関数で、ROUNDは四捨五入です。しかし、VBAでのRoundは、銀行型丸め(最近接偶数への丸め)で、Accessも銀行型丸めとなっています。四捨五入では、どうしても大きくなる傾向があるようです。
関数で他シートへ並べ替える方法(サンプル:ABC分析)
通常、並べ替えは元データが変更されれば、毎回、並べ替えを行う必要がありますが、, これが、結構面倒な場合もあります。出来れば、マクロを組みたいところですが、マクロはちょっとと言う人もいるでしょう。そこで、関数で他シートへ並べ替える方法です。
VLOOKUPを他の関数でやる方法

・VLOOKUP関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所
誕生日一覧から、指定誕生月の人を全員取り出す
配列数式を使って、誕生日が指定月の該当者全員を取り出します。各種条件でデータを絞る場合は、オートフィルタが一般的に使用されますが、日付の中の、月だけの指定になると、お手上げです。(出来ない事はありませんが、かなり面倒です。
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
・解説に使うシート ・VLOOKUP関数 ・INDEX関数とMATCH関数の組み合わせ ・OFFSET関数とMATCH関数の組み合わせ ・VLOOKUPを他の関数でやる方法 ・徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)の最後に
セルの参照範囲を可変にする(OFFSET、COUNTA、MATCH)
・説明で使用するエクセル表 ・OFFSET関数 ・MATCH関数 ・COUNTA関数 ・セルの参照範囲を可変にする数式の解説 ・全合計(E3)の数式 ・開始日(E4)と終了日(E5)に名前定義とリストを設定 ・期間合計(E5)の数式 ・短く易しい数式で
グラフのデータ範囲を可変にする
データの範囲に合わせて、自動的にグラフのデータ範囲が変更されるようにします。グラフのデータ個数が増えるたびに、「データの選択」(2003は元データ)を変更するのは、いかにも面倒です。できれば、マクロでやりたいところですが、マクロはちょっという人用に解説します。
セルの個数を数える関数
セルの個数を数える関数を解説します COUNT関数 COUNTA関数 COUNTBLANK関数 COUNTIF関数 になります。以下の表で説明します。COUNT関数 これは、数値のみカウントします。セルの書式や、先頭に「'」等があっても数値ならカウントされます。
【奥義】大量データでの高速VLOOKUP
・高速VLOOKUPに使用するサンプルデータ ・高速VLOOKUPの数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
数値を時刻に変換
・数値を時刻に変換:方法1 ・数値を時刻に変換:方法2 ・数値を時刻に変換:方法3 ・数値を時刻に変換の注意点
関数のネスト方法
関数のネスト(入れ子)をする場合の、考え方と作成方法です。IF関数のネスト作成方法 A列の数値によって、B列に"A"から"E"の評価を入れます。・80以上はA ・60以上はB ・40以上はC ・20以上はD ・20未満はE この場合は、まず、=IF(A1>=80,
ワイルドカードが使える関数
・ワイルドカードについて ・データベース関数を除くExcel2003までの関数 ・データベース関数を除くExcel2007以降 ・2020年3月現在のOffice365 ・ワイルドカードの使用例
グラフで特定の横軸の色を変更し基準線を引く
特定の横軸のみ色を変更する、つまり、基準値や下限・上限等に線を引く場合の手順です 基準値や、下限・上限等に線を引きたい場合になります。結構面倒なので、いざやろうとすると、なかなか出来なかったりしますので、自分自身の覚書を兼ねて掲載しておきます。
OFFSET関数 解説・応用・使用例
OFFSET関数は、検索ワードで最頻出のひとつです。他の関数とは、かなり異質に感じるのかもしれません。機能 基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル範囲の参照を返します。
MATCH関数 解説・応用・使用例
MATCH関数は、検索ワードで最頻出のひとつです。非常に便利な関数です。少し込み入った事を関数でやろうとした時は、必ず必要になる関数です 機能 セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)

クリックまたはカーソルキーで選択セルを移動した場合に、当該行の色を変更して目立たせたせる方法で、条件付き書式と、シートのイベントであるWorksheet_SelectionChangeを使用します。Worksheet_SelectionChangeイベントのみでやろうとすると、直前の選択行の色を元に戻す必要がある為、
他ブックを参照できる関数、他ブックを参照できない関数
・他ブックを参照できる関数、他ブックを参照できない関数 ・SUMPRODUCT関数:後日追記 ・他ブックを参照できる関数、他ブックを参照できない関数のまとめ ・テーブル構造化参照では他ブックは取得できません ・他ブックを参照する最も簡単な方法 ・配列数式を使って、他ブックを参照する ・他ブックを参照することについて
時間計算で困ったときの確実な対処方法
・日付・時刻のシリアル値とは ・Excelにおける小数の問題 ・どんな時に問題が発生するか ・確実な時間計算方法 ・TIME関数の制限について ・単純化した結論
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得
SUMIF関数の良くある間違い
エクセルの関数の中で最も頻繁に使われる関数と言っても過言ではないSUMIF関数ですが、間違った指定をして、合計が合わずに悩み続けて時間を浪費してしまうことあります、そういう間違いで最も多いのが、範囲と合計範囲の指定間違いです。まずは、SUMIF関数の確認 SUMIF関数 範囲の中で、指定した条件を満たすセルの値を合…
論理式とは条件式とは(IF関数,AND関数,OR関数)
エクセルを使いこなす上で論理式はとても重要です、そもそも論理式とは何か、どうして論理式というのか、論理式の作り方、論理式の使い方について解説します。そもそも論理式という言い方が分かりずらいと思う。なぜエクセルでは論理式というのか… Microsoftのヘルプによると、IF関数 構文:IF(logical_test,
先頭の数値、最後の数値を取り出す
数値と文字が混在した文字列から、数値だけを取り出します、先頭の数値や、最後の数値だけを取り出す方法です。A1セルに 1234abcd5678 このA1セルから、1234や5678を取り出します。先頭の数値…1234 =LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) COLUMN($1:…
最後の空白(や指定文字)以降の文字を取り出す
いくつかのスペースやハイフンで区切られた文字列から、最後のスペースやハイフン以降の文字列を取り出します。A1セルに、abcdefghi や abc-def-ghi これらの文字列から、ghiを取り出します。以下では、見やすいように区切り文字は"-"で説明します。
SUMIFの間違いによるパフォーマンスの低下について
再計算が終わらない… そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。単純な四則演算が遅いという事はありません、それはもうPCの問題です。時間のかかる計算としては、大量データの集計計算を多数使っている場合です。
数値範囲で表検索するVLOOKUP近似一致
・表をエクセルで扱えるように調整する ・VLOOKUP関数の近似一致 ・VLOOKUP近似一致がどのように値を探しているか ・VLOOKUP近似一致の表を作成する場合の注意点 ・VLOOKUP関数に関する参考ページ
連続数値部分を取り出し記号で連結
・作成する関数の仕様 ・スピル前:2019永続版まで ・スピル後:Office365 ・LET関数:Office365 Insider 2020年3月現在 ・関数の作成手順 ・LET関数の作成手順
指数近似/対数近似/累乗近似(掲載順位とCTR)
・Google Search Consoleから:掲載順位とCTRデータを取得 ・散布図と近似曲線 ・X軸Y軸を入れ替えて散布図を作成 ・適切な近似曲線の選択について ・指数近似を数式で求める ・関数の説明 ・掲載順位に対するCTRを求める ・指数近似/対数近似/累乗近似(掲載順位とCTR)の最後に
ジャンプ・セル選択の応用(空欄を埋める、1行置きに挿入)
ジャンプ機能は、指定のセルにジャンプする機能です。このジャンプ先の指定方法が豊富に用意されていて、その応用範囲はとても広いです。以下の2通り、・表の同上で空欄になっているセルを埋める ・表に1行おきにきに空白行を挿入する 動画と簡単な解説で紹介します。
累計を求める数式あれこれ
・例題サンプル ・SUM関数:順に足していく ・SUM関数:絶対参照と相対参照で範囲をずらす ・SUM関数:OFFSET関数で範囲をずらす ・SUM関数:INDIRECT関数で範囲をずらす ・SUMIFS関数:当該行より前の行を条件指定 ・SUM+IF関数:SUM+IFの配列数式(CSE)で ・SUMPRODUCT関数:当該行より前の行を条件指定 ・FILTER関数:当該行より前の行を条件指定 ・SUMIFS関数:スピル ・SCAN関数:LAMBDA関数内でSUM
入力規則で○△を入れる数を制限する方法|エクセル雑感
・問題を出したツイート ・回答をお寄せくださったツイート ・用意した解答




新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ