Home > Excel エクセルの使い方-関数/計算式-検索・行列
Excel エクセルの使い方-関数/計算式-検索・行列
「エクセル 間引き」
「Excelでデータを間引く方法」
といった検索が行われているのを見かけることがあります。
大量のデータが存在するとき、何らかのルールに従ってデータを間引く方法を探している方による検索です。
実務でExcelを使っていると、オートフィルで
X
Y
Z
AA
AB
AC
といった入力をしたくなることがあります。
X・Y・Z
という並びは通常のアルファベットですから、CHAR関数とROW関数などを使うことで入力できます。
しかし「Z」につづく
AA・AB・AC
という並びはダメです。
ちなみに、AA・AB・ACというのはExcelの列番号です。
R1C1参照形式を使用する設定をしていない通常のExcelでは、
25列目 ＝ Y列
26列目 ＝ Z列
27列目 ＝ AA列
28列目 ＝ AB列
・
52列目 ＝ AZ列
53列目 ＝ BA列
54列目 ＝ BB列
・
とアルファベットを使った26進法で列番号は表示されます。
2003までのExcelは256列ありますからIV列まで、2007以降のExcelでは16,384列ありますからXFD列まで存在しています。
Excelで業務アプリを作っているようなときなどに、この列番号を入力する必要が出てくることがありますが、
X・Y・Z・AA・AB・AC・・
といった並びを手入力するとなるとかなり面倒です。
表の一番下のデータを取得するために、INDEX関数とCOUNTA関数を使う方法と、INDIRECT関数とCOUNTA関数を使う方法をご紹介しました。
表の一番下のデータを取得する方法は、他にもありますのでご紹介しておきます。
LOOKUP関数を利用する方法です。
INDEX関数とCOUNTA関数を組み合わせると、SUM関数で合計する範囲を自動的に更新できることをご紹介しました。
「合計範囲を自動的に更新するには？」
というご質問は、新たなデータが随時追加されるようなワークシートを作成している方から、いただく定番的な質問で、その実現方法もいろいろあります。
先日ご紹介した、INDIRECT関数とCOUNTA関数を使って一番下のセルを表示する方法を組み合わせても、合計範囲を自動的に更新することができます。
INDEX関数とCOUNTA関数を組み合わせ、表の一番下のデータを取得する方法をご紹介しました。
表の一番下のセルからデータを取得する方法はいろいろありますので、他の方法をご紹介します。
今回ご紹介するのは、INDIRECT関数とCOUNTA関数を利用する方法です。
最新のデータを表の下に追加していく場合に、一番下つまり最新のデータをシートの上のほうに表示させる方法をご紹介しました。
同じく最新のデータを表の下に追加していく場合に、
「合計する範囲を自動的に変更させるにはどうしたらいいのでしょうか？」
というご質問をいただくこともあります。
例えば、B列に売上データだけが追加されるなら「=SUM(B:B)」という数式で、合計を求める数式を更新しなくても、常に合計を求めることは可能です。
しかし、シートの作り方などの理由で「=SUM(B:B)」といった数式を使えないようなときに、
「合計範囲を自動的にさせるには？」
といったご質問をいただくことがあります。
「表の一番下のデータを表示させるにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
例えば、売上データをExcelで管理していて、最新のデータを表の下に追加していく場合に、一番下つまり最新のデータをシートの上のほうに表示させるにはどうしたらいいのかというご質問です。
実務でExcelをご利用の方からの定番的な質問の一つで、その実装方法もいくつかあります。
給与所得者の所得計算についてご紹介してきています。
14種類ある所得控除のうち、配偶者控除額の計算についてご紹介しましたから、関係の深い配偶者特別控除額の計算についてご紹介しましょう。
配偶者控除だけが存在していると、例えば、パートタイムで働いている妻の所得が、年間38万円（給与収入に換算すると103万円）を超えたとたんに、その妻を配偶者としている夫の所得税負担が急激に重くなってしまいます。
この急激な税負担の変化を緩和する役目を持っているのが配偶者特別控除です。
配偶者控除を補完するような意味を持った配偶者特別控除ですから、子ども手当創設と引き換えに、配偶者控除を見直すとしている民主党が政権を担当する場合、配偶者特別控除も当然見直される（廃止される）と推測されます。
納税者（妻がパートで働いているのなら、その夫）の所得が1,000万円以下で、配偶者（パートで働いている妻）の所得が38万円から76万円・給与収入に換算すると103万円から141万円のときに、配偶者特別控除は受けられます。
控除できる金額は、配偶者の所得によって9段階に分類されています。
配偶者の所得がいくらのときに、いくらの配偶者特別控除を受けられるかは、年末調整の手引きのP.75の配偶者特別控除の早見表に明示されています。
「VLOOKUP関数で新しいほうのデータを取得したいのですがどうすればいいのでしょうか？」
といったご質問をいただくことがあります。
VLOOKUP関数を使うと、例えば、商品コードを入力したときにその商品の単価を自動的に表示させるといったことができます。
実務では、商品マスタの履歴も残したいために、同じ商品コードで複数のレコードが存在するような作りにするといったことが行われたりします。
そんなときに、新しいほうのレコードを下に追加するという業務ルールにして、
「VLOOKUP関数で常に一番下のデータを取得したいのですがどうすればいいのでしょうか？」
といった質問をいただくことになります。
リレーショナルデータベースを扱っている方からすると、テーブルの中で上にあるか下にあるかだけで、レコードが新しいかどうかという判断をしようとすること自体がビックリかもしれませんが、Excelをデータベースに利用する方からこの手の要望をいただくことは、とてもよくあるのが現実です。。
先日、年末調整関連書類作成の第一歩：年調給与額の計算についてご紹介しました。
その続き「給与所得控除後の給与等の金額」の計算をご案内します。
実際の源泉徴収票では、「給与所得控除後の金額」に該当する値の計算です。
どう計算すればいいのかは、国税庁の配布している「年末調整の手引き」にしっかり記載されています。
AVERAGE関数を使って移動平均が求められることをご紹介しました。
この方法を知った方からは、
「平均を求める区間を可変にして移動平均を求めるにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。
単純にAVERAGE関数を使うだけでは、表示したい移動平均分の列を用意しておく必要があります。
例えば、
5日移動平均・25日移動平均・200日移動平均を表示したければ、
5日移動平均用数式・25日移動平均用数式・200日移動平均用数式
をそれぞれ入力しておく必要があります。
これを、いずれかのセルに例えば
「5」と入力すれば5日移動平均が、
「25」と入力したときには25日移動平均が
「200」と入力したときには200日移動平均が
表示されるようにしたいというのが、
「区間を可変にして移動平均を求めるには」
というご質問の主旨です。
INDEX関数を使うと、行・列を指定して値を取得できることをご紹介しました。
同じようなことのできる関数が他にもありますので、ご紹介しておきます。
「エクセル CHOOSE関数」
「CHOOSE関数 使い方」
といった検索がこのサイトで行われていることがあります。
CHOOSE関数の使い方を調べてらっしゃったのでしょう。
INDEX関数とあわせて使うと便利なMATCH関数について調べるのに「マッチ関数」とカタカナで検索する方がいらっしゃいますが、アルファベットの「MATCH関数」で検索すべきだということをご案内しました。
MATCH関数については他にも注意すべき検索があります。
このサイトで、
「MUCH関数」
「MACH関数」
という検索が行われていることがあるのです。
INDEX関数とMATCH関数の使い方についてご紹介しました。
単独では使い道のよくわからないMATCH関数ですが、INDEX関数と合わせて使うと便利な関数です。MATCH関数は。
このサイトで
「V Look Up関数の使い方」
「V-Look-Up関数」
「V Lookupの使い方」
「V-Lookup関数とは」
といった検索が行われていることがあります。
先日、INDEX関数の使い方をご紹介しました。
INDEX関数を使うと、例えば、料金表から行番号と列番号を指定して料金表を調べるといったことができます。
そんなINDEX関数と組み合わせて使われる関数と言えば、MATCH関数です。
このサイトで、
「INDEX関数 使い方」
「INDEX関数 使用例」
といった検索が行われていることがあります。
INDEX関数の使い方・使用例を知りたいということでしょう。
INDEX関数を実務で使う場合、MATCH関数など他の関数と組み合わせて使うことが多く、検索をしてみると他の関数と組み合わせた例が多く見つかります。
関数に慣れていない方が、関数を組み合わせた例をいきなり見ると、何をしているのかわからない、難しいと感じてしまうことはよくありますが、INDEX関数は単体での使い方を見れば、決して難しいものではありません。
VLOOKUP関数のように使えるのだけれど、認知度のいまいち高くないLOOKUP関数についてご紹介しました。
このLOOKUP関数をはじめて使ってみようという方から、よくいただくご質問があります。
「引数の選択というダイアログが表示されるのですが、これはどうしたらいいのでしょう？」
「引数の選択ダイアログでは何を選べばいいのでしょう？」
といったご質問です。
［関数の挿入］ダイアログからLOOKUP関数を挿入しようとすると、
「LOOKUP関数で使用する引数を選択してください。」
と表示された、下図のような［引数の選択］ダイアログが表示されます。
表を横方向に検索して値を取得したいという場合、よく知られたVLOOKUP関数ではなく、VLOOKUP関数と非常によく似たHLOOKUP関数を使うということをご紹介しました。
Excelのワークシート関数について調べる際に、関数名をアルファベットではなくカタカナで検索する方がいらっしゃいますが、関数について調べたいのなら、関数名をアルファベット表記で検索するほうがいいということを、何度も繰り返しご案内しています。
このことを意識してこのサイトの検索ログを眺めていると、カタカナで検索される関数は、私が以前感じていたよりも随分あることに気付かされます。
例えば、商品コードから商品名や単価を検索して表示させることのできるVLOOKUP関数は、便利かつ有名な関数です。
このサイトでも以下のようなVLOOKUP関数のネタをご紹介してきました。
- VLOOKUP関数の検索の型
- VLOOKUP関数で第4引数を省略した場合
- IF関数のネストが深いときにVLOOKUP関数
- VLOOKUP関数で#N/Aエラーを表示させない−IF関数
- VLOOKUP関数で#N/Aエラーを表示させない−入力規則
そんなVLOOKUP関数をご存知の方から、
「LOOKUP関数というのはどうやって使うのでしょうか？」
というご質問をいただくことがあります。
VLOOKUP関数をご存知の方から、
「VLOOKUP関数で検索する表を横向きに検索するにはどうしたらいいのでしょう？」
というご質問をいただくことがあります。
VLOOKUP関数は、第2引数で指定する表の左端の列を縦方向に検索して、第3引数で指定された列の値を取得する関数です。
検索する表が横向きになっていて、その上端の行を横方向に検索して、第3引数で指定された行の値を取得するにはどうしたらいいのかというのが、ご質問の主旨です。
検索する表の向きが異なるだけで、同じようなことをするので、VLOOKUP関数でできるように感じてしまう方もいらっしゃるようですが、検索する表が横向きの場合はVLOOKUP関数ではなく、別の関数を使う必要があります。
Excelのワークシート関数について調べる際に、ついカタカナで検索したくなる関数がありますが、アルファベットで検索したほうがいいということをご紹介しています。
まだまだこの手の関数はありますが、日本語の中に定着してしている単語などの場合に、カタカナで検索される率が上がると言っていいんじゃないかと思っています。
このサイトの検索ログで
「エクセル インデックス関数」
といった検索を見かけることもあります。
VLOOKUP関数を、
VLOOK関数と
思い込んでいる方が少なくないということをご紹介しました。
VLOOKUPと正しく覚えていても、検索する際にタイプミスする方が少なくありません。
［形式を選択して貼り付け］ダイアログの、［行列を入れ替える］チェックボックスを利用して、行と列を入れ替えられることをご紹介しました。
元の表とはリンクなどされていない独立した表を、もう一つ作ることで簡単に表の向きを変更でる便利な機能です。
実務では、データ的には同じで連動しているんだけれども、向きの違う表が必要ということもあります。
そしてその表のデータが頻繁に更新される可能性のある場合、［形式を選択して貼り付け］ダイアログを使って行列を入れ替えるというのでは、いまいちよろしくありません。
［形式を選択して貼り付け］ダイアログで行列を入れ替えた場合、データが更新されるたびに向きの異なる表を作らなければいけないからです。
そのため
「行列の入れ替わった連動した表を簡単に作成することはできないのでしょうか？」
というご質問をいただくことがあります。
VLOOKUP関数は、Excelのワークシート関数の中で便利度＆使用頻度の高い関数の一つでしょう。
このサイトでも以下のようなVLOOKUP関数に関連したネタをご紹介しています。
そんなVLOOKUP関数について
「ブイルック関数で値を引っ張ってきて・・・」
「ブイルックの引数って・・・」
といった会話を耳にすることがあります。
ちょっとした会話の中で、省略していることを意識して「ブイルック」とおっしゃっているのならば問題ないのですが、「VLOOK」が正しい名称だと誤解している方も少なくないように感じるのです。
算数・数学では、一つの問題を解く方法は複数存在します。
非常に簡単な例で、
「2を3つ足してください。」
という問題ならば
「2+2+2」
でもいいですし
「2x3」
でもいいわけです。
算数・数学の好きな方というのは、一つ回答を得るのに複数の方法があるという事実を面白いと感じ、シンプルな数式で回答が得られる方をカッコイイと感じられるようなセンスを持っているように、私は思います。
計算をする道具の一つであるExcelでも、同じ回答を得るのに複数の方法があります。
Excelを使いこなせるというのは、複数の方法を思いつくことができ、その中から様々な要素を鑑みて最適な方法を選択できるということなんだと思っています。
VLOOKUP関数についてご紹介した「VLOOKUP関数の検索の型」というネタは、このサイトの人気ページの一つです。
「検索の型」というのは、VLOOKUP関数でどういう検索をするのかを指示する4つ目の引数で、「TRUE」か「FALSE」を指定します。
この第4引数・検索の型は、省略すると「TRUE」を指定したのと同じことになり、完全に一致しなくても近似値を返すのが仕様なのですが、
「省略したときにエラー値#N/Aになってしまったので、省略時にはFALSEを指定したのと同じことなのではないのか？」
といったご質問をいただきました。
別のワークシートのセルを参照する方法をご紹介しました。
この方法をご存知の方からは、
「簡単に他の複数のシートのデータを表示させる方法はないのでしょうか？」
「オートフィルを使って他のシートのセルの値を表示できませんか？」
といったご質問をいただきます。
関数を使ってハイパーリンクを張る方法をご紹介しました。
この方法を知った方からは、
同じワークブック内の別のワークシートにリンクを張るにはどうすればいいのでしょう？
というご質問をいただきます。
Office製品にハイパーリンクという機能があります。
Webページを見るときにリンクをクリックしながら次々と別の文書を表示させご覧になってらっしゃると思いますが、そのWebページのハイパーリンクと同じことがOffice文書の中でも作ることができます。
一般的にはリンクを張る元の文字などを選択しておいて、右クリックして表示されるショートカットメニューの［ハイパーリンク］をクリック、［ハイパーリンクの挿入］ダイアログでリンク先を指定するというのが多い操作でしょう。
ただしこのハイパーリンクの機能は、いちいちダイアログで設定しなければいけないところがちょっと面倒です。
Excelの参考書などを見ると、ある段階まで進んだところで必ずと言っていいほど出てくる関数があります。
VLOOKUP（ブイルックアップ）関数です。
Vertical LOOK UPの略で、「垂直方向に調べる」というような意味です。
Home > Excel エクセルの使い方-関数/計算式-検索・行列
