テーブルと一緒に使うといい感じの関数☆

2024年8月3日2024年8月9日

テーブル機能の便利さは、この記事で書きました。

テーブルって使わなきゃExcelの魅力半減だよ☆

新しいデータをテーブルの下の行に入れたら自動で計算式が上の行と同じものが入ったり、1つのセルに計算式を入れるとその計算式が列全体に自動で入ったり、計算式を使う上でも、ものすごく便利でした。

じゃあ、テーブルと組み合わせて、どんな関数が活躍できるの？をちょっと考えてみました。

テーブルのデータと関数って、テーブルの中に入れて便利な関数と、テーブルのデータをほかのところから見て便利な関数があるって思ったんですよね。

Excelって集計が得意じゃないですか。テーブルは一覧表が得意じゃないですか。

一番最後はExcelって集計して終わりだよね、って思ったんです。

集計するにはテーブルのデータそのまま使ってもいいし、もしかしたらVLOOKUP関数みたいなので他の表からデータをくっつける必要があるのかもしれないし、なんかの文字の一部分が値段だったりしたら、その値段を取り出さなきゃいけないから文字列を抜き出す関数とかでなんとかする必要があるかもしれないです。

そんか感じでテーブルのデータを集計の材料になるデータにして、そのあとにテーブルの中のデータをSUMIF関数とかで集計するってことをしていくって流れですよね。

ちょっとその2通りについて考えました。

テーブルの中でいい感じの関数

テーブルの中でいい仕事をしてくれる関数は、同じ行のデータを加工してくれる関数かなと思いました。
そのままでは集計できないときの集計前の下準備をするんですね。
ものすごく大事なことをしてくれる優秀な子！！

同じテーブルの同じ行のデータを計算式で参照するときは、セル参照ではなく、「@[項目名]」と入力するとそのデータを使うことができます。

VLOOKUP関数、XLOOKUP関数

その代表はVLOOKUP関数？かもですね。
ほかの表からデータをもってきて、今のテーブルにないデータをくっつける関数です。

代表的なのは、商品一覧表から値段を持ってくる例ですね。さとうせんせいのVLOOKUP関数の問題にはだいたいこれ入ってます。同じ行の商品の名称を商品一覧表で見て、その値段を持ってくるということをします。

VLOOKUP関数は、ほかのテーブルのデータ範囲を参照するときもあるのですが、その指定は、テーブル名を書けばいいだけです。

XLOOKUP関数はVLOOKUP関数で使いにくかったところをいろいろ直して最近生まれた関数です。
VLOOKUP関数は求める値を列で指定するんですけど、XLOOKUP関数は求める列を範囲で示せるというのが特徴です（もっともっとできることあるけど）。
なんかXLOOKUP関数のほうが優秀っぽいですけど、なんかあたしはVLOOKUP関数でできるものはVLOOKUP関数でやっちゃうなぁ。
XLOOKUP関数は元の表で参照するのが一番左にないときにVLOOKUP関数じゃないからできないときに使うんですよね。

LEFT関数、MID関数、RIGHT関数

これらの関数は文字列操作関数ですね。ある１セルの文字列の中から、決まった部分の文字を切り出す関数ですね。

よく商品番号の一部が商品の色の英語表記のはじめの1文字とかになっていて、商品の色はそれを見ればわかるときによく使います。

CONCAT関数、TEXTJOIN関数

CONCAT関数はLEFT関数とかとは逆に、セルの中の文字同士をくっつける関数です。CONCAT関数はそのままつくっつけるだけなので読みにくくなる時があるんですけど、間に区切りの文字を入れる場合はTEXTJOIN関数を使います。商品名と内容量と成分みたいなのが列で別れて記録されているとき、それが全部くっついたものが商品の正式名称だったりするので、そういうときに使います。あまりそういう例はないかもしれません。CONCAT関数やTEXTJOIN関数よりLEFT関数たちのほうがよく使います。

