タグ別アーカイブ: IF関数

もしもし問題を解決する~IFS関数&LET関数~

 エクセルの関数を覚えていくと、そのままエクセル作業の効率は上がっていきます

そして、エクセル関数の使用方法が習熟してくると必ず次の問題が起こります

関数を組み合わせている内に、長く複雑な数式になってします

 長く、複雑な数式で一番やっかいなことの1つは、IF関数の中に更にIF関数を入れていくもしもし問題です

 条件が増えれば増えるだけやっかいな数式になり、作った人以外は誰も直せなくなります

 今回は、この問題をIFS関数とLET関数を使って解決していきます

まず、今回の取り組むテーマについてお話します

下の表は個人の成績表です

 この表からMAX関数を使って、各人の最高得点を出力しておき、更にIF関数を使って、最高得点科目を出します

 このテーマを解決するのに、従来のIF関数を使用した場合には、下の画像のように2つのIF関数を組み合わせねばなりません

ちなみにセルF4では、前述のようにMAX関数を使用しています

 筆者も5科目でテーマを作りたかったのですが、数式が複雑すぎて説明に困ると考えたので3科目にしました

さて、ここでIFS関数をIF関数の代わりに使用してみます

こちらはIFS関数は1つ使用するだけですみます

IFS関数は複数の条件式があるときに便利な関数です

IF関数の場合ですと

 =IF(条件式,出力/条件式に合致,出力/条件式に合致しない)

という数式の書き方になりますが

IFS関数の場合ですと、

 =IFS(条件式1,出力/条件式1に合致, 条件式2,出力/条件式2に合致,・・・,TRUE,出力/左記の条件式にいずれも合致しない場合)

という数式の書き方になります

この数式のポイントは2つあります

①条件式と出力の並べ方

 条件式⇒条件式に合う場合の出力⇒出力⇒条件式に合う場合の出力

 という並び順になり、IF関数の場合の一)条件式、ⅱ)出力(合致)ⅲ)出力(合致しない)の場合と違うこと

➁条件式に合致しないもの

 これは英語で言えば、ELSEのことですが、IFS関数では

 「,TRUE,出力(いずれにも合致しない)」

と書きます

さて、ここでLET関数を使って、IFS関数をもっとシンプルにします

LET関数とは関数の中に変数を使うことができる関数です

変数とは方程式のXと考えて頂ければ幸いです

方程式もXを使うことで式がシンプルになっています

 今回は上の表でIFS関数の外で求める最高得点(MAX関数の出力結果)を方程式に組み込みます

まず、LET関数の文法について解説します

=LET(変数名(X),変数の値(X=?),実際に変数を使った式)

変数(X)を最初に宣言するのがポイントです

今回の事例では、最高得点をMaxの名で宣言して、実際にMAX関数で最高得点を算出しておきます

そして次に、IFS関数にて変数:Maxを条件式内に使います

セルを通じて参照するよりずっと分かり易い数式になっているはずです

LET関数を使わない場合には、上の数式のようにF4セルを参照し、F4に最高得点が入っている、という意味合いを確認せねばなりません

<まとめ>

今回は、IFS関数を使い、複数条件が存在する数式を分かり易くする方法について紹介しました

更に、LET関数でIFS関数の中味を方程式のXのように分かり易くする方法についても触れました

IFS関数もLET関数も最新の関数です

ぜひ、この新たな2つの関数を有効活用して、「エクセル業務」の効率を向上させていきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

関数をマスターする3~IF関数2~

前回に引き続き、IF関数を解説します

繰返しになりますが、IF関数を覚えるとエクセル活用の仕方はだいぶ深くなりますので

しっかり取り組みましょう!

今回取り組む内容は以下です

①IF関数の中にIF関数を更に加えて、判断条件を複数組み合わせる

②前回は判定条件にEXACT関数を入れたが、今回は直接、判断条件を入力する

この内容が出来れば、更にIF関数を有効に活用することができます

では前回と同じサンプルファイルを使用して取り組みましょう!

アイコン

