Excel。OR関数の引数がたくさんあるので、数式がわかりにくい【many】

Excel。OR関数の引数がたくさんあるので、数式がわかりにくい

＜IF+OR関数＞

遠方の事務所は会議に参加するのにオンラインで参加OKという表をつくりました。

C列に地域というフィールドがあります。

D列のオンラインというフィールドには、C列の地域が、東北・四国・九州だったらば、オンラインで会議に参加。

それ以外は、リアルで参加するという表です。

D列のオンラインには、東北・四国・九州ならば、○と判断する数式を設定したいわけです。

C2が東北だったら、四国だったら、九州だったらと判断したいわけです。

判断となれば、IF関数をつかうわけですが、IF関数だけだと、ネストだらけになってしまいま

す。

そこで、OR関数をつかうことで、可読しやすい数式にすることができます。

D2に設定した数式は、

=IF(OR(C2="東北",C2="四国",C2="九州"),"○","")

この数式でも可読性はいいのですが、OR関数の引数を「C2=」が多く繰り返される傾向にあります。

今回は、3つで済んでいますが、10あれば、「C2=」が10も作る必要があります。

これでは、可読性が悪化してしまいます。

そこで、OR関数の引数に、配列定数をつかうことで、改善することができます。

D2の数式を修正します。

=IF(OR(C2={"東北","四国","九州"}),"○","")

配列定数をつかうことで、OR関数は、

C2={"東北","四国","九州"}

と、とてもわかりやすくなりました。

これならば、10件だったとしても、「C2=」は一つだけですみます。

Excel。FILTER関数で抽出結果に0（ゼロ）が表示されてしまうので対応したい【ZERO display】

Excel。FILTER関数で抽出結果に0（ゼロ）が表示されてしまうので対応したい

＜FILTER+IF関数＞

抽出するのに便利なFILTER関数ですが、ちょっと困ることがあります。

次の表をつかって説明します。

A1:D11にデータがあります。

チームがBで、合宿参加のデータを抽出したいので、F4にFILTER関数をつかって、抽出しました。

F4につくった数式は、

=FILTER(A2:D11,C2:C11=G1,"データなし")

最初の引数は、「配列」。

範囲選択なので、A1:D11。

FILTER関数は、スピル機能対応の関数なので、絶対参照は不要です。

2つ目の引数は、「含む」。

条件なので、C2:C11=G1 と等しいデータという条件で抽出します。

G1はBなので、チームBを抽出します。

最後の引数は、「空の場合」。

該当データがない場合は、どうするかということで、「データなし」と表示する設定をしました。

抽出結果は、チームBの人が抽出できたのですが、よくみると、合宿参加の列。

I4に「0（ゼロ）」と表示されています。

抽出元のデータは、空白なのですが、0（ゼロ）と表示されてしまっています。

これでは、今回たまたま「○（まる）」ですが「0（ゼロ）」だった場合、空白で0が表示されているのか、抽出元が0なのかわかりません。

では、どのようにしたら、抽出元が空白の場合、抽出結果も空白にすることができるのでしょうか。

抽出元が空白なので、0になってしまいます。

そこで、空白を意味する「””（ダブルコーテーション×2）」に置換した状態にして抽出する必要があります。

よって、FILTER関数を次のように修正することで対応できます。

=FILTER(IF(A2:D11="","",A2:D11),C2:C11=G1,"データなし")

これで、0ではなく空白のままにすることができました。

修正したのは、「配列」のところにIF関数を追加しました。

IF(A2:D11="","",A2:D11)

A2:D11で、空白セルは、空白。

そうでなければ、A2:D11のセルに設定されているデータのままという意味です。

このように、抽出で便利な関数のFILTER関数ですが、ケースによっては、アレンジする必要があります。

Excel。時間の大小比較の結果がおかしいんです。どう対応したらいいの。【Comparison】

Excel。時間の大小比較の結果がおかしいんです。どう対応したらいいの。

＜IF+TIMEVALUE関数＞

次の表は、ランニング成績の表です。

C列がスタート時間で、D列には、ゴールした時間が入力されています。

E列には、経過した時間。

=D2-C1

という数式が設定されています。

オートフィルで数式をコピーして、E5まで求めた表です。

そして、F列には、1時間以内なら○と表示する数式を設定してみました。

F2の数式は、

=IF(E2<="1:00","○","×")

オートフィルで数式をコピーしてみると、結果がおかしなことがわかります。

全部「○」になっています。

IF関数をつかっていて、論理式は、「E2<="1:00"」と、「1時間以内なら」という条件にしたはずなのですが、うまくいっていません。

