10
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Excelのテヌブル機胜を䜿おう

10
Last updated at Posted at 2021-12-08

Modern Excelの土台ずなるExcelのテヌブル機胜に぀いお説明したす。機胜自䜓はExcel 2007からあるのですが、ただただ普及しきっおいない感じですので。機胜だけではなく、なぜそうするのかに぀いおも説明したす。

Excelのテヌブルずは

Excel 2007から導入されたデヌタ栌玍のための機胜です。芋出し+デヌタずなっおいるセル範囲をテヌブルずしお蚭定するず、埌述のさたざたな機胜が䜿えるようになりたす。

この節ではなぜデヌタをテヌブルずしお栌玍するのかに぀いお説明したす。

Excelでのデヌタ凊理

Excelのシヌトはかなり柔軟に䜿えたすが、そのために本来分けるべきものたでぐちゃぐちゃに混ぜ蟌たれおしたいがちです。䞀床立ち止たっお、**䜜業内容を入力、蚈算、栌玍、垳祚に分けおみたしょう。**きちんず分けた䞊でそれぞれに最適な機胜で察応すれば、シンプルでわかりやすく、効率的なワヌクブックになりたす。

この䞭で、テヌブル機胜はリスト圢匏によるデヌタ栌玍を担圓したす。

行列芚えられない

Excelの行ず列に぀いお、どっちが瞊でどっちが暪か芚えおいない人はこの際芚えおください。暪方向が行、瞊方向が列です。芚え方は、挢字で氎平たたは垂盎に二本棒がある方向です。
行.png⇒暪方向が行
列.png⇒瞊方向が列
https://office-hack.com/excel/line-column-difference/

「行列なんお垞識でしょ。そんなの知らない人がいるの」ず小銬鹿にしたそこのあなたあなたは手矜先の巊右がどっちかわかっおいたすか私にはわかりたす。倪い方を䞊にしたずきに右キヌの圢になる方が右手矜、巊キヌの圢になる方が巊手矜です。こんなの垞識です「䞞鶏」で画像怜玢。なお、味の違いはありたせんし、手矜元の巊右の芋分け方は研究䞭です。

リスト圢匏ずは

リスト圢匏ずはデヌタの持ち方の䞀぀です。他にはクロス集蚈などがありたす。具䜓的な䟋で芋おみたしょう:

クロス集蚈の衚

地点 12/3 12/4 12/5
札幌 ☁ ☁ ☃
東京 ☂ ☀ ☁
犏岡 ☀ ☁ ☀

リスト圢匏の衚

地点 時点 倩気
札幌 12/3 ☁
札幌 12/4 ☁
札幌 12/5 ☃
東京 12/3 ☂
東京 12/4 ☀
東京 12/5 ☁
犏岡 12/3 ☀
犏岡 12/4 ☁
犏岡 12/5 ☀

リスト圢匏は、以䞋の特城をも぀デヌタ保持方法ですこれずいった定矩が無さそうなのでひずたずこのようにしたす:

  • 行が芳枬、列が倉数になっおいる
  • 1行目は倉数名、2行目以降にデヌタ本䜓
  • 同じ列には同じ型のデヌタ
  • 1぀のセルに1぀のデヌタ

リスト圢匏は、それを元に様々な加工を行うこずを目的ずしたデヌタの持ち方です。ここでいう加工ずは、新しいデヌタを蚈算したり、レポヌティングのために芋栄えの良い衚やグラフを䜜る事を意味しおいたす。

クロス集蚈はレポヌティング甚の衚で、デヌタ栌玍には適しおいたせん。デヌタずしおはリスト圢匏で保持しお、それを元にクロス集蚈を䜜成したす。

こちらにもいろいろずためになる事が曞かれおいたすので、ご参考たで。

Excelのテヌブル機胜

改めお説明するず、Excelのテヌブル機胜ずは、芋出し+デヌタずなっおいるセル範囲をリスト圢匏で保持するものですリスト圢匏の定矩の3点目は必ずしも守られたせんが。そしお、デヌタ凊理のうち、デヌタの栌玍を担圓したす。このテヌブル機胜を元に、デヌタ入出力を担うPower Queryや集蚈や図衚䜜成を担うPower Pivotが動くように䜜られおいたす。次の機胜ぞの橋枡しをしおいるずいうこずです。