関数1 560.58 KB 8 downloads

...

5

上記の画面で3つのセルの値が一致していたら、”全一致”を出力しましょう

一方、一つのセルのでも違っていたら”不一致”を出力しましょう

つまり、3つの値を比較しますが、最終的には全一致、不一致の2つに分けるのです

では2つのステップで完成させましょう

1.まずは1つ目のIF関数を作成しましょう!

B3セルとB4セルの内容が=(イコール)の時に”全一致”を出力しましょう

一方、それ以外の時には”不一致”を出力します

6

では次に考えるのは、ここから更に条件が分かれるのは次のどちらでしょう

”全一致”の場合

”不一致”の場合

少し考えてから、次に進みましょう

2.2つめのIF関数を入力しましょう!

3つの値を比較しますが、最終的には全一致と不一致の二つに分かれます

ですから、1.で更に2つに分かれるのは全一致のケースです

もう少し解説しましょう

●全一致のケース

①B3=B4 AND B4=B10

●不一致のケース

②B3<>B4 AND B4=B10

③B3=B4 AND B4<>B10

注)<>は不一致の意味

ここで1.のIF関数に当てはまると②のケースは既に不一致として出力されるようになっています

後は①と③の違いの判定です

ですから1.IF関数の”全一致”に①と③が一緒に組み込まれてしまっています

ここを更にIF関数で分けましょう9

すると以下のような動きになります

GIF3

今回はここで解説は終わりです

尚、条件の書き方については、=の他の<や>の不等式も使えます

では、また次回!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

関数をマスターする2~IF関数~

前回の記事に関して補足です

サンプルファイルでEXACT関数を試す際には、リストを活用して関数の正確性を試してみてください

GIF1

では、今回はIF関数について解説します

IFですから、日本語に直すと”もし・・・”ですよね

ちなみに小説を書く際(私は書いた事はありません)、ストーリーを構想するプロットなるものを書くそうですが、これは

主人公がもし・・・なら、

世界がもし・・・なら

といった感じでもし・・・うまく活用しながら書くそうです

 

話しはそれましたが、もし、をうまく活用すればエクセル活用の仕方は大分深まります

ここからは前回のサンプルファイルをベースに活用の仕方を解説します

アイコン

関数1 560.58 KB 8 downloads

...

1.IF関数の書き方

EXACT関数を思い出してください。引数を2つ入れると”TRUE”、”FALSE”、2種類の結果を取り出すことができます。

IF関数はこのEXACT関数の延長になります

引数は3つです

<=IF(1/条件式,2/条件式がTRUEの場合のアクション,3/FALSEの場合のアクション)

2.EXACT関数の結果を”一致”、”不一致”で書き換える(サンプルファイル:C7セル)

IF関数を使って前回のEXACT関数の結果を書き換えましょう

4

条件式にC3セルのEXACT関数を使い、

TRUEの場合:”一致”

FALSEの場合:”不一致”

となるようにIF関数を記入してみてください

結果は次のようになるはずです

GIF2

ちなみに、””のマークで囲むことでExcelは文字列で表示するという認識をすることができます。逆に””で囲まない場合にはエラーになります

ではまた次回、IF関数を更に深く解説していきます

 

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

テーブルを作成しよう~裏技集計~

前回はテーブル機能を活用して様々なデータ集計を行いました

今回はIF関数を使って更に深堀します!

話しはいきなりテーブル機能の話しと逸れますが、データ集計の際に便利なのが

<0と1を使いこなすことです>

今回はその1つのパターンとテーブル機能を組み合わせます

まず、サンプルファイルにIF関数を入れる為の列を挿入し、次のようなIF関数式を入力します

<=IF(条件式,1,0)>

つまり条件に合えば1、合わなければ0を出力します

ここで出力された”1”を合計すれば自動的に条件に合うデータの数を集計できます

IF関数の使い方を説明したところで、早速、テーブル機能と組み合わせてみましょう

1.条件に合うデータの数を集計する

サンプルデータの中で地域が”アメリカ”のデータ数を集計しましょう