「E2<="1:00"」の「”（ダブルコーテーション）」が邪魔なのかと思い、消した数式、

=IF(E2<=1:00,"○","×")

にしてみると、

エラーが表示されます。

何が原因なのでしょうか。

そこで、数式タブにある、数式の検証をつかって、確認してみましょう。

数式の検証は、数式の途中結果などの状況を確認することができるツールです。

クリックすると、数式の計算ダイアログボックスが表示されます。

検証ボタンをクリックすると、1ステップずつ、計算されます。

時間は、Execlの場合シリアル値に戻りますので、0.05となっています。

検証ボタンをさらにクリックして、進めてみると、

”1:00”は、シリアル値にかわることなく、文字として扱われていることが確認できます。

“1:00”は、文字扱いになっていますから、当然、”1:00”という文字よりも、数値の方が小さい扱いになるので、すべて「○」になってしまったというわけです。

”1:00”をどうにか、時間に変換することができれば、解決できるわけです。

そこで、登場するのが、TIMEVALUE関数です。

まずは、先程の数式に、TIMEVALUE関数を追加して修正してみます。

=IF(E2<=TIMEVALUE("1:00"),"○","×")

オートフィルで数式をコピーしてみると、正しい結果を求めることができました。

このTIMEVALUE関数は、

文字列で表示された時刻をシリアル値に変換してくれる関数です。

もし、時間を使った計算で、結果が正しくない場合には、文字になっていないか、文字になっていたら、TIMEVALUE関数をつかってみて確認してみるといいかもしれませんね。

Excel。1回目から3回目まですべて80以上かを判定したい【AND】

Excel。1回目から3回目まですべて80以上かを判定したい

＜IF+AND関数＞

条件判断する関数はExcelには多く用意されています。

次のような表があります。

B列からD列には、1回目から3回目のポイントが入力されています。

この1回目から3回目までのポイントすべてが80以上ならば、「○」と表示したいのですが、どのようにしたらいいのでしょうか。

IF関数を複数重ねたネストでも判定することはできますが、少し複雑な数式になりますし、面倒です。

そこで、今回は、IF関数と、AND関数をつかった数式で、対応していきます。

E2に数式を設定します。

E2に設定した数式は、

=IF(AND(B2>=80,C2>=80,D2>=80),"○","")

あとは、オートフィルで数式をコピーします。

これで、1回目から3回目まですべてが80以上の場合に、○が表示されていることが確認できました。

AND関数は、「○○かつ○○」というAND条件の関数です。

AND関数の引数に条件を3つ設定する必要がありますが、とてもわかりやすく、数式を作ることができます。

仮に4回目以降に増えたとしても、AND関数の条件を追加するだけで、すみますので、比較的容易に対応することができます。

Excel。日付を直接入力した数式はシリアル値で考える必要があります。【DATE】

Excel。日付を直接入力した数式はシリアル値で考える必要があります。

＜IF+DATEVALUE関数＞

判定に使う日付をセルに入力している場合には、いいのですが、直接日付を引数に入力した場合、きちんと算出してくれないことがあります。

次の表を用意しました。

B列に作業終了日という日付が入力されています。

C列には、2024/10/31以前ならば、○。

それ以降ならば、×と算出したいと考えています。

そこで、IF関数をつかって、C2に数式をつくります。

=IF(B2<=2024/10/31,"○","×")

オートフィルで数式をコピーします。

すべてC列は、2024/10/31以前も以降も関係なく、×になっています。

C2の数式を確認するために、数式タブの「数式の検証」を行ってみると原因がわかります。

2024/10/31の「2024/10」を数式と勘違いして、2024÷10として、さらに÷31をしています。

つまり、B列の日付がシリアル値という数値で、C列は「/」を割り算と勘違いして、除算した結果の数値と比べているために、結果がおかしくなっています。

日付として扱ってくれていないわけです。

だったら、”2024/10/31”と「”（ダブルコーテーション）」で日付を囲ってみたらどうなるのでしょうか。

今度は、すべて「○」となってしまいました。

これは、”2024/10/31”が日付ではなくて、文字列として判断されています。

そのため、シリアル値という数値と文字を比べていますので、当然、シリアル値である日付のほうが小さくなります。

よって、結果は「○」となってしまったというわけです。

紹介した両方とも、「日付」として扱ってくれていません。

Excelには日付はシリアル値という数値です。

日付型ではありません。

そこで、日付型というか、日付とExcelにわからせる必要があります。

そこで、DATEVALUE関数をつかって、数式を修正します。

