いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

Excel

(Excel)年齢からあり得る生年月日を求める

基本的に満年齢から生年月日を求めることはできません(一意に定まらない)が、ある日においてある年齢の者がいつごろ生まれたのか絞り込むことはできます。 これをExcelでやってみます。 EDATE関数を使って、満年齢と基準日に基づき生年月日の範囲を求める…

(Excel)重複値(グループ)ごとに連番を振る方法

Excelで、セルの値でグループを区別し、各グループごとに1から連番を振る方法についてです。 各グループは連続する範囲に固まっていても、飛び飛びになっていてもOKです。 COUNTIF関数を使って(値により区別される)グループごとの連番を振ることができま…

(Excel)ランダムな時刻を生成する

Excelで、指定した2つの時刻の範囲内におさまるランダムな時刻を生成する方法についてです。 正確さを求めるとかなり込み入った内容になりますが、参考まで。 生成する時刻についての注意 「時:分」の時刻を生成する 「時:分:秒」の時刻を生成する n分(…

(Excel)左からn文字目だけ削除する方法

Excelで、テキストのうち左(先頭)から数えてn文字目だけを消す方法についてです。 REPLACE関数を使って左からn文字目だけを削除することができます。 左からn文字目を削除する方法 複数の文字を一括削除する方法 備考:「右から」の場合 左からn文字目を削…

(Excel)SUMIF関数で集計がうまくいかない原因と対策

ExcelのでSUMIF関数で合計を求めるときに、条件に該当する値があるにも関わらず結果が0になったり計算の意図とは異なる結果になることがあります。 ここでは考えられる原因と対策についていくつか紹介します。これらについてはSUMIFS関数にも共通します。 原…

(Excel)XLOOKUP関数で2番目(n番目)に該当するものを抽出する

ExcelのXLOOKUP関数で、検索値が複数該当(検索範囲内に複数存在する)場合に2番目(あるいは任意のn番目)に該当するものを探し、対応する値を抽出する例についてです。 ただし「無理やりXLOOKUPを使った」という感じで、実用的にはFILTER関数を使うべき内…

(Excel)IF関数で複数条件(OR)をリストで指定する方法

IF関数を使って「~または~である」という判定をする際に、別に用意したリストを使う方法についてです。 IF関数とCOUNTIF関数を併用して、リストを使った複数条件(OR、または)の判定ができます。手順 次の画像では左の表に判定対象となる商品名が並んでお…

(Excel)SUMIF(S)関数で日付・期間・年・月を指定して合計を求める

ExcelのSUMIF(S)関数を使って指定した日付、あるいは指定した期間に含まれる日付に対応する数値の合計を求める方法について紹介します。 さらに応用として、指定した年や月(年及び月)に含まれる日付に対応する数値の合計を求める例をいくつか紹介します。 …

(Excel)COUNTIF(S)関数で日付・期間・年・月を指定してカウントする

ExcelのCOUNTIF(S)関数を使って指定した日付、あるいは指定した期間に含まれる日付をカウントする方法についてです。 応用として指定した年や月(年及び月)に含まれる日付をカウントする例についても紹介します。 特定の日付をカウントする 特定の日以降・…

(Excel)COUNTIF関数で空白/空白以外のセルをカウントする

ExcelのCOUNTIF関数を使って空白セル(未入力状態)または空白以外のセルをカウントする方法についてです。 式は難しくはなものの、空白セルと「空白セルに見えて異なるセル」の違いを把握することが重要になります。 空白セルをカウントする 空白セル以外を…

(Excel)テキスト中の複数種類の文字列を一括削除する

Excelで、テキスト中の複数種類の文字列をすべて削除する方法です。 置換と同様にSUBSTITUTE関数を使う方法と、TEXTSPLIT関数を使った方法を紹介します。 SUBSTITUTE関数をネスト(入れ子)する方法 SUBSTITUTE関数とREDUCE/LAMBDA関数を使う方法 TEXTSPLIT…

(Excel)COUNTIF関数のワイルドカードで数値・日付をカウントする

ExcelのCOUNTIF関数ではワイルドカードを使って該当する値をカウントすることができますが、基本的に数値(数字)や日付にワイルドカードを適用することはできません。 以下ではこれを何とかする方法について紹介します。 作業列を追加して数値や日付を文字…

(Excel)COUNTIF関数でワイルドカードを使ってカウントする

ExcelのCOUNTIF関数で条件を指定する際にワイルドカードを使う例についてです。「~を含む文字列」とか「~で終わる文字列」といった条件に対応する文字列をカウントすることができ、あいまい検索などとも呼ばれます。使い方によっては複雑なカウントも簡単…

(Excel)XLOOKUP関数で「~以上~以下」の値を検索する

ExcelのXLOOKUP関数で「~以上~以下」や「~超~未満」の数値・日付を検索する方法についてです。 第5引数(一致モード)の設定により検索値を分類・ランク分けするのではなく、XLOOKUP関数内で最小値と最大値の両方を指定するというのが趣旨です。 実用的…

(Excel)XLOOKUP関数で数値・日付をワイルドカード検索する

ExcelのXLOOKUP関数ではワイルドカードを使った検索ができますが、基本的に検索対象が数値や日付の場合は検索できません。 そこで少し工夫して検索できるようにする例を紹介します。 XLOOKUP関数の第2引数内で検索対象を文字列に変換してしまえばワイルドカ…

(Excel)条件に一致する文字列を結合する

Excelで、条件に一致する文字列(条件に一致する行にある文字列)を結合する方法についてです。 よく「SUMIFで文字列を結合」などと例えられますがSUMIFではできないので、別の関数を使う必要があります。 FILTER関数とCONCAT関数(区切り文字を入れる場合は…

(Excel)月の最後・最初の平日の日付を求める

Excelで、月の最後の平日(土日祝日以外)や最初の平日の日付を求める方法についてです。 いくつかのパターンに分けて紹介します。 WORKDAY関数などの関数を使って、月の最後・最初の平日の日付を求めることができます。 最後の平日を求める方法 日付から求…

(Excel)SUMIF関数でワイルドカードを使って合計を求める

ExcelのSUMIF関数では条件の指定にワイルドカードが使えます。あいまい検索などとも呼ばれ、「~を含む文字列」とか「~で終わる文字列」といった条件に対応する数値の合計を求めることができます。使い方によっては複雑な集計も簡単にできるので、やり方を…

(Excel)VLOOKUP関数を使って合計を求める例

ExcelのVLOOKUP関数の活用例についてです。この関数自体には集計を行う(合計を求める)機能はありませんが、集計の過程で表引きを要する際には便利に活用できます。 手順 ここで想定する集計(合計を求める)の内容ですが、商品一覧と注文一覧の各データに…

(Excel)月末・月初の日付を求める方法

Excelで、月末や月初(1日)の日付を求める方法についてです。いくつかのパターンに分けて紹介します。 EOMONTH関数を使う方法やDATE関数を使う方法で、月末や月初の日付を求めることができます。 月末の日付を求める方法 日付から求める場合 年,月の値から…

(Excel)XLOOKUP関数は検索値の全角と半角を区別しない

ExcelのXLOOKUP関数の検索値の判定についてです。 XLOOKUP関数はVLOOKUP関数と異なり検索値の全角と半角を区別しません(同一視する)。大文字と小文字を区別しない点は同じです。 XMATCH関数についても同様です。手順 次の画像ではE3セルに入力されている全…

(Excel)行列のn乗を求める

Excelで数値のべき乗を求める場合は^演算子やPOW関数が使えますが、行列の場合にはそれらのようなものがありません。 そこでMMULT関数を応用して行列のべき乗を求める計算を行ってみます。ただし可能なのは整数乗のみです。 REDUCE/LAMBDA関数とMMULT関数を…

(Excel)VLOOKUP関数で複数条件(AND,OR)を指定する方法

ExcelのVLOOKUP関数で、AND条件(かつ)やOR条件(または)による検索・抽出をする方法についてです。 ただしXLOOKUP関数やINDEX/MATCH関数でより簡単に同じことができるため実用性は薄いです。 AND条件(かつ)の場合 OR条件(または)の場合 1つの列に複数…

(Excel)ワイルドカードで「n文字以上」を指定する方法

Excelでワイルドカードを使って1文字以上とか2文字以上の文字列を指定する方法についてです。 1つの「*」とn個の「?」を並べれば「n文字以上の任意の文字列」にマッチ(該当)するワイルドカードになります。「*」の位置はどこでもいいです。 手順 備考 手順…

(Excel)VLOOKUP関数で大文字と小文字を区別する

ExcelのVLOOKUP関数で、検索値の大文字と小文字を区別して検索する方法についてです。 ただしあまり実用的とは言えないため、より簡単なXLOOKUP関数やINDEX/MATCH関数を使う方法をお勧めします。 VLOOKUP関数とHSTACK/EXACT関数を併用することで、大文字と小…

(Excel)XLOOKUP関数で大文字と小文字を区別する

ExcelのXLOOKUP関数では、検索値(第1引数)を大文字で入力しても小文字で入力してもその違いを無視して検索するため、意図しない結果となる場合があります。 そこで、式を少し工夫して大文字と小文字を区別して検索する方法を試してみます。 XLOOKUP関数とE…

(Excel)INDEX+MATCH関数で検索範囲に複数列を指定する

Excelの関数の応用例として「INDEX関数とMATCH関数の組み合わせによる表引き」というのがありますが、ここでは検索値が複数列の値のいずれかに該当する場合に対応する値を返す方法を紹介します。 +演算子を利用してMATCH関数の検索範囲に複数列を指定した結…

(Excel)INDEX+MATCH関数で複数条件(AND,OR)を指定する方法

ExcelのINDEX関数とMATCH関数を使って、AND条件(かつ)やOR条件(または)に該当する値を検索・抽出する方法です。AND条件の一種であるクロス抽出についても紹介します。 MATCH関数の第1,2引数(検索値,検索範囲)で複数列の値を&でつないで指定することで…

(Excel)エラーの数をカウントする方法

Excelで、関数を使ってエラーのセルの数をカウントする方法についてです。 ISERROR関数とSUM関数等の集計関数を組み合わせてエラーの数をカウントできます。 SUM/ISERROR関数等を使う方法 SUMPRODUCT/ISERROR関数を使う方法 SUM/ISERROR関数等を使う方法 次…

(Excel)1か月分の平日の日付を自動入力する

Excelで関数を使い、指定された月のすべての平日(土日祝日以外)の日付を1列に表示する方法についてです。 各セルに式を配置する方法と、SEQUENCE関数を使って1つの式で出力する方法を紹介します。 出力の内容と表示形式の設定について 各セルに式を入力す…