GIF

2.条件に合うデータの構成比を算出する

これはの集計の仕方を平均にするだけです

平均の計算式を思い出してください

分子(1+1+1+0+0+0+・・・)÷分母(データ数)

11

計算式がしっくり行かなかった方は実際の例を考えてみてください

(例:データが2つあり、日本とアメリカだった場合、分子(1+0)÷分母(2)=0.5)

3.AND関数やOR関数を使って更に細分化されたデータ集計する

ここまでで使用した関数はIF関数のみでした

もう少し細分化されたデータ集計を行いたい方はAND関数とOR関数なども組み合わせることもできます

例えば、下の図のようにAND関数を組み合わせると

条件1:地域=”アメリカ” 且つ 条件2:年度=2017

の条件でデータ集計できます

12

また、下の図のようにOR関数を組み合わせると

条件1:地域=”アメリカ” もしくは 条件2:地域=”東京”

にて集計できます

13

前回も説明しましたが、単にデータ集計するならばピボットテーブルを作成して、集計条件をいじるよりも、テーブルを活用した方が圧倒的に早いです

ではまた次回
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

VLOOKUP関数とIF関数だけを使い倒す#4-1

【問題】

昨日の続きです。IF関数とIFERROR関数を組み合わせて次の条件で注文を3つに分類してください

1.会員住所=東京 且つ 注文商品=日本酒

2.会員住所=神奈川 且つ 注文商品=ウィスキー

3.その他、上記以外

キャプチャ#4_3

【回答】

複合キーを作るところまでは前回と一緒です

キャプチャ#4_1

そこからVLOOKUP関数の参照用に複合キーを検索値とした番号(1,2)の対応表を作成します

キャプチャ#4_2

既にお気づきの方もいらっしゃると思いますが、表にない複合キーの時にはエラーになってしまいます。

そこで”エラー”が出る点をうまく利用します

IFERROR関数ならば=IFERROR(関数、左記関数がエラーの場合の値)と指定できるので、エラーの場合は”3”と出力するようにします

IFERROR関数はとても便利なので有効活用しましょう!

 

VLOOKUP関数とIF関数だけを使い倒す#4

【問題】

分類の問題です。IF関数のみで以下の条件で注文を3つに分類してください

1.会員住所=東京 且つ 注文商品=日本酒

2.会員住所=神奈川 且つ 注文商品=ウィスキー

3.その他、上記以外

*但し、IF関数の中に組み入れる(ネストする)IF関数は1つまでとします

【回答】

複雑そうな問題に見えますが、いきなり解決しようとせず、新たに列を挿入してそこに何を入れるかを考えましょう

答えは”&”を活用して住所と注文商品を組み合わせた複合キーを作成するのです

そして、複合キーをIF関数で分類していくのです

(複合キー)

キャプチャ#4_1

(IF関数による分類)

キャプチャ#4_2

VLOOKUP関数とIF関数だけを使い倒す#3

【問題】

OR条件による抽出の問題です。”IF関数のみを使用し”、会員の住所が”東京”か(OR)、注文商品が”日本酒”か?いずれかの条件に当てはまる”注文番号”の個数をカウント、及び、抽出してください*最後はテーブル機能を活用してください

キャプチャ#3_1

【回答】

IF関数による0か1の判定及び小計列を有効活用します

1.IF関数で東京、もしくは日本酒の場合は1を出力するようにする

2.1で出力する価を小計する列を作成する

3.2で小計した値を1以上か?、もしくはそうでないか?を判定する

加えてテーブル機能により、列の集計を行います。

以下、詳細を解説します

・1について

キャプチャ#3_2

住所(C列)、注文商品(D列)の横に列を挿入し、IF関数で条件に該当するならば、”1”をそうでなければ”0”を出力するようにする

・2について

キャプチャ#3_3

1で出力する価をG列で小計しておく

・3について

キャプチャ#3_4

G列で小計した値が1以上であれば、”1”を、そうでなければ”0”を出力するようにします。この出力された1を集計(1を合計)、もしくは1の注文番号をフィルターで抽出すればそのまま”回答”になります