=IF(B2<=DATEVALUE("2024/10/31"),"○","×")

DATEVALUE関数は、日付文字列にしてくれる関数です。文字列を日付扱いにしてくれます。

これで、きちんと算出してくれました。

このように、日付を直接入力して使う場合には、日付文字列にする必要がある場合、DATEVALUE関数を組み合わせることで対応することができます。

Excel。値を検索して、複数の列から対応する結果を抽出したい【Multi-column】

Excel。値を検索して、複数の列から対応する結果を抽出したい

＜VLOOKUP関数＋IF関数・XLOOKUP＋INDIRECT関数＞

会員と一般で、単価が異なる表から、会員なら会員の、一般なら一般の単価を抽出し表示したい場合、どのようにしたらいいのでしょうか。

次の表をつかって説明します。

A2の所属が一般で、商品コードがB2のA01です。A5:D7の表から、商品コードがA01の

一般なので、D2の単価は、100と表引きされています。

A2の値を会員にすれば、D2の単価は80となるようにしたいわけです。

このような場合、VLOOKUP関数をつかうといいように思えますが、うまくいきません。

C2の商品名は、VLOOKUP関数で対応することは、できます。

C2の数式は、

=VLOOKUP(B2,A6:D7,2,FALSE)

VLOOKUP関数の最初の引数、検索値は、B2

2つ目の引数は、範囲なので、A6:D7 と設定します。

3つ目の引数は、列番号です。

2つ目の引数で設定した範囲の左から何列目のデータを表示するのかということなので、左から「2」列目なので、2と設定します。

最後の引数の、検索方法は、完全一致ですから、FALSE。

商品名は、VLOOKUP関数でもいいのですが、問題は、列番号です。

会員ならば、3。一般なら4としなければなりません。

このような場合、

=VLOOKUP(B2,A6:D7,IF(A2="会員",3,4),FALSE)

と列番号を判断させるようにIF関数をつかってもいいと思います。

ただし、所属数が会員・準会員・一般・学生のように、増えた場合、IF関数では対応するのが大変になるので、多分岐できる関数を使う必要がでてきます。

また、所属、それぞれの表をつくって、INDIRECT関数をつかって対応する方法があります。

ただ、その方法では、別表を作る必要がありますので、面倒です。

そこで、INDIRECT関数はつかうのですが、VLOOKUP関数よりもXLOOKUP関数で対応する方法があります。

事前に「名前の定義」を設定します。

C6:C7に、「会員」

D6:D7に、「一般」

と名前を設定しました。

D2の数式は、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

A2の所属を変更しても、会員と一般、それぞれの数値を表引きしてくれます。

もし、所属数が増えても、名前の定義を増やすだけで、数式を変更する必要はありません。

では、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

XLOOKUP関数の数式を確認します。

最初の引数は、検索値　なので、B2を設定します。

2つ目の引数は、検索範囲。

検索値が所属されている範囲なので、A6:A7。

3つ目の引数は、戻り範囲。

表示したい範囲です。

ここに、会員なのか、一般なのかで、戻り範囲を対応させたい。

そこで、INDIRECT関数をつかうことで、戻り範囲を変更させています。

INDIRECT関数は、値そのものをつかうことができます。

名前の定義で、会員と一般を設定していますので、切り替えることができるというわけです。

4つ目の引数は、見つからない場合。

見つからない時は「””（ダブルコーテーション×2）」で空白と設定します。

5つ目の引数は、一致モードなので、０の完全一致にしました。

6つ目の引数は、検索モード。

先頭から検索させますので、1と設定します。

このように、XLOOKUP関数とINDIRECT関数を組み合わせることで、値を検索して、複数の列から対応する結果を抽出することができます。

Excel。条件で分岐して異なる計算結果を返すのがIF関数です。【IF】

Excel。条件で分岐して異なる計算結果を返すのがIF関数です。

＜関数辞典：IF関数＞

IF関数

読み方： イフ　　

分類： 論理 

IF(論理式,[値が真の場合],[値が偽の場合])

条件で分岐して異なる計算結果を返す

Excel。全て入力されているか、手早くチェックするにはどうしたらいい【Entered】

Excel。全て入力されているか、手早くチェックするにはどうしたらいい

＜IF+COUNT関数＞

入力欄のすべてにデータが入力されているかをチェックしたい場合には、件数を算出する関数をつかいます。

今回のデータは、1回目から3回目の測定値が入力されている表です。

3回目まで終わっているならば、E列に○と表示させるようにしています。

まず、判断が必要になりますので、IF関数をつかいます。