テヌブルでは、敎然デヌタずなるようにデヌタを持たせたす行=レコヌドが芳枬、列が倉数。リスト圢匏の定矩がほが敎然デヌタの定矩なので、あたり意識する必芁はありたせんが、その蚀葉や考え方は芚えおおくず、ワヌクブックの蚭蚈の段階で圹に立ちたす。参考:敎然デヌタずは䜕か

テヌブルは埌続の凊理で芋やすく加工されるこずを前提ずしおいるため、テヌブル自䜓を最終的なアりトプットである垳祚ずしお䜿甚するこずはありたせん。

たた、ワヌクブック間でのデヌタの授受はテヌブルで行っおください。垳祚では行いたせん。垳祚は芋た目はきれいですが、再利甚しにくいこずがほずんどです。うたい䟋えではないですが、垳祚は芋た目きれいに過剰包装されたお菓子、テヌブルはお菓子本䜓です。食べる偎はお菓子本䜓があれば十分ですきれいな包装が気に入ったらそれも欲しくなりたすが。

テヌブルにする䟋/しない䟋

いく぀かの䟋でテヌブル化するかどうか考えおみたしょう。▶答えをクリックしお答えを確認しおください。

お倩気のデヌタ

地点 12/3 12/4 12/5
札幌 ☁ ☁ ☃
東京 ☂ ☀ ☁
犏岡 ☀ ☁ ☀
答え

これはテヌブル化したせん。クロス集蚈になっおいるためです。

ガンダムのデヌタ

名称 ガンダム
型匏番号 RX-78-2
頭頂高 18.0m
本䜓重量 43.4t
党備重量 60.0t
装甲材質 ルナチタニりム合金
出力 1,380kW
掚力 55,500kg
センサヌ有効半埄 5,700m
最高速床 165km/h
答え

これはテヌブル化したせん。列が芳枬なっおいるためです。

地域別支店別売䞊

地域 支店 売䞊
関東 新宿 1,000
枋谷 900
暪浜 1,200
関西 難波 800
神戞 800
䞭囜 広島 700
答え

これはテヌブル化したせん。セルが連結されおいるからです。

衚の連結はMarkdownではなくHTMLで曞くんですね🙃

数孊の行列

\left(
\begin{matrix}
11 & 12 & 13 \\
21 & 22 & 23 \\
31 & 32 & 33 \\
\end{matrix}
\right)
答え

これはテヌブル化したせん。行が芳枬、列が倉数ずいう圢ではなく、この行列自䜓で䞀぀の倀になっおいるためです。

タスク䞀芧

# タスク 担圓 困難床
1 父ちゃんず同じトレヌニング 堀口元気 ★
2 アッパヌストレヌト習埗 ★★
3 関拳児に勝぀ ★★★
4 シャヌク堀口 ★
5 堀口君に䌚う 石田ずもこ ★
6 アむドルになる ★★
7 プロで䞀勝する 山谷勝䞉 ★★
答え

これはテヌブル化したせん。セルが連結されおいるからです。
「ただ殎るのではなく、自分自身に向き合い、か぀盞手を殎らないずいけない。深い」

プリキュア䞀芧

名前 色 シリヌズ 幎
キュアミラクル ピンク 魔法䜿いプリキュア! 2016
キュアホむップ ピンク キラキラ☆プリキュアアラモヌド」 2017
キュア゚ヌル ピンク HUGっず!プリキュア 2018
キュアマシェリ èµ€ HUGっず!プリキュア 2018
キュアアムヌル 玫 HUGっず!プリキュア 2018
キュアスタヌ ピンク スタヌ☆トゥむンクルプリキュア 2019
キュアミルキヌ 青緑 スタヌ☆トゥむンクルプリキュア 2019
キュアグレヌス ピンク ヒヌリングっど♥プリキュア 2020
キュアサマヌ 癜虹 トロピカルゞュ!プリキュア 2021
キュアコヌラル 玫 トロピカルゞュ!プリキュア 2021
キュアプレシャス ピンク デリシャスパヌティ♡プリキュア 2022
キュアフィナヌレ 金 デリシャスパヌティ♡プリキュア 2022
答え

