Excel
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形式の参照文字列（セル番地）を互いに変換できます。 …
ExcelのXLOOKUP関数で、検索値が複数列の値のいずれかに該当する場合に対応する値を返す方法を紹介します。 +演算子を併用することで、XLOOKUP関数の検索範囲に複数列を指定した結果を得ることができます。 COUNTIF関数等を併用する方法もあります。検索範囲…
ExcelのFIND関数を別の関数と組み合わせて「含まれる最も右の文字が何文字目にあるか」を取得してみます。 また、「右から数えて何文字目か」を取得する例も示します。 FIND関数とLEN/SUBSTITUTE関数を組み合わせて「最も右にある文字の位置」や「右から数え…
Excelで、正規分布に従う乱数を出力させる方法についてです。 NORMINV関数とRAND関数（複数の乱数を得る場合はRANDARRAY関数）を組み合わせることで、正規分布に従う乱数を生成することができます。 「データ」タブから「データ分析」メニューを選択し、「乱…
ExcelのSUMIF関数で、合計範囲が複数列である場合に合計を正しく求める方法についてです。 合計列を作るのが最も自然な対応ですが、関数側で対応する方法と別の関数を使った方法を紹介します。 列の数だけSUMIF関数を用意して足す方法、SUMIF/BYCOL/LAMBDA関…
SWITCH関数では基本的に第1引数の式と第2引数以降の値が一致するかどうかをチェックしていきますが、実は不等号を用いた比較も可能です。 ただし結果的にIFS関数と大して変わらない式になるため実用性は薄いです。 SWITCH関数の第1引数を「TRUE」とし、第2（…
Excelで2つの日付の間の日数を計算する際に、初日を含める方法についてです。 DATEDIFなどの関数を使うと初日が算入されない結果となるので、引数を少し変更する必要があります。 単純な引き算、DATEDIF関数、DAYS関数で日数を求めると初日を含まない日数と…
ExcelのXMATCH関数でAND条件（かつ）やOR条件（または）を指定して値を取得する方法についてです。 考え方はXLOOKUP関数の例と同じです。 XMATCH関数の第2引数（検索範囲）として複数の列の値を&でつないだものを指定すれば、複数の列に対するAND条件にする…
VLOOKUP関数で検索範囲よりも左側の列にある値を取得する方法についてです。 VLOOKUP関数とHSTACK関数を併用すれば検索範囲の列よりも左側の値を取得できます。 ただしVLOOKUPにこだわる理由がなければXLOOKUP関数の使用をおすすめします。 手順 代替案 XLOO…
ExcelでUNIQUE関数の対象に空白セルがあると「0」として出力されてしまいますが、これを空文字列（=""）にする方法についてです。空文字列に限らず任意の値に変換することができます。 併せて、そもそも空白セルを削除してしまう方法にも触れておきます。 UN…
Excelで、数値そのものにOR条件（または）をつけて合計を求める例や、複数の列にOR条件をつけて該当する行の数値を合計する例です。どちらの方法でも、両方の条件に該当する値を差し引くという手間が必要がなく、3つ以上の条件にも対応できます。 SUM関数とI…
Excelで、値そのものにOR条件（または）をつけてカウントする（つまり、いずれかの条件を満たす値の数を数える）例や、複数の列にOR条件をつけて件数（行数）をカウントする例です。どちらの方法でも、両方の条件に該当する値の数を差し引く手間が必要がなく…
ExcelのRANK関数で、同順位（n位タイ）があってもその次の数字（n+1位）を飛ばさずに順位をつける方法についてです。 XMATCH関数とUNIQUE関数等を併用して、同順位となるものが複数あっても次の順位を飛ばさないように順位をつけられます。 手順 文字列等を…
ExcelのFILTER関数で、対象となる表のうち取得する列を指定し、その中で条件を満たす行を抽出する方法についてです。 FILTER関数とHSTACK関数を併用すれば、HSTACK関数で指定した列だけを対象としてFILTER関数を実行できます。 抽出する列の見出しを用意して…
Excelで表からの検索を行うのに便利なXLOOKUP関数の使い方についてです。 XLOOKUP関数を使うことでVLOOKUP関数よりも簡単に表引き（表からの検索による値の取得）を行うことができ、機能もより充実しています。 機能と構文 基本的な使用例 第4引数（見つから…
Excelで複数行複数列のデータを縦1列に並べ替える、TOCOL関数の使い方についてです。 TOROW関数も並べる方向が違うだけでほぼ同じです。 「=TOCOL(範囲)」という式で、複数行複数列のデータを縦1列に並べ替えることができます。 機能と構文 基本的な使用例 …
Excelで、既存の表を指定した列数の表に並べ直す方法についてです。 WRAPROWS関数とTOCOLまたはTOROW関数を併用して、表の列数を変えて並べ直すことができます。手順 次の画像では、B2:C12の範囲にある2列の表を、関数を使って4列の表に並べ直しています。 E…