そして、IF関数の論理式には、3回目、すなわち、3件データがあるかどうかを調べればいいわけです。

そこで、E2には、次の数式を設定しました。

=IF(COUNT(B2:D2)=3,"○","")

あとは、オートフィルで数式をコピーします。

件数を算出するわけですが、すべて数値なので、COUNT関数をつかっています。

もし、文字列とか、数値や文字列が混在している場合にはCOUNTA関数をつかいます。

=IF(COUNTA(B2:D2)=3,"○","")

その結果が3と等しいか判断させればいいというわけです。

条件付きの件数なので、COUNTIF関数やCOUNTIFS関数をつかってもいいですが、条件がシンプルなので、IF関数とCOUNT関数、あるいは、IF関数とCOUNTA関数で対応することができます。

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。【trouble】

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。

＜AND関数・IF+COUNTIFS関数＞

AかつBという条件をつくることができる「AND関数」ですが、欠点があります。

それが、ワイルドカードをつかった条件式には対応してくれないということです。

次の表をつかって説明します。

判定しないのは、所属はAからはじまり、かつ、住所には横浜市が含まれるデータなら、「○」を表示して、そうでなければ、「空欄（空白）」としたい。

AND関数をつかってみたところ、思っているように算出されません。

C2に設定した数式は、

=IF(AND(A2="A*",B2="*横浜市*"),"○","")

AND関数とワイルドカードの組み合わせは、うまくいかないようです。

AND関数で、ワイルドカードをつかわない数式にすれば、算出することはできます。

ワイルドカードを使わない数式は、

=IF(AND(MID(A2,1,1)="A",MID(B2,5,3)="横浜市"),"○","")

ただ、MID関数をつかうので、可読性が下がってしまいます。

そこで、COUNTIFS関数をつかうことで、対応することができます。

C2の数式を次のように設定して、オートフィルで数式をコピーします。

=IF(COUNTIFS(A2,"A*",B2,"*横浜市*"),"○","")

COUNTIFS関数は、ワイルドカードをつかった条件をつかうことができます。

数式を確認しておきましょう。

IF関数の引数であるCOUNTIFS関数から説明します。

COUNTIFS関数の最初の引数が、「検索条件範囲1」です。A2を設定します。

2番目の引数は、「検索条件1」。ここに「”A*”」とワイルドカードをつかっています。

「A*」とすれば、「Aで始まる」という意味です。

3番目以降は条件範囲と、条件の繰り返しです。

B2のあたいに、「*横浜市*」なのかを確認する条件式を設定します。

「*横浜市*」はB2に「横浜市という文字が含まれる」という意味です。

この2つの条件が合致したら「TRUE」、合致しない場合は「FALSE」と判定結果がでます。

IF関数をつかってTRUEならば「”○”」と表示します。

Excel。データが揃ってから、手早く平均値を算出するには、どうしたらいいの。【data is available】

Excel。データが揃ってから、手早く平均値を算出するには、どうしたらいいの。

＜IF+AVERAGE+COUNT関数＞

平均値を算出するならば、AVERAGE関数をつかうことで、手早く算出することができます。

ただし、平均値は、データの件数で除算する必要があります。

そこで、データの一部が揃っていないものと揃っているものではデータ数に差があるので、算出された平均値の意味は、当然変わってしまいます。

データが揃っていない場合には、平均値を算出しないで、保留にするには、どのようにしたらいいのでしょうか。

データが揃っていないところには、数式を削除して、揃ってから、数式をコピーするというのも、面倒です。

データが全て揃っているのを確認して、揃っていたら、平均値を算出する数式に変更します。

E2の数式を次のように設定します。

=IF(COUNT(B2:D2)=3,AVERAGE(B2:D2),"データ待ち")

あとは、オートフィルで数式をコピーします。

これで、データが揃ってから平均値を算出するようにできました。

数式を確認します。

IF関数をつかって、データが揃っていない時には、「データ待ち」と表示するようにします。

IF関数の最初の引数である論理式には、

COUNT(B2:D2)=3

COUNT関数をつかって、データの件数を算出させています。

件数が3ならば、データが揃っているとわかる仕組みです。

2つ目の引数であり、真の場合は平均値を算出したいので、AVERAGE関数をつかって、平均値を算出します。

このように、データの件数を算出することで、データが全て揃っていなければ、平均値を算出しないようにできます。

Excel。見積書や請求書の単価×数量で表示される0（ゼロ）を消すにはどうするの【hide 0】

Excel。見積書や請求書の単価×数量で表示される0（ゼロ）を消すにはどうするの

＜IF関数＞

