Excel
SUMIF関数で検索条件にワイルドカードを使うときにうまくいかず結果が0になる原因についてです。 たいていは数値や日付を検索の対象にしていることが原因ですが、ここでは対応も含めて紹介します。 原因と対策 検索対象が数値である 検索対象が日付である そ…
Excelで、日付を「20231224」のような8桁の数字（数値または文字列）を日付に変換する方法についてです。 TEXT関数を使って日付を8桁の数字に変換できます。さらにVALUE関数を併用すれば数値に変換できます。 手順 備考：表示形式を変更した場合 手順 B3:B8…
Excelで、「20231224」のような8桁の数字（数値または文字列）を日付に変換する方法についてです。 関数を使う方法と、メニューを使って値を直接変換する方法を紹介します。 DATE関数等を使う方法 TEXT関数を使う方法 「区切り位置」メニューを使う方法 DATE…
Excelで、別のセルの値に応じてリストの内容を切り替え、段階的な絞り込みができるプルダウン（ドロップダウンリスト）を作る方法についてです。 表の名前定義とINDIRECT関数を併用する方法がよく知られていますが、ここではXLOOKUP関数を中心に新しい関数を…
Excelで、セル内にダブルクォーテーション（"）を入れる場合は普通に入力（Shift+2）すれば済みますが、式を使ってダブルクォーテーションをつける（囲む）ときにうまくいかない場合があります。その対策についてです。 数式を使って「" "」の内側にダブルク…
Excelでテキスト中の特定の文字まで、ただし左からではなく右から特定の文字までを抽出する方法についてです。 SUBSTITUTE関数など伝統的な関数を用いる方法だとなかなか複雑になりますが、TEXTAFTER関数が使えれば非常に簡単です。 「右から特定の文字まで…
ExcelのCOUNTIF関数では複数行複数列の範囲を指定してカウントできるものの、基本的に飛び飛びの（離れた）範囲を指定してカウントすることができないため少し工夫が必要になります。 ここでは2つの例を試してみます。 範囲の数だけCOUNTIF関数を繰り返して…
基本的に満年齢から生年月日を求めることはできません（一意に定まらない）が、ある日においてある年齢の者がいつごろ生まれたのか絞り込むことはできます。 これをExcelでやってみます。 EDATE関数を使って、満年齢と基準日に基づき生年月日の範囲を求める…
Excelで、セルの値でグループを区別し、各グループごとに１から連番を振る方法についてです。 各グループは連続する範囲に固まっていても、飛び飛びになっていてもOKです。 COUNTIF関数を使って（値により区別される）グループごとの連番を振ることができま…
Excelで、指定した2つの時刻の範囲内におさまるランダムな時刻を生成する方法についてです。 正確さを求めるとかなり込み入った内容になりますが、参考まで。 生成する時刻についての注意 「時：分」の時刻を生成する 「時：分：秒」の時刻を生成する n分（…
Excelで、テキストのうち左（先頭）から数えてn文字目だけを消す方法についてです。 REPLACE関数を使って左からn文字目だけを削除することができます。 左からn文字目を削除する方法 複数の文字を一括削除する方法 備考：「右から」の場合 左からn文字目を削…
ExcelのでSUMIF関数で合計を求めるときに、条件に該当する値があるにも関わらず結果が0になったり、合計が合わないなど計算の意図とは異なる結果になることがあります。 ここでは考えられる原因と対策についていくつか紹介します。これらについてはSUMIFS関…
ExcelのXLOOKUP関数で、検索値が複数該当（検索範囲内に複数存在する）場合に2番目（あるいは任意のn番目）に該当するものを探し、対応する値を抽出する例についてです。 ただし「無理やりXLOOKUPを使った」という感じで、実用的にはFILTER関数を使うべき内…
IF関数を使って「～または～である」という判定をする際に、別に用意したリストを使う方法についてです。 IF関数とCOUNTIF関数を併用して、リストを使った複数条件（OR、または）の判定ができます。手順 次の画像では左の表に判定対象となる商品名が並んでお…
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つの列に複数…
