Excel。どちらかが合致しているという条件式には、IFS関数でもOKです。【OR】

Excel。どちらかが合致しているという条件式には、IFS関数でもOKです。

＜IF+OR関数　IFS関数＞

WritingとReadingの結果、どちらかが70点より大きい場合は、○。

そうでなければ×と判定したい場合は、どちらか一方ということで、「～または～」というOR条件ですね。

そこで、IF+OR関数をつかった数式を設定します。

D3には、

=IF(OR(B3>70,C3>70),"○","✕")

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

どちらかが70より大きければ、○なので、D4とD5は✕になっています。

まぁ、問題はありません。

ただ、Excelの関数が苦手、関数の中に関数を設定するネストは苦手という人もいます。

そこで、他の方法もありますので、今回はその一つとして、IFS関数をつかった方法をご紹介します。

E3には、IFS関数を設定しました。

=IFS(B3>70,"○",C3>70,"○",TRUE,"✕")

結果は同じになっていますね。

IFS関数は、複数の条件をまとめて設定できる関数です。

それぞれが70より大きいのかを設定してあげる。

そして、その他は、 TRUE を設定すれば、同じように求めることができます。

ただ、IF＋OR関数もIFS関数の数式も、実は欠陥があります。

C5とC6の空白セルに、「欠席」と入力してみましょう。

すると、5行目が、どちらも○に変わってしまいました。

原因は、欠席という文字は、数値よりも大きいからです。

最悪なのは、6行目のほうです。

結果はあっていますので、チェックをすり抜けます。

後日、83が70以下に変わってしまうと、トラブルが発生するというわけです。

100点が最高値ならば、100以下という条件も追加すればいいように思えますが、100以下だと、70以下も含まれてしまうため、条件を追加するだけではダメです。

このような場合、文字を入れさせないように、運用上のルールを設けるのがいいと思います。

ただ、どうしても、というのであれば、文字じゃないという条件を追加する必要があります。

D3の数式を変更しました。

=IF(OR(IF(ISNUMBER(B3),B3,0)>70,IF(ISNUMBER(C3),C3,0)>70),"○","×")

ISNUMBER関数は、数値かどうかを確認する関数です。

ISNUMBER(B3)が成立、すなわち、数値ならば、B3をつかいます。

ISNUMBER(B3)が不成立ならば、0として判断させます。

このように、対応はできますが、数式が複雑化していくことになりますから、運用上で対応というのでもいいかと思います。

Excel。日付から曜日を求めるWEEKDAY関数だけでは曜日を表示できません【WEEKDAY】

Excel。日付から曜日を求めるWEEKDAY関数だけでは曜日を表示できません

＜WEEKDAY関数＞

日付から曜日を求める関数があります。

それがWEEKDAY関数です。

このWEEKDAY関数は、日とか月とかというような文字ではなくて、日曜日を1、月曜日を2というような数値で表示する関数です。

WEEKDAY関数は、2つ目の引数の種類を変えることによって、何曜日から始めるのかを決めることができます。

そこで、数値で表示されても、曜日名がわからないと何が何だかということになってしまうので、色々な関数と組み合わせます。

 

B2の数式は

WEEKDAY関数をつかった場合です。

=WEEKDAY(A2,1)

結果は、数値として表示されています。

これでは、何曜日か一目ではわかりません。

C2の数式は、

IF関数とWEEKDAY関数を使った場合です。

=IF(WEEKDAY(A2,1)=1,"日",IF(WEEKDAY(A2,1)=2,"月",IF(WEEKDAY(A2,1)=3,"火",IF(WEEKDAY(A2,1)=4,"水",IF(WEEKDAY(A2,1)=5,"木",IF(WEEKDAY(A2,1)=6,"金","土"))))))

とんでもなく、長い数式になっています。

これでは、可読性が悪いですし、ミスも起こりやすくなります。

それならば、IFS関数をつかってみたらどうなるのでしょうか。

D2の数式は、IFS関数とWEEKDAY関数をつかっています。

=IFS(WEEKDAY(A2,1)=1,"日",WEEKDAY(A2,1)=2,"月",WEEKDAY(A2,1)=3,"火",WEEKDAY(A2,1)=4,"水",WEEKDAY(A2,1)=5,"木",WEEKDAY(A2,1)=6,"金",TRUE,"土")

IF関数よりかは、IFS関数はIFの中にIF関数をいれていないので、可読性は改善されていますが、WEEKDAY関数が曜日ごとにでてきますので、作成時にミスを起こす可能性は高いかと思われます。