IF関数は、条件分岐で使用しますが、100点以上なら○でそうでなければ×というような場合以外にも、エラーに対応する場合には、使用します。

そして、エラーは表示されていないけど、その結果はどうなの？という場合もあります。

用意したのは見積書です。

 

E4には、単価×数量で金額を算出する、C4*D4という数式を設定しました。

E4は、きちんと算出結果である金額が算出されています。

その数式を、E8までオートフィルで数式をコピーすると、E5:E8まで「0（ゼロ）」と表示されてしまいます。

これは、C列とD列にデータがないのが原因です。

数値がないセルを０という数値が入力されていると考えて、Excelは算出しますので、E5:E8には0と表示されているというわけです。

表示されていてもいいケースなら、別にこれでOKですが、見積書や請求書をはじめ、第三者がみるような資料の場合、あまり、お勧めできる結果ではありません。

この0をどのようにしたら表示しないようにできるのかというわけです。

そこで、登場するのがIF関数というわけです。

E4の数式を次のように修正します。

=IF(C4="","",C4*D4)

オートフィルで数式をコピーします。

すると、0が表示されなくなりました。

今回は、C列の単価が未入力ならば、データがないものとして、条件に使用しましたが、A列のNOが空白ならば、A列を使うほうがいいですね。

要するに、入力されていなければ、空白そうでなければ、単価×数量を算出すると、条件分岐するIF関数と組み合わせることで対応できます。

Excel。日付から、作業列（条件）をつくらないで、月別集計をしたい。【totalling】

Excel。日付から、作業列（条件）をつくらないで、月別集計をしたい。

＜SUM+IF＋MONTH関数＞

A列には、セミナー開催日が入力されている表があります。

B列には、セミナー別に参加した人数が入力されており、E列に、月別ごとの集計をしたいわけです。

そこで、1つの数式だけで月別集計を行うことはできないのでしょうか。

月別集計を行いたい場合、日付から何月なのかがわからないと集計することができません。

作業列として、MONTH関数をつかって、月を用意する必要があります。

また、期間計算でおこなうとしても、結局のところ、算出するための作業列をつくらないと算出することができません。

実のところ、合計を算出するSUM関数の引数にIF関数を組み込むことで、1つだけの数式で算出することができます。

E2に次の数式を設定しました。

=SUM(IF(MONTH($A$2:$A$10)=D2,$B$2:$B$10,0))

あとは、オートフィルで数式をコピーしました。

このように、1つの数式だけで、月別集計を算出することができました。

使用している関数は、SUM関数とIF関数とMONTH関数という比較的、馴染みのある関数だけで算出することができました。

数式を確認しておきましょう。

IF関数の論理式。

MONTH($A$2:$A$10)は、配列関数ですが、スピル機能が搭載されたExcelだと、配列関数と意識することなく、数式をつくることができます。

A2＝D2だったら、B2。

そうでなければ0（ゼロ）とします。

これを、A10までおこなうわけです。

その値を、SUM関数で、和算するというわけです。

Excel。重複しているデータがわかるように、「重複」という文字を表示させたい【overlapping】

Excel。重複しているデータがわかるように、「重複」という文字を表示させたい

＜IF+COUNTIF関数＞

データに重複しているものがあれば、データタブの「重複の削除」をつかうと、データは削除されてしまいます。

また、条件付き書式の「セルの強調表示ルール」にある「重複する値」をつかうことで、塗りつぶしたりすることで、重複を確認することができます。

今回は、重複している場合、「重複」という文字を表示するには、どのようにしたらいいのかを紹介します。

B列に担当者名が入力されています。

重複されていることがわかった時点で、C列に「重複」を表示したいわけです。

重複は2件以上という意味なので、全体の中で、そのデータが何件あるのかがわかれば、いいわけですね。

何件あるのかということですから、使用する関数は、COUNTIF関数をつかうと対応することができます。

C2にCOUNTIF関数をつかった数式を設定します。

=IF(COUNTIF($B$2:B2,B2)>=2,"重複","")

あとは、オートフィルで数式をコピーすれば、完成です。

重複しているデータには、重複という文字が表示されていることが確認できます。

では、数式の説明です。

最初のIF関数は、条件に合致したら、「重複」。

それ以外は「空白」を表示させるための関数です。

メインの関数がCOUNTIF関数です。

COUNTIF($B$2:B2,B2)>=2

最初の引数は、範囲です。

始点を絶対参照にすることで、オートフィルで数式をコピーすることで、対象範囲を拡張することができます。

2番目の引数は、検索条件です。

自分自身が何件あるのかを確認させます。