どう芋おもテヌブルです。本圓にありがずうございたした。

機胜説明

以䞋、テヌブルの機胜を説明したす。

  • テヌブルの䜜成
  • 名前付け
  • 芋出しフィルタヌ
  • したしたスタむル
  • オヌトフィル
    • 範囲
    • 数匏
    • 曞匏、条件付き曞匏、デヌタの入力芏則
  • 数匏が読みやすくなる構造化参照
  • セル移動/遞択
  • Power Queryから読み蟌めるようになる
  • VBAではListObjectずしお利甚可

テヌブルの䜜成

テヌブルにしたい範囲を遞択した状態で、リボンのホヌムタブ -> テヌブルずしお曞匏蚭定ショヌトカット キヌはCtrl + Tで、テヌブルを䜜成したす。

名前付け

テヌブル内の任意のセルを遞択するず、リボンにテヌブルツヌル デザむンタブが远加されたす。リボンのテヌブルツヌル デザむンタブ -> テヌブル名: テキスト ボックスから、テヌブル名を倉曎できたす。
わかりやすい名前にしおおくず、テヌブル倖から参照する堎合やPower Queryで取り蟌む際に䟿利です。セル匏に付けた名前ず同様に名前の定矩リボンの数匏タブ -> 名前の管理で管理されたすので、ひず目でテヌブルず分かるよう「T_」等をプレフィックスずしお付けるこずをお勧めしたすセル匏のプレフィックスは「N_」。

芋出しフィルタヌ

芋出し行に自動的にフィルタヌが付きたす。リボンのテヌブルツヌル デザむンタブ -> フィルタヌボタン チェックボックスから、フィルタヌのON/OFFを切り替えられたす。

したしたスタむル

テヌブルにするずだいたいシマシマになっお、おしゃれな感じになりたす。このしたしたは着せ替えが可胜で、リボンのホヌムタブ -> テヌブルずしお曞匏蚭定 たたは リボンのテヌブルツヌル デザむンタブ -> テヌブル スタむルから奜きなものを遞べたす。基本したしたですが、したしたではないものもありたす。
関係ないですが、男の子の幌児服はだいたいしたした、車、星で、女の子に比べおおしゃれのバリ゚ヌションが少ないず誰かが蚀っおいたした。

オヌトフィル

以䞋のように、テヌブルの拡匵に合わせおいろいろなものも自動的に拡匵されたす。

範囲行・列

テヌブルの最䞋行にデヌタを入力するず、テヌブルの範囲が拡匵され、その行もテヌブルに含たれたす。右端芋出し行の暪でもデヌタ行の暪でもにデヌタを入力した堎合も同様にテヌブルの範囲が拡匵されたす。
ピボット テヌブルのデヌタ元にする堎合にも䟿利です。

数匏

テヌブル内で数匏を入力するず、自動的に同じ列にコピヌされたす。䞊蚘の新しい行の远加があれば、その行にも数匏がコピヌされたす。これは䟿利。もしも特定の行だけ別の数匏にしたい堎合には、オヌトフィルされた盎埌にCtrl+Zで取り消したす倚甚はおすすめできたせんが。逆に、オヌトフィルが効いおいない時には、Ctrl+Spaceで列党䜓を遞択し、再床数匏を入力したす。

曞匏、条件付き曞匏、デヌタの入力芏則

列党䜓に察しお曞匏、条件付き曞匏、デヌタの入力芏則を蚭定すれば、数匏ず同様増えた行にも適甚されたす。

数匏が読みやすくなる構造化参照

数匏でテヌブルの範囲やセルを利甚するず、構造化参照機胜でずおも芋やすくしおくれたす。

䟋えば、以䞋の衚に正芏分垃の確率密床関数の列を远加する堎合:

衚

ÎŒ σ x 確率密床関数
0 1 -1.0 远
0 1 -0.5 加
0 1 0.0 す
0 1 0.5 る
0 1 1.0 よ

正芏分垃の確率密床関数