YEAR関数、MONTH関数、DAY関数、DATE関数

集計するときに仕分けする項目として、年や月で区分けするときに使います。YEAR関数、MONTH関数、DAY関数は1つのセルに入っている年月日を、それぞれ年、月、日の部分だけ数字として取り出す関数です。

DATE関数はその逆に3つのセルにバラバラに入っている年と月と日のデータをくっつけて一つの日付データを作る関数です。

集計してほしいってもらったデータってどんな形で年月日が入ってるかわからないので、こういった関数で加工しなきゃいけないときがあるんですよ。

ちなみに曜日を求めるにはWEEKDAY関数っていうのがあるんですが、曜日を数字で表したものを出すのでちょっと使いにくいです。さとうせんせいはTEXT関数を使ってます。これも使い方は複雑なので、公式だけ暗記しておけばいいんじゃない？って言ってました。その公式は次です。

=TEXT(曜日を調べたいセル,"aaa")

こうすると日から土まで漢字で出てくれるので、曜日で仕分けしたいときは便利なんですよ。

IF関数、IFS関数、SWITCH関数、CHOOSE関数

この子たちは、仕分けする関数で、どれも似ているんですけど、使うシチュエーションによって使い分けるといい感じになる関数です。

IF関数って、そうなのかそうじゃないのかで判定する関数ですよね。2つに一つの答えになります。

その判定が複数になって、どんどんふるいにかけていくのがIFS関数です。

もし、ふるいにかける値がバラバラのセルじゃなくて1つのセルだったらSWITCH関数を使います。

評価が優秀なら対応は表彰、合格なら昇進、落第なら現状維持、問題なら補講ってなるような式です。

CHOOSE関数は、1から順番の番号によって値を変えるものです。

これはかなり特殊な例なんですが、点数を20点区切りで100点なら優秀、80点以上なら合格、60点以上なら落第、それ以下は問題というラベルを付けるために、点数を20で割って、割り切れないので小数点以下を切り捨てるINT関数を使って小数点以下をなくすと、点数が1から5の整数の5段階になるので、それでCHOOSE関数で処理しています。

微妙に全部違うんですね。

ROUND関数、ROUNDUP関数、ROUNDDOWN関数

Excelは実際には小数点以下の小さい数字がセルに入っていても、小さい値は表示しないで四捨五入で表示します。だから合計するとその見えなくなってる数字が積み重なってきて、見た目で計算したものとExcelで計算したもので違いが出ちゃい時があるんですよね。

それを実際の値として四捨五入するのがROUND関数です。

これは値引額を1000円単位にしたいので桁数を3にした例です。

同様にROUNDDOWN関数は切り捨て、ROUNDUP関数は切り上げです。

ROUND関数は四捨五入の桁を数字の桁数で指定するので、これがとってもわかりにくいんです。

だから、四捨五入を自由な数字でできるMROUND関数というのもあります。さとうせんせいはこっち派です。MROUND関数に対して切り上げはFLOOR.MATH関数、切り捨てはCEILING.MATH関数です。

上と同じ千円単位の切り捨てをFLOOR.MATH関数で、切り捨ての単位は1000とそのまま入るのでイメージしやすいです。

IFERROR関数

集計するときに、元のデータ範囲にエラーがあると、集計した値もエラーで計算されちゃうんですよね。エラーを無視してくれてもいいのに。エラーを無視する関数もあるんですけど使うのが面倒なんですよね。
もしエラーがあったらそれに代わる値を入れてくれるのがIFERROR関数です。

エラーに代わる値は空白だったり、0だったり、集計の方法によって変わりますが、0を入れると平均の値とかはおかしくなるので注意しましょう。

テーブルの外でいい感じの関数

テーブルの外でいい動きをしてくれるのは、集計する関数ですね。元のテーブルにあるデータっていっぱい書いてあってどうしても見にくいじゃないですか。それを全体的に区分けして集計するのが集計関数です。