その結果が、2以上なら重複しているわけなので、重複と表示させることができるというわけです。

Excel。10日締め翌10日払いの日が、土日祝日なら前営業日にするにはどうするの【before the weekend】

Excel。10日締め翌10日払いの日が、土日祝日なら前営業日にするにはどうするの

＜WORKDAY＋EOMONTH＋IF＋DAY関数＞

支払予定日が、土日祝日だと、だいたい、前営業日に支払いをすることになるわけです。

その前営業日を算出するには、どのように関数を組み合わせたたらいいのでしょうか。

次のケースをつかって説明します。

結論というか、対応した数式をまず、紹介すると、

=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)

という数式をつくることで、土日祝日ならば、前営業日を算出することができます。

A2の2023/10/3は、10日締めなので、翌月の10日が支払日です。

B2のように2023/11/10が支払日となります。

B2に設定した数式は、

=EOMONTH(A2,IF(DAY(A2)<=10,0,1))+10

この式がベースとなっていきます。

EOMONTH関数は、月末日を算出する関数です。

1番目の引数は、「開始日」なので、A2を設定します。

これで、2023/10/31が月末日です。

2番目の引数は、「月」です。

10日締めなので、当月なのか、翌月の月末なのかという月を算出させる必要があります。

IF(DAY(A6)<=10,0,1))

IF関数とDAY関数をつかって、10日以前なのどうかを判断させています。

10日以前ならば「0」。

そうでなければ「1」とすることで、当月末なのか、翌月末なのかを算出できます。

その日付に「+10」すれば、10日払いの日付を算出することができるというわけです。

もし、25日払いならば、「+25」とすればいいわけです。

だから、EDATE関数ではなくて、EOMONTH関数をつかったというわけです。

さて、算出した日付が、平日ならばいいのですが、土日祝日だった場合、金融機関がお休みなので、前営業日にしたいわけです。

そこで、土日祝日を除くことができるWORKDAY関数をつかって、先程のEOMONTH関数をネストします。

=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)

WORKDAY関数の最初の引数は、「開始日」です。

これは、先程のEOMONTH関数で算出した数式を設定します。

ただし、先程、10日払いだから「+10」としましたが、前営業日にしたいので、ワザと1日多い「+11」にします。

2つ目の引数は、「日数」なので、これを「-1」と設定することで、前営業日を算出することができます。

この「+1」と「-1」の考え方ですが、前営業日にしたいので「-1」したいわけです。

土曜日の場合は、「-1」すれば金曜日なので、問題はないのですが、金曜日など平日の場合は、その日でいいのにもかかわらず、「-1」されてしまい、前日が支払日として算出されます。

そのため、わざと「+11」と一日多くして、「-1」させるという方法をつかっております。

最後3つ目の引数は、祝日のカレンダーを用意しておき、その日付にも対応するようにしています。

土日祝日が絡んだ場合、前営業日を算出する方法をご紹介しました。

Excel。文字列に統合や3-D集計は、対応していないので、CONCAT関数をつかいます。【string】

Excel。文字列に統合や3-D集計は、対応していないので、CONCAT関数をつかいます。

＜IF+CONCAT関数＞

当たり前ですが、文字列は合算したりすることはできません。

たとえば、次のシート。

Aさん・Bさん・Cさんのそれぞれの出席可能がわかる表があります。

「○」があるところが出席可能を表しています。

やりたいことは、3名が出席できる日時を、手早く確認したいわけです。

「○」ではなく、数値で判定されていれば、統合などの方法もあるのでしょうけども、文字列なので、つかえません。

IF関数とAND関数をつかってもいいですが、もっと多くの表の場合、AND条件が多くなり、可読性も悪化します。

そこで、IF関数とCONCAT関数をつかって、全員出席できる日時を確認していきます。

B2に次の数式を設定します。

=IF(CONCAT(B7,F7,J7)="○○○","開催OK","")

あとは、オートフィルで数式をコピーします。

これで、10月3日10時は、予定が空いているので、開催できる日時を見つけることができました。

この数式のCONCAT関数ですが、CONCAT関数は文字結合をする関数です。

範囲選択するだけで、文字を結合してくれます。

すべて「○」。

つまり「○○○」ならば、出席できると判断することができるというわけです。

Excel。VLOOKUP関数で条件によって検索する列を変えるにはどうしたらいいの【column change】

Excel。VLOOKUP関数で条件によって検索する列を変えるにはどうしたらいいの

＜VLOOKUP+IF関数＞

VLOOKUP関数は、INDIRECT関数と名前の定義を組み合わせることで、別々の表（範囲）から検索値を抽出することはできます。