WEEKDAY関数を何度もつかうというのが、可読性が悪化する要因ならば、LET関数をつかったらどうなるのか、確認してみましょう。

LET関数とWEEKDAY関数を組み合わせたのが、E2の数式です。

=LET(yb,WEEKDAY(A2,1),IF(yb=1,"日",IF(yb=2,"月",IF(yb=3,"火",IF(yb=4,"水",IF(yb=5,"木",IF(yb=6,"金","土")))))))

だいぶ、改善されましたが、LET関数自体の馴染みがないので、理解する必要がありそうです。

なお、LET関数は、数式内で変数を定義し、その変数を繰り返し利用できる関数です。

これにより、同じ計算や値を何度も書く必要がなくなり、数式の可読性や効率が大幅に向上します。

LET関数の引数は、=LET(変数1, 値1, 変数2, 値2, ..., 計算式) となっており、最初に変数とその値を定義し、最後にその変数を用いた計算式を記述する関数です。

それならば、WEEKDAY関数が何度も出てこない関数はないものでしょうか。

F2には、SWITCH関数をつかってみました。

=SWITCH(WEEKDAY(A2,1),1,"日",2,"月",3,"火",4,"水",5,"木",6,"金",7,"土")

IF関数やIFS関数をつかうよりも、可読性もいいですし、数式自体もシンプルですね。

さらに、シンプルになるのが、G2のCHOSE関数を使った場合です。

=CHOOSE(WEEKDAY(A2,1),"日","月","火","水","木","金","土")

CHOOSE関数は、SWITCH関数とことなり、1ならばという設定をしなくても、自動的に1ならばという設定になっています。

これまでは、WEEKDAY関数をつかうと、数値が算出されて、曜日がわからないので、他の関数と組み合わせていましたが、一番効率がいいのは、TEXT関数だと思います。

H2の数式は、

=TEXT(A2,"aaa")

たったこれだけです。

TEXT関数は、表示形式を反映する関数です。

”aaa”とするだけで、日付を曜日に変更することができます。

関数の組み合わせによって、結果は同じですが、可読性・修正しやすい数式のほうがいいように思えますね。

Excel。空いている時間はいつ。複数人の出席可能時間を見つけたい【Gather everyone】

Excel。空いている時間はいつ。複数人の出席可能時間を見つけたい

＜IF+CONCAT関数+REPT関数＞

会議参加予定の空いている時間を見つけて、その日時で会議を開催したいと考えています。

下記のような予定になっています。

参加予定の3名。

それぞれ時間ごとに、○✕で、時間が空いているかどうかの確認はできます。

ただし、この3名がすべて○になっている時間を見つけようとしたら、大変です。

今回は、紹介用なので、3名で3日間の4時間ですが、人数が多くなればなるほど大変な作業になることは間違いありません。

IF関数をつかってみるとしたら、それも大変です。Aさんの10時は○かどうかを判断して、さらにBさんCさんの10時というような数式をつくるとしたら、面倒でしかありませんし、何より非効率です。

そこで、文字結合のCONCAT関数をつかうことで、簡潔に解決することができます。

では、B2をクリックして、CONCAT関数をつかった数式をつくります。

B2に設定した数式は、

=IF(CONCAT(B8,B14,B20)=REPT("○",3),"OK","")

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

これで、この3名が空いている時間は、

12月1日の10時と11時

12月2日の14時

ということがわかりました。

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

全員空いているならば、OKと表示したかったので、IF関数をつかっております。

IF関数の論理式ですが、

3名の12月1日10時を確認したい。

そこで、CONCAT関数をつかって文字結合します。

CONCAT(B8,B14,B20)

こうすることで、○○○　のように文字結合することができます。

この結果が、○○○　と等しいかという論理式をつくるわけです。

今回は、3名なので、次のように数式をつくってもよかったのですが、

=IF(CONCAT(B8,B14,B20)=”○○○","OK","")

人数が多くなると、○の数が多くしなければなりません。

仮に、10名だと○が10個。

入力するのも大変ですが、ミスが発生しやすくなります。

そこで、文字を繰り返す関数のREPT関数をつかって、○を入力する作業を簡素化します。

REPT("○",3)

とすることで、○を3回繰り返すので、○○○という文字をつくることができます。

このように、CONCAT関数、さらにREPT関数をつかうことで、空いている時間を手早く見つけることができます。

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関数をつかう必要があります。