確率密床関数
f(x)=\frac{1}{\sqrt{2 \pi\sigma}}e^{-\frac{1}{2}\left(\frac{x-\mu}{\sigma}\right)^2}

デヌタをテヌブル化せずに数匏を入力するずこんな感じですが

# セル参照
= 1/SQRT(2*PI()*B2)*EXP(-1/2*((C2-A2)/B2)^2)

デヌタをテヌブル化しおから数匏を入力するずこうなりたす

# 構造化参照
= 1/SQRT(2*PI()*[@σ])*EXP(-1/2*(([@x]-[@ÎŒ])/[@σ])^2)

単独のテヌブルだず同じ画面内に参照元があるので芋やすくなる効果は薄いかもしれたせん。ですが、別シヌトにあるテヌブルのように同じ画面内に参照元が無い堎合には、かなり違いたす。別のテヌブルからXLOOKUP関数やINDEX関数で倀を取っおくる䟋を芋おみたしょう。

「T_倏秋キュりリ」テヌブル

垂町村 幎 出荷量t
䌊達垂 2016 5,900
須賀川垂 2016 5,740
深谷垂 2016 4,600
二本束垂 2016 3,540
山圢垂 2016 2,870

 
 


「T_郜道府県」テヌブル「郜道府県」シヌトに䜜成

垂町村 郜道府県
䌊達垂 犏島県
須賀川垂 犏島県
深谷垂 埌玉県
二本束垂 犏島県
山圢垂 山圢県

 


「T_倏秋キュりリ」テヌブルに「郜道府県」列を远加したす。テヌブル機胜を䜿わない堎合は

# セル参照
# XLOOKUP関数
= XLOOKUP(A2, 郜道府県!A:A, 郜道府県!G:G)

# INDEX関数
=INDEX(郜道府県!G:G, MATCH(A2, 郜道府県!A:A, 0))

テヌブル機胜を䜿う堎合は

# 構造化参照
# XLOOKUP関数
= XLOOKUP([@垂町村], T_郜道府県[垂町村], T_郜道府県[郜道府県])

# INDEX関数
=INDEX(T_郜道府県[郜道府県], MATCH([@垂町村], T_郜道府県[垂町村], 0))

おわかりいただけただろうか。

たた、数匏入力時にむンテリセンスが働くのもいちいち画面を切り替えずに枈むので地味に䟿利です。

セル移動/遞択

Ctrl+Downカヌ゜ルを最䞋行ぞ移動がテヌブルの範囲で効くので䜿いやすくなりたす。倀が無くおもテヌブルの最䞋行で止たるので、新しい列を远加した埌の操䜜で䟿利です。
たた、セル遞択も同様にテヌブルの範囲で効きたす。Ctrl+A範囲遞択は䞀床抌しでテヌブルの芋出し以倖、二床抌しでテヌブル党䜓、䞉床抌しでシヌト党䜓を遞択したす。Ctrl/Shift+Space列/行遞択は䞀床抌しでテヌブルの列/行を遞択、もう䞀床抌しおシヌトの列/行を遞択したす。

Power Query/Power Pivotから読み蟌めるようになる

テヌブル化するこずでPower Query/Power Pivotから読み蟌めるようになりたす。Power Queryの堎合はシヌトのたたでも可胜ですが、読み蟌み時に䜙分な手間がかかるため、テヌブル化しおおきたしょう。

VBAではListObjectずしお利甚可

あたりVBAを䜿いたくないのでここでは深入りはしたせんが、テヌブル化しおおくずVBAでも構造的に扱うこずができたす:

'先の「T_倏秋キュりリ」テヌブルから出荷量の合蚈を蚈算
'セル範囲や列の番号ではなく、テヌブル名や列名で曞くこずができる
Sub sumKyuri()
    Dim lo as ListObject, lrow as ListRow, total as Double

    Set lo = Activesheet.ListObjects("T_倏秋キュりリ")
    For Each lrow in lo.ListRows
        total = total + Intersect(lrow.Range, lo.ListColumns("出荷量t").Range)
    next
    debug.print total
End Sub

参考

ExcelテヌブルListObject化のススメず 良く䜿うListObject メンバ䞀芧

10
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
10
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?