ここからはテーブル機能について説明します

テーブル機能はエクセルでデータを管理するのに、”痒いところ”に手を届くほどまで”データを扱いやすく”してくれる機能です

まず、管理したいデータのどこかのセルにカーソルを置き、Ctrl + Tを押します

#1GIF_2

次に、

デザインタブで、集計行にチェックを入れてください

キャプチャ#3_5

すると列の下に▼マークのボタンが出てくるので”合計”を選択します

#1GIF_3

ここではテーブル機能を紹介しきれませんが、とにかく便利な機能なので皆さん研究してみてください

サンプルで確認したい方は以下↓からダウンロードできます

VLOOKUP関数とIF関数だけを使い倒す#2

 

【問題】

IF関数を使用して、重複している会員NOからユニーク(重複していない)な会員番号の個数と会員番号を抽出する

*会員番号の個数はステータスバー(エクセル画面の下)で確認するものとする

キャプチャ#2_4

キャプチャ#2_5

 

【回答】

”値が重複している”、ということは値を昇順、もしくは降順で並べ替えると重複している価は”塊”で現れます。この性質を活かして3つのステップで重複しない値を”カウント”、”抽出”します

1.会員NOを並べ替え

2.IF関数を使用し、1行上と”一致していない”場合は1を出力する(逆に1値している場合は0を出力する)

3.IF関数を入力した列にカーソルを置く(ステータスバーでカウント)、フィルターで1のみを抽出(抽出)

*1の合計は自働的に該当する値の個数になります

キャプチャ#2_1

ちなみに下の画像のように合計がエクセル画面下に表示されない時には右クリックして合計にチェックを入れましょう

キャプチャ#2_2

キャプチャ#2_3

サンプルファイルで実際に確認したい人は以下↓をダウンロードしてください

 

ExcellentなDashboard作成演習#1 ~スマホ編 4回目~

今回はスマホ編の4回目になります(1回目2回目3回目

今回はピボットテーブルのシートを作成しますが、3つのことを行います

1.ピボットテーブルを作成してデザインを整える

2.ピボットテーブルの横にNOを割り当てる

3.スライサーを追加する

キャプチャ1

まずは4分程の動画をご覧ください

では1から詳細に解説します

まずDATAシートからピボットテーブルを作成したら、次のようにフィールドをセットします

キャプチャ2

次にピボットテーブルのデザインを変えます

まず、データを降順にします。これは最後にスマホで表示する時には販売個数の上位から見れるようにする為です

GIF1

次に総計を非表示にします。これは総計行をスマホに表示しないようにするためです

GIF2

いずれの2つもピボットテーブル上にカーソルを置いておかないと操作できませんので注意してください

次に”ピボットテーブルの横にNOを割り当てる”について説明します

最初に”ROW関数”について解説します。

これは行番号を表示する関数です。意外に使えるので覚えておくと得です。NOを手打ちで入力しておくと他の列のデータを並べ替えたときに一緒に並べ替えられたりしますが、この関数を使っておけば固定されます

使い方は単純に=ROW()とセルに入力すればいいのです

GIF3

次に=ROW()-3と入力してNOを調整するのですが、スマホで表示する時にデータが無い行にはNOを表示しないようにします

隣のセルが空白を表示する為に、IF関数を使用するのですが、動画ではIF関数の条件設定をセル<>””としましたが、セル=””としても大丈夫です。

ちなみに””は空白、<>は一致しないという条件になります

最後にスライサーについて説明します

GIF4

スライサーは”挿入”から”スライサー”をクリックすればできますが、デフォルトでは縦並びになっていますので、横並びにする方法を解説します

オプションタブ(スライサーにカーソルを置いたまま)にある列数を指定すればいいのです。指定した列分が横に並びます

例えば今回のサンプルデータで2列を指定すると2行X2列になります

GIF5

そして最後に色あいなどのスタイルを選択して今回は終了です

GIF6

ではまた次回!