Excel
ExcelのSUMIF(S)関数を使って指定した日付、あるいは指定した期間に含まれる日付に対応する数値の合計を求める方法について紹介します。 さらに応用として、指定した年や月（年及び月）に含まれる日付に対応する数値の合計を求める例をいくつか紹介します。 …
ExcelのCOUNTIF(S)関数を使って指定した日付、あるいは指定した期間に含まれる日付をカウントする方法についてです。 応用として指定した年や月（年及び月）に含まれる日付をカウントする例についても紹介します。 特定の日付をカウントする 特定の日以降・…
ExcelのCOUNTIF関数を使って空白のセルまたは空白以外のセルをカウントする方法についてです。 式は難しくはなものの、空白セルと「空白セルに見えて異なるセル」の違いを把握することが重要になります。 空白セルをカウントする 空白セル以外をカウントする…
Excelで、テキスト中の複数種類の文字列をすべて削除する方法です。 置換と同様にSUBSTITUTE関数を使う方法と、TEXTSPLIT関数を使った方法を紹介します。 SUBSTITUTE関数をネスト（入れ子）する方法 SUBSTITUTE関数とREDUCE/LAMBDA関数を使う方法 TEXTSPLIT…
ExcelのCOUNTIF関数ではワイルドカードを使って該当する値をカウントすることができますが、基本的に数値（数字）や日付にワイルドカードを適用することはできません。 以下ではこれを何とかする方法について紹介します。 作業列を追加して数値や日付を文字…
ExcelのCOUNTIF関数で条件を指定する際にワイルドカードを使う例についてです。「～を含む文字列」とか「～で終わる文字列」といった条件に対応する文字列をカウントすることができ、あいまい検索などとも呼ばれます。使い方によっては複雑なカウントも簡単…
ExcelのXLOOKUP関数で「～以上～以下」や「～超～未満」の数値・日付を検索する方法についてです。 第5引数（一致モード）の設定により検索値を分類・ランク分けするのではなく、XLOOKUP関数内で最小値と最大値の両方を指定するというのが趣旨です。 実用的…
ExcelのXLOOKUP関数ではワイルドカードを使った検索ができますが、基本的に検索対象が数値や日付の場合は検索できません。 そこで少し工夫して検索できるようにする例を紹介します。 XLOOKUP関数の第2引数内で検索対象を文字列に変換してしまえばワイルドカ…
Excelで、条件に一致する文字列（条件に一致する行にある文字列）を結合する方法についてです。 よく「SUMIFで文字列を結合」などと例えられますがSUMIFではできないので、別の関数を使う必要があります。 FILTER関数とCONCAT関数（区切り文字を入れる場合は…
Excelで、月の最後の平日（土日祝日以外）や最初の平日の日付を求める方法についてです。 いくつかのパターンに分けて紹介します。 WORKDAY関数などの関数を使って、月の最後・最初の平日の日付を求めることができます。 最後の平日を求める方法 日付から求…
ExcelのSUMIF関数では条件の指定にワイルドカードが使えます。あいまい検索などとも呼ばれ、「～を含む文字列」とか「～で終わる文字列」といった条件に対応する数値の合計を求めることができます。使い方によっては複雑な集計も簡単にできるので、やり方を…
ExcelのVLOOKUP関数の活用例についてです。この関数自体には集計を行う（合計を求める）機能はありませんが、集計の過程で表引きを要する際には便利に活用できます。 手順 ここで想定する集計（合計を求める）の内容ですが、商品一覧と注文一覧の各データに…
Excelで、月末や月初（1日）の日付を求める方法についてです。いくつかのパターンに分けて紹介します。 EOMONTH関数を使う方法やDATE関数を使う方法で、月末や月初の日付を求めることができます。 月末の日付を求める方法 日付から求める場合 年,月の値から…
ExcelのXLOOKUP関数の検索値の判定についてです。 XLOOKUP関数はVLOOKUP関数と異なり検索値の全角と半角を区別しません（同一視する）。大文字と小文字を区別しない点は同じです。 XMATCH関数についても同様です。手順 次の画像ではE3セルに入力されている全…
Excelで数値のべき乗を求める場合は^演算子やPOW関数が使えますが、行列の場合にはそれらのようなものがありません。 そこでMMULT関数を応用して行列のべき乗を求める計算を行ってみます。ただし可能なのは整数乗のみです。 REDUCE/LAMBDA関数とMMULT関数を…
ExcelのVLOOKUP関数で、AND条件（かつ）やOR条件（または）による検索・抽出をする方法についてです。 ただしXLOOKUP関数やINDEX/MATCH関数でより簡単に同じことができるため実用性は薄いです。 AND条件（かつ）の場合 OR条件（または）の場合 1つの列に複数…
Excelでワイルドカードを使って1文字以上とか2文字以上の文字列を指定する方法についてです。 1つの「*」とn個の「?」を並べれば「n文字以上の任意の文字列」にマッチ（該当）するワイルドカードになります。「*」の位置はどこでもいいです。 手順 備考 手順…
ExcelのVLOOKUP関数で、検索値の大文字と小文字を区別して検索する方法についてです。 ただしあまり実用的とは言えないため、より簡単なXLOOKUP関数やINDEX/MATCH関数を使う方法をお勧めします。 VLOOKUP関数とHSTACK/EXACT関数を併用することで、大文字と小…
ExcelのXLOOKUP関数では、検索値（第1引数）を大文字で入力しても小文字で入力してもその違いを無視して検索するため、意図しない結果となる場合があります。 そこで、式を少し工夫して大文字と小文字を区別して検索する方法を試してみます。 XLOOKUP関数とE…
Excelの関数の応用例として「INDEX関数とMATCH関数の組み合わせによる表引き」というのがありますが、ここでは検索値が複数列の値のいずれかに該当する場合に対応する値を返す方法を紹介します。 +演算子を利用してMATCH関数の検索範囲に複数列を指定した結…
ExcelのINDEX関数とMATCH関数を使って、AND条件（かつ）やOR条件（または）に該当する値を検索・抽出する方法です。AND条件の一種であるクロス抽出についても紹介します。 MATCH関数の第1,2引数（検索値,検索範囲）で複数列の値を&でつないで指定することで…
Excelで、関数を使ってエラーのセルの数をカウントする方法についてです。 ISERROR関数とSUM関数等の集計関数を組み合わせてエラーの数をカウントできます。 SUM/ISERROR関数等を使う方法 SUMPRODUCT/ISERROR関数を使う方法 SUM/ISERROR関数等を使う方法 次…
Excelで関数を使い、指定された月のすべての平日（土日祝日以外）の日付を1列に表示する方法についてです。 各セルに式を配置する方法と、SEQUENCE関数を使って1つの式で出力する方法を紹介します。 出力の内容と表示形式の設定について 各セルに式を入力す…
Excelで、与えられた日付が土・日曜や祝日に該当するかあるいは平日かを判定して表示する方法についてです。 土日祝日or平日の判定だけでなく、土日or祝日or平日の判定などのパターンに分けて紹介します。 判定の内容 土日祝日or平日の判定 土日or祝日or平日…
ExcelのSUMPRODUCT関数は積和（複数の掛け算の合計）を求める関数ですが、条件付きの合計値を求めるのに使われることがあります。ここではSUMPRODUCT関数で複数条件を指定して合計を求める方法を紹介します。 SUMPRODUCT関数と*演算子を使ってAND条件（かつ…
Excelで関数を使い、指定された月のすべての日付を1列に並べて表示する方法についてです。 各セルに式を配置する方法と、SEQUENCE関数を使って1つの式で出力する方法を紹介します。前者の場合は翌月の日付を出さない工夫が必要ですが、その方法も含めて紹介…
Excelで値を検索・抽出するのにINDEX関数とMATCH関数を併用するという方法がよく用いられますが、基本的に条件に該当する1つ（1行）の値しか抽出できません。そこで条件に該当するすべての値を抽出する例を試してみます。n番目の指定も可能です。 ただし複雑…
Excelで、列の位置を表すアルファベットを数値（数字）に変換する方法についてです。 COLUMN関数とINDIRECT関数を組み合わせて、A～XFDのアルファベット（文字列）を数値に変換できます。手順 次の画像では、B列にアルファベットが入力されています。 一方で…
Excelで数値（数字）を、列番号を表すアルファベットに変換する方法についてです。 ADDRESS関数などの関数を組み合わせて、1～16384の数値（数字）を列のアルファベット（文字列）に変換できます。手順 次の画像では、B列に数値が入力されています。 そしてC…
Excelでセルを参照するときの参照文字列（セル番地）の形式としてA1形式とR1C1形式がありますが、それらの文字列を関数を使って互いに変換する方法についてです。 INDIRECT関数等を使って、A1形式とR1C1形式の参照文字列（セル番地）を互いに変換できます。 …