では、表（範囲）の中で条件によって抽出対象になる列を変えることはできるのでしょうか。

次の表で説明します。

A2に商品名を入力したら、B2にD1:F5の表（範囲）から、単価かセールの数値を表示したいわけです。

B1には、入力規則のリストをつかって、「単価」と「セール」を切り替えるようにしています。

B1が単価なら、B2には、E列の単価の数値を抽出し、B1がセールならB2にはF列のセールの数値を抽出したいわけです。

そこで、B2に設定するVLOOKUP関数は次のようにします。

=VLOOKUP(A2,$D$2:$F$5,IF(B1="単価",2,3),FALSE)

これで、B1を切り替えると、該当する列からデータを抽出することができます。

VLOOKUP関数のポイントは、3番目の引数の列番号です。

この列番号を、単価なら「2」、セールなら「3」としたいわけですから、IF関数をつかって列番号を変えることができます。

これによって、B1の値と連動して、抽出範囲を切り替えることができるようになります。

Excel。検索文字を含むなら「○」という判定を手早く行うにはどうしたらいいの【contains characters】

Excel。検索文字を含むなら「○」という判定を手早く行うにはどうしたらいいの

＜IFERROR+IF+SEARCH関数＞

東京都の23区内なのかなど、セル内に該当する文字が含まれているのかを判定したい場合、単純にIF関数だけでは対応することができません。

次の表をつかって説明します。

B列に住所が入力されています。

C列に23区内ならば、「○」を表示するとします。

C2に設定する数式は、

=IFERROR(IF(SEARCH("東京都*区",B2),"○",""),"")

として、オートフィルで数式をコピーすると、完成なのですが、IF関数以外にもIFERROR関数やSEARCH関数をつかっています。

なぜIF関数以外をつかっているのかというと、IF関数の論理式に「ワイルドカード」が使えないからです。

「東京都」という文字で始まり「区」を含んでいるのかを判断させる必要が生じます。

そこで、SEARCH関数をつかって、判断させます。

IF関数にSEARCH関数をネストした数式、

=IF(SEARCH("東京都*区",B2),"○","")

オートフィルで数式をコピーしてみると、合致しないセルに「#VALUE!」というエラーが表示されています。

SEARCH関数で困るのは、合致しない場合、「#VALUE!」というエラーを表示してしまうことなんです。

つまり、偽の場合の設定よりもエラーが強いので、偽の場合の表示設定を行ってくれません。

そのため、エラーをコントロールするIFERROR関数をつかって「#VALUE!」というエラーを表示しない作業をさせる必要があるわけです。

なお、IF関数は、偽の場合を設定しないとエラーになるので、「#VALUE!」と表示されます。
そのため、「””」を設定しておきます。

また、検索するSEARCH関数と同じようにFIND関数がありますが、FIND関数は、ワイルドカードをつかうことができませんので、SEARCH関数をつかう必要があります。

Excel。手早く曜日ごとの売上高を集計したいけど、どのようにしたらいいの。【by day of the week】

Excel。手早く曜日ごとの売上高を集計したいけど、どのようにしたらいいの。

＜SUM＋IF＋WEEKDAY関数＞

曜日ごとに集計したい場合、曜日がわかるように一度数式をつくり、それを条件として集計する方法を行うことがあります。

途中の計算式を作らなくても、SUM関数とIF関数とWEEKDAY関数を組み合わせるだけで、数式一つで、曜日ごとの集計をすることができます。

次の表を用意しましたので、確認していきます。

B1:C11にデータがあります。

曜日が表示されていれば、SUMIF関数をつかえば、算出することができますが、曜日を表示されていません。

通常、日付に基づいた曜日を算出させてから、曜日を条件として集計するわけですが、それでは、数式を複数作る必要があります。

そこで、G2には、次の数式を設定しています。

=SUM(IF(WEEKDAY($B$2:$B$11,1)=E2,$C$2:$C$11,0))

オートフィルで数式をコピーしています。

これで、曜日ごとの売上高の集計を算出することができました。

数式を説明すると、

WEEKDAY($B$2:$B$11,1)=E2

WEEKDAY関数は、曜日を算出する関数です。

WEEKDAY関数の2つ目の引数にある「種類」を1と設定することで、1を日曜日で7を土曜日として数値を割り振ってくれます。

E列のNOをつかって、合致することで、何曜日なのかを判断することができます。

そこで、IF関数をつかうことで、合致しなかった場合は、「0（ゼロ）」とすることができます。

最後に、SUM関数をつかって、集計します。

以前は、この数式は、配列関数で作成したのですが、Microsoft365では、普通に数式をつくるだけで、対応してれます。

本当に、合っているのか、念のため確認してみましょう。

A2に曜日を算出する数式をつくります。

=TEXT(B2,"aaa")

A11まで、オートフィルで数式をコピーしています。

日曜日は、983+874なので、1857とG2の値と合致していることがわかります。

今回紹介した数式ですが、WEEKDAY関数をMONTH関数やYEAR関数にすることで、作業列に算出してから集計しなくても、数式一つで算出することができます。

Excel。数値がすべて入力されてから平均値を算出したいけど、どうしたらいい【have everything】

Excel。数値がすべて入力されてから平均値を算出したいけど、どうしたらいい

＜IF+COUNT+AVERAGE関数＞

平均値を算出するには、AVERAGE関数をつかえば、手早く算出できます。

そもそも、平均値は、数値の合計値をその件数で除算した値です。

ということは、数値の件数に差があると、平均値を比べることができません。

次の表で説明します。

7行目には、AVERAGE関数で平均値を算出する数式が設定してあります。

C7も、データが3件の平均が算出してあります。

B7の平均は5件分とすべてのデータが入力してあるので、当然B7とC7を単純に比べることはできません。

そこで、すべてのデータが入力されたらば、平均値を算出するようにしたいわけです。

B7に次の数式を設定します。

=IF(COUNT(B2:B6)=5,AVERAGE(B2:B6),"")

C7には、オートフィルで数式をコピーします。

これで、データがすべて入力されていない場合には、平均値を算出、表示しないようにできます。

数式を確認しておきましょう。

この数式のポイントはCOUNT関数をつかっているところです。

範囲内のセルの一部が空白だったら計算しないというようなIF関数を考えがちですが、範囲の一部というどこのセルが空白なのかを調べる条件をつくるのは、大変です。

そこで、COUNT関数を組み合わせることで、手早く、データが全て入力されていなければ算出しないようにできます。

なお、対象が数値だったので、COUNT関数をつかって対応しましたが、当然文字の場合は、COUNT関数では対応できません。

COUNT関数は数値のみが、数える対象です。

Excel。午前0時をまたぐ時間計算は、減算だけでは算出できません。【Nighttime】

Excel。午前0時をまたぐ時間計算は、減算だけでは算出できません。

＜IF関数＞

夜間勤務など午前0時をまたぐ時間計算は、単純に減算だけでは、算出することができません。

D列の勤務時間は、C列の退社からB列の出社の時間を減算すれば、算出することができるはずです。

D4には、

=C4-B4

と設定してあって、オートフィルで数式をコピーしています。

D4は、きちんと減算してくれていて「3:00」と結果を表示していますが、D5やD6は「####」と表示されてしまっています。

D5をアクティブにすると、次のメッセージが表示されます。

「負であるか、大きすぎる日付および時間は######と表示されます。」

どうやら、Excelは午前0時をまたぐ場合、単純な減算だけでは算出できないようです。

この原因は、Excelでは、時間や日付は、シリアル値で管理されているためです。

よって、D4の数式をIF関数で修正します。

=IF(B4<C4,C4-B4,C4+1-B4)

あとは、オートフィルで数式をコピーします。

このように、午前0時をまたいでも、減算結果を表示することができました。

退社が「5:00」とあれば、翌日の「5:00」というイメージをもちますが、Excelは単純に「5:00」という時間でしかありません。

出社の「22:00」と退社の「5:00」は1900/1/1の一日内の数値の大小でしかないということです。

それを踏まえて、数式をつくります。

IF関数の数式を確認します。

最初の引数の「論理式」は、B4<C4。

退社のほうが出社よりも大きいかを判断させます。

これで、退社が午前0時以降なのかを判断することができます。

2つ目の引数の「真の場合」は、C4-B4。

普通の退社-出社ですね。

3つ目の引数の「偽の場合」は、C4+1-B4。

なぜ「+1」しているのかというと、翌日とさせたいわけですね。

Excelは1日をシリアル値で「1」としています。

「5:00」は29:00ですね。

なので、「+24」したいわけです。

ただ単に「+24」すると、24日後ということになってしまいます。

シリアル値で考えると、「1日＝1＝24:00」なので、「+1」するということは「24:00」を足して24時間以上の表示にしているわけです。

こうなれば、算出結果が「負」にはなりません。

「######」と表示されないで、勤務時間を算出することができたというわけですね。

Excelの時間計算は、シリアル値を考えないといけないケースがあるので、注意が必要ですね。