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

起算日からの一年間の最後の日の求め方です、起算日が2011/4/1なら、2012/3/31の求め方です。セルA1に起算日が入っており、セルA2に一年間の最終日を設定する場合。一年後の日付 よくみかけるのは、=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 DATEは、DATE(年,

・サンプルデータ ・複数条件の合計 ・複数条件の件数 ・スピルと新関数

一覧リストを使用して、「入力規則」の「リスト」を作成し、さらに、一覧リストへの追加・削除に自動対応させるものです。シート「リスト」の部署を使用して、シート「入力」に入力規則のリストを範囲可変で作成します。名前を定義します。

一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、さらに、一覧リストへの追加・削除に自動対応させるものです。入力規則のリストを、追加・削除に自動対応で作成 を理解した上でお読みください。

一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、「入力規則のリストを、２段階の絞り込みで作成1」こちらのリストの縦横を入れ替えたパターンになります。入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リ…

一覧リストを使用して、「入力規則」の「リスト」を２段階で絞り込めるように作成します。１段目のリスト選択により、２段目のリストが自動変化します。以下の記事で紹介している内容と同じです。入力規則のリストを、2段階の絞り込みで作成1 入力規則のリストを、2段階の絞り込みで作成2一覧リストを使用して、「入力規則」の「リスト…

ピボットテーブルの参照範囲を可変に設定し、データの追加・削除に自動で対応させます。以下の表で説明します。名前を定義します。シート「リスト」を選択します。Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。

行列を入れ替える方法として、一般には、「形式を選択して貼り付け」ですが、これでは、元のデータを更新しても入れ替え先にデータ反映されません。そこで、関数を使って行列を入れ替えることにより、直接データが反映するようにします。以下の表で説明します。

各種条件でデータを絞る場合は、オートフィルタが一般的に使用されますが、元のデータがあるシートで作業する為、間違ってデータを消してしまったり、使用する上で制約があります。また、VLOOKUPでは、単一条件のみであり、しかも１件しか取得できません。

エクセルの関数で、ROUNDは四捨五入です。しかし、VBAでのRoundは、銀行型丸め（最近接偶数への丸め）で、Accessも銀行型丸めとなっています。四捨五入では、どうしても大きくなる傾向があるようです。

通常、並べ替えは元データが変更されれば、毎回、並べ替えを行う必要がありますが、, これが、結構面倒な場合もあります。出来れば、マクロを組みたいところですが、マクロはちょっとと言う人もいるでしょう。そこで、関数で他シートへ並べ替える方法です。

・VLOOKUP関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所

配列数式を使って、誕生日が指定月の該当者全員を取り出します。各種条件でデータを絞る場合は、オートフィルタが一般的に使用されますが、日付の中の、月だけの指定になると、お手上げです。（出来ない事はありませんが、かなり面倒です。

・解説に使うシート ・VLOOKUP関数 ・INDEX関数とMATCH関数の組み合わせ ・OFFSET関数とMATCH関数の組み合わせ ・VLOOKUPを他の関数でやる方法 ・徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)の最後に

・説明で使用するエクセル表 ・OFFSET関数 ・MATCH関数 ・COUNTA関数 ・セルの参照範囲を可変にする数式の解説 ・全合計（E1）の数式 ・開始日(E3)と終了日(E3)に名前定義とリストを設定 ・期間合計(E5)の数式 ・短く易しい数式で

データの範囲に合わせて、自動的にグラフのデータ範囲が変更されるようにします。グラフのデータ個数が増えるたびに、「データの選択」（2003は元データ）を変更するのは、いかにも面倒です。できれば、マクロでやりたいところですが、マクロはちょっという人用に解説します。

セルの個数を数える関数を解説します COUNT関数 COUNTA関数 COUNTBLANK関数 COUNTIF関数 になります。以下の表で説明します。COUNT関数 これは、数値のみカウントします。セルの書式や、先頭に「'」等があっても数値ならカウントされます。

・高速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関数は、検索ワードで最頻出のひとつです。他の関数とは、かなり異質に感じるのかもしれません。機能 基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル範囲の参照を返します。

MATCH関数は、検索ワードで最頻出のひとつです。非常に便利な関数です。少し込み入った事を関数でやろうとした時は、必ず必要になる関数です 機能 セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。

クリックまたはカーソルキーで選択セルを移動した場合に、当該行の色を変更して目立たせたせる方法で、条件付き書式と、シートのイベントであるWorksheet_SelectionChangeを使用します。Worksheet_SelectionChangeイベントのみでやろうとすると、直前の選択行の色を元に戻す必要がある為、

・他ブックを参照できる関数、他ブックを参照できない関数 ・SUMPRODUCT関数：後日追記 ・他ブックを参照できる関数、他ブックを参照できない関数のまとめ ・テーブル構造化参照では他ブックは取得できません ・他ブックを参照する最も簡単な方法 ・配列数式を使って、他ブックを参照する ・他ブックを参照することについて

・日付・時刻のシリアル値とは ・Excelにおける小数の問題 ・どんな時に問題が発生するか ・確実な時間計算方法 ・TIME関数の制限について ・単純化した結論

・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得