テーブル以外の部分からテーブルを参照しますので、参照するテーブルは計算式の中ではテーブル名を記載するとそのテーブルのデータ全体が選ばれます。

COUNTIF関数、COUNTIFS関数

条件に当てはまったものの件数を数える関数です。条件が一つの場合はCOUNTIF関数、条件が複数の場合はCOUNTIFS関数です。

COUNTIF関数で食事を回数を数えてみました。

その食事回数をメニューごとのほかに、時間ごとの回数を知りたいとなると複数の条件ということになるのでCOUNTIFS関数の登場です。

COUNTIF関数、COUNTIFS関数はアンケート集計でもよく使う関数ですね。

SUMIF関数、SUMIFS関数

条件に当てはまったものの指定した項目の合計を求める関数です。条件が一つの場合はSUMIF関数、条件が複数の場合はSUMIFS関数です。

合計なので、テーブルに記載されている金額や個数を集計するときに使えそう。

メニューごとに記録されているカロリーをまとめてみました。

SUMIF関数では商品ごとの集計、SUMIFS関数では商品ごとと取引先ごとに同時に集計した表を作れますね。

売上金額と費用って普通2つの項目に分けて入ってるので、2つあるからってSUMIFS関数を使いたくなるんですけど、あくまで商品ごとの1つの条件ならSUMIF関数で、SUMIF関数を売上金額と仕入金額で2つ作るような形になるんですよね。

でもこれも、売上か仕入のどっちかって記録してる項目があって、金額が1列にまとまってると、売上なのか仕入なのかっていうのも条件になるのでSUMIFS関数を使うことになります。

こういう違いもあるので、どの関数が一番適しているか、元になる表をよく見て集計しろっていつもさとうせんせいは言ってます。

UNIQUE関数

UNIQUE関数は新しい関数で、範囲の中にある同じデータを省く、重複を削除するという役割の関数です。重複を削除したものっていっぱいでてくるので1セルに収まらないじゃないですか。だから、この関数は1つのセルじゃなくてたくさんのセルに答えを出す、「スピル機能」を使った関数です。

データの中から重複を削除するので、1列に入力されているのが商品名だとしたら、同じ商品名を省略して1つの商品だけにします。ってことは商品名一覧表を作ることができます。

UNIQUE関数で作った範囲はセル参照の後に#を付けると計算式の中で使えるようになっているので、SUMIF関数やCOUNTIF関数で商品名を指定するときにこの#のセル参照を使うと、1つの計算式で範囲にSUMIF関数などの答えを出してくれます。この時、絶対参照も意識しなくていいんです。もうスピル最高です！

スピルはExcel2021以降、またはMicrosoft365でしか使えない新しい機能です。

まとめ

テーブルと一緒に使う関数を優先して覚えると、Excelの関数が覚えやすいかもって思いました。
テーブルを使うと集計するって目的があるから、その関数が何のためにどんな風に動くのか具体的にイメージできますよね。

テーブルの中で使うデータを整理する関数も、テーブルの外で使うデータを集計する関数も、ほんとはもっともっとあるんですけど、いつか紹介したいなって思います。

テーブルの外で使うって紹介したCOUNTIF関数は、テーブルの中で使うと重複したデータを見つけることができたりするので、目的によってはどっちがどっちかあいまいになるときもあります。
DATE関数は集計とは関係ないので、集計するためにテーブルの外で使うということはありえなくて、これはどっちで使うかはっきりしてるんです。
その時にしたいことが実現できるように関数は選んでいくとよいと思います。

テーブルのデータは追加すれば範囲が自動的に広がるし中に入ってる計算式も自動で増えるし、って考えると、自動的な集計するシートとかは関数使って作ればいいじゃん、って思います。
テーブルも集計関数も自動的にいろんなことしてくれるので最高なんですけど。