エクセルの関数の中で最も頻繁に使われる関数と言っても過言ではないSUMIF関数ですが、間違った指定をして、合計が合わずに悩み続けて時間を浪費してしまうことあります、そういう間違いで最も多いのが、範囲と合計範囲の指定間違いです。まずは、SUMIF関数の確認 SUMIF関数 範囲の中で、指定した条件を満たすセルの値を合…

エクセルを使いこなす上で論理式はとても重要です、そもそも論理式とは何か、どうして論理式というのか、論理式の作り方、論理式の使い方について解説します。そもそも論理式という言い方が分かりずらいと思う。なぜエクセルでは論理式というのか… 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を取り出します。以下では、見やすいように区切り文字は"-"で説明します。

再計算が終わらない… そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。単純な四則演算が遅いという事はありません、それはもうPCの問題です。時間のかかる計算としては、大量データの集計計算を多数使っている場合です。

・表をエクセルで扱えるように調整する ・VLOOKUP関数の近似一致 ・VLOOKUP近似一致がどのように値を探しているか ・VLOOKUP近似一致の表を作成する場合の注意点 ・VLOOKUP関数に関する参考ページ

・作成する関数の仕様 ・スピル前：2019永続版まで ・スピル後：Office365 ・LET関数：Office365 Insider 2020年3月現在 ・関数の作成手順 ・LET関数の作成手順

・Google Search Consoleから:掲載順位とCTRデータを取得 ・散布図と近似曲線 ・X軸Y軸を入れ替えて散布図を作成 ・適切な近似曲線の選択について ・指数近似を数式で求める ・関数の説明 ・掲載順位に対するCTRを求める ・指数近似/対数近似/累乗近似（掲載順位とCTR）の最後に

ジャンプ機能は、指定のセルにジャンプする機能です。このジャンプ先の指定方法が豊富に用意されていて、その応用範囲はとても広いです。以下の2通り、・表の同上で空欄になっているセルを埋める ・表に1行おきにきに空白行を挿入する 動画と簡単な解説で紹介します。

・例題サンプル ・SUM関数：順に足していく ・SUM関数：絶対参照と相対参照で範囲をずらす ・SUM関数：OFFSET関数で範囲をずらす ・SUM関数：INDIRECT関数で範囲をずらす ・SUMIFS関数：当該行より前の行を条件指定 ・SUM+IF関数：SUM+IFの配列数式（CSE）で ・SUMPRODUCT関数：当該行より前の行を条件指定 ・FILTER関数：当該行より前の行を条件指定 ・SUMIFS関数：スピル ・SCAN関数：LAMBDA関数内でSUM

・問題を出したツイート ・回答をお寄せくださったツイート ・用意した解答

・QRコード：Microsoft BarCode Control ・QRコード：IMAGE関数+API（api.qrserver.com） ・QRコード：IMAGE関数+API（api.excelapi.org） ・QRコード：PY関数（qrcode） ・バーコード：Microsoft BarCode Control ・バーコード：IMAGE関数+API（bwipjs-api.metafloor.com）

・ステップ1：スピルなし（従来の集計） ・ステップ2：スピルあり（Excel 2021以降） ・ステップ3：GROUPBY関数（2025/9月時点で365限定）

・セル参照を戻す（返す）数式の使いどころ ・関数の戻り値が参照かどうかの判定 ・セル参照を返す関数の一覧

・LAMBDAヘルパー関数について ・REDUCE+VSTACKのサンプル ・REDUCE+VSTACKが遅い理由と解決方法 ・ブロック・リダクションの実例

・テストデータ ・テストに使用したVBA ・2列で10万件の抽出 ・2列で1件の抽出 ・3列で10万件の抽出 ・3列で1件の抽出 ・まとめ

・説明に使用したサンプルデータについて ・MEDIAN関数＋IF関数 ・MEDIAN関数＋FILTER関数 ・MEDIAN関数＋FILTER関数＋LAMBDA関数 ・LAMBDA関数＋イータ縮小ラムダでさらなる汎用化

直積（デカルト積、クロスジョイン）とは、複数のデータ集合（リストやテーブル）の要素をすべて組み合わせてできる新しい集合のことです。簡単に言うと、「ありとあらゆる組み合わせのリスト」です。以下の数式は、あくまで1例です。

フィボナッチ数列、トリボナッチ数列、テトラナッチ数列は、いずれも前の項の和によって次の項が定義される数列ですが、何項前までの和を取るかが異なります。以下で紹介している数式は、あくまで1例です。いろいろな数式の書き方があるので、ぜひ挑戦してみてください。

・元表データ ・抽出条件の詳細仕様 ・複数条件による複合抽出 (横AND/縦OR) の数式

・配列を自在に回転させる数式の全コード例 ・数式の使い方 ・数式の解説

掛け算(*)を使わずに掛け算をする数式を作成してください。足し算(+)を使わずに足し算をする数式を作成してください。計算可能な桁数は指定しません。たぶん、作成する数式によって様々な制限が出てくると思います。

・検証に使用したデータとVBA ・VLOOKUP/XLOOKUPのパフォーマンス比較テスト ・パフォーマンス異常を発生させる条件 ・非スピル数式（結果が単一値）の場合は？ ・発生メカニズムの技術的考察