エクセル（Excel）の技。知ってましたか・・・。

この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

マイクロソフトアクセスの記事はこちら

人気ブログランキングへ




にほんブログ村

INDEXとMATCH


INDEXとMATCH関数の組み合わせ

下の表でB14に入力された相手先の合計金額F14に表示したいとします。

今F14に「=INDEX(D2:F11,MATCH(B14,B2:B11,0),3)」という式が入力されています。



分かりやすくするために分解してみましょう。

まずMATCH(B14,B2:B11,0)の部分ですが、B14に入力された相手は

B2:B11の範囲内で何番目を表示する関数ですね。

答えは「8」です。

となると=INDEX(D2:F11,MATCH(B14,B2:B11,0),3)は=INDEX(D2:F11,8,3)という

置換が出来ますね。

この内容はD2:F11の範囲で8行目と3列目の交差する値を検索する内容ですね。

答えは「2500」です。

もちろんVLOOKUPでも同じ結果が出せます。

だったらVLOOKUPでいいじゃないという考えもありますが何故INDEXと

MATCH関数の組み合わせを使用するかというと、上の表の黄色の枠と、

青の枠が離れていた場合でも使用できるメリットがあります。

極端な話をすると、まったく別の表でも構わないということです。

そうなるとVLOOKUPは使用できないということになります。

マイクロソフトアクセスの記事はこちら

人気ブログランキングへ




にほんブログ村


INDEX

指定したセルの内容を表示します。

INDEX関数は2つの使い方があります。

・=INDEX(配列定数又はセル範囲,行番号,列番号)

・=INDEX(セル範囲,行位置,列位置,領域番号)

「セル範囲」・・・B2:C4のように、セル範囲を指定する。複数指定も可能

「配列定数」・・・{1,2,3,4,5;6,7,8,9,10}これは2行5列の配列定数

「行番号」・・・セル範囲を指定した場合は必須。

値を返す行を数値で指定する。

列の配列を受け取りたい場合は「0」にする

「列番号」・・・値を返す列を数値で指定する。

行の配列を受け取りたいときは「0」を指定する

範囲の中から、行位置と列位置を指定し、そのセルの値を取り出す。

=INDEX(範囲,行番号,列番号)



今、I2に「=INDEX(B2:C4,2,2)」と入力されています。

B2:C4は範囲です。図の赤枠部分です。

次に最初の「2」は行番号でB2から数えると2つ目はB3ですね。

次の「2」は列番号でB列から数えるとC列になりますね。

ですからこの交差するセルはC3になり、「鉛筆」が表示されます。



=INDEX(範囲,行位置,列位置,領域番号)・・・領域番号は離れた場所にある

複数の範囲を指定する場合に使用する。

省略した場合は「範囲」に指定された場所を指す。

複数領域があれば「範囲」のところに「B2:C4,B7:C10」という形で指定すると、

領域番号1は「B2:C4」になり、領域番号2は「B7:C10」になる。




A8.netなら！
------------------------------------------------
◇スマートフォンやPCから簡単に広告が掲載可能

◇もちろん会員登録は無料。その後も費用はかかりません！

◇欲しい商品がお得に購入できるセルフバックの利用ができる

◇有名ブランドから高額報酬がもらえる広告を掲載

-----------------------------------------------
アフィリエイトを始めるならA8.net（エーハチネット）
まずは登録→　https://px.a8.net/svt/ejp?a8mat=2NQXR8+BN56NM+0K+ZXXSJ

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村


左側の検索


VLOOKUPでは検索値から右側しか検索できないですね。

でも左側を検索したい場合もあります。

そのような場合、OFFSETとMATCHを組み合わせて左側の検索が出来ます。



今、I3に「=OFFSET(B1,MATCH(I2,B2:B6,0),-1)」と入力されています。

まずMATCH(I2,B2:B6,0)の部分だけ見て行きましょう。

検索値はI2の「関西コンクリート」です。

検索範囲はB2からB6です。

照合の型は完全一致で探すとB2にありますね。

だからMATCHの部分は「1」になります。

次にOFFSETの部分ですがOFFSET(B1,MATCH(I2,B2:B6,0),-1)のうち

MATCHの部分は「1」なので

OFFSET(B1,1,-1)という式になりますね。

B1が基準で下に「1」、右に「-1」ということは左に「1」ですね。

ですから、B1から見て結果はA2を参照して2015/1/4となります。

このように、関数を組み合わせることによって、単独では出来ない

事も出来るようになります。

色々組み合わせて使ってみてください。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村



MATCH


検索指定範囲内で値を検索し、見つかった値が何番目にあるのかを数値で返す。

【構文】

MATCH（検索値,範囲,[照合の型]）

検索値・・・検索する値

範囲・・・検索範囲

照合の型

　「0」・・・完全一致

　「1」・・・一致するものが無い場合は、検索値以下で最大の値を表示

　「-1」・・・一致するものが無い場合は、検索値以上で最小の値を表示



今、I3に「=MATCH(I2,B2:B10,0)」という関数が入っています。

(I2,B2:B10,0)でI2が検索値「平成電気�梶vになります。

B2:B10が検索範囲。

照合の型は「0」で完全一致を探します。

平成電気�鰍ﾍB7にありますので、B2から数えて6番目になりますので

I3は「6」と表示されます。




今度は列方向の場合

I6に「=MATCH(I5,A1:G1,0)」と入力されています。

(I5,A1:G1,0)でI5が検索値「単価」で、A1:G1が検索範囲、照合の型は「0」です。

単価はD1にあるのでA1から数えて4番目なのでI5は「4」になります。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村



OFFSET


基準のセル（セル範囲）から指定された行数と列数だけ移動した位置にあるセルの参照

【構文】

OFFSET（基準,行数,列数,[高さ],[幅]）

・基準・・・基準となるセル又はセル範囲（範囲の場合は左上のセル）

・行数・・・基準のセル（範囲の場合は左上のセル）を上方向又は下方向にシフトする。

正の場合は下方向へ、負の場合は上方向へシフトする

・列数・・・基準のセル（範囲の場合は左上のセル）を左方向又は右方向にシフトする。

正の場合は右方向へ、負の場合は左方向へシフトする

・高さ・・・オフセットの行数を指定する。正の数であること。省略可

・幅・・・オフセットの列数を指定する。正の数であること。省略可



今I3に「=OFFSET(B2,3,1)」と入力されています。

(B2,3,1)でB2が基準セルになります。次の「3」で3行下にシフト。

次の「1」で列が右に1列シフトでC5セルを参照するので「消しゴミ」という表示になります。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村


AVERAGE関数



AVERAGE関数・・・引数の平均を求める場合に使用します。

【構文】

AVERAGE（数値1,数値2・・・数値255）・・・エクセル2007以降は

引数が255まで設定できます。

直接数値を入れてもいいですし、範囲指定も可能です。



上はF2・F4・F6・F9（黄色部分）の平均を求めています。



今回はF2からF9の範囲の平均を求めています。

※AVERAGE関数は範囲指定した場合、

途中に文字列があった場合は「０」とみなします。


またAVERAGEIF関数というのもあります。

下の例では「�潟Aート」の合計金額の平均を出しています。



【構文】

AVERAGEIF（範囲,条件,平均対象範囲）

最初の「範囲」というのは平均を求めたい相手先の範囲です。

条件というのは「相手先名」。

平均対象範囲というのは平均を出したい範囲のことです。

上のようにH3に「=AVERAGEIF(B2:B9,"�潟Aート",F2:F9)」と入力されています。

B2からB9の間の相手先で「�潟Aート」を選択して、

その合計金額の平均を出しなさいという意味です。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村


AND・OR関数


AND関数は全ての条件を満たされているか調べる関数です。

OR関数はいずれかの条件が満たされているか調べる関数です。

どちらの関数も「TRUE」（その通り）又は「FALSE」（そうではありません）で

返されます。

【構文】

AND(論理式1,論理式2,論理式3、・・・)

OR(論理式1,論理式2,論理式3、・・・)



H列に「判定」というフィールドを追加してH2に「=AND(D2>100,E2>100)と

入力されています。またH3からH35まではコピーされています。

単価が100円以上で数量が100個以上という条件です。

H4・H9・H21にTRUEが表示されていますね。

それ以外はFALSEになっています。



今回はOR関数です。

単価が500円以上か、数量が100個以上でTRUEが表示されます。

但しどちらの場合も結果がTRUEかFALSEなのであまりよくないですね。

そういったことから、通常はIF関数と組み合わせて使用される場合が多いです。



今回は=IF(AND(D2>100,E2>100),”多”,”少”)と入力されています。

先ほどのANDの式にIFを付けてANDの結果がTRUEの場合は「多」と表示し、

FALSEの場合は「少」と表示します。

OR関数も同じようにIFと組み合わせて使用される場合が多いです。

まずは15日間【無料】お試し利用から！大容量256GBレンタルサーバー『HETEML』


マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村


ISBLANK関数

ISBLANK・・・対象セルが空白の場合はTRUEを返します。

【構文】　

=ISBLANK(対象セル)

下のようにE4が空欄の場合はF4は「0」表示になります。

しかし空欄の場合は何も表示してほしくない場合もあります。



ここでISBLANK関数を使用してみましょう。



上記のようにF4に「=IF(ISBLANK(E4),"",IF(ISERROR(D4*E4),"未定",D4*E4))」

という数式を入れました。

これによりE4が空白の場合はF4も空白になります。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村



ISERROR関数

エクセルを使っているときに、よくエラーが出ますね。

特に「#VALUE!」のように、原因がはっきりわかっている場合の

エラー表示は気分が悪いですね。

そこで、IF関数とくみあわせて数式の結果がエラーになるかどうか、

あらかじめチェックして、エラーの場合は非表示にしたり、

別の文字列を表示させたりすることが出来ます。

ISERROR関数単独では数式の結果がエラーかどうかの判定だけです。



E4の数量に「未定」という文字列を入れると、F4もH4も「#VALUE!」というエラーが表示されます。

そこでF4に=ISERROR(D4*E4)と入力するとF4は「TRUE」になります。

ここにIF文を追加したので下の例です。



=IF(ISERROR(D4*E4),”未定”,D4*E4)と入力されています。

計算結果がエラーならば「未定」という文字列を入れ、計算結果がエラーでない

場合はそのまま計算するという内容です。

「“未定”」のところを単に「””」とすると空白になります。

これで目障りなエラー表示が出なくなります。

まずは15日間【無料】お試し利用から！大容量256GBレンタルサーバー『HETEML』


マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




にほんブログ村



DATEDIF関数

２つの日付のシリアル値から期間を求めます。

【構文】

=DATEDIF(開始日,終了日,単位)

・単位の種類

“Y”・・・期間内の萬年数

”M”・・・期間内の満月数

“D”・・・期間内の日数



C2に「=DATEDIF(A1,A2,”M”)と入力されています。

A1〜A2の期間で満月数を出しなさいという意味で、答えは「１１」です。



上の例ではE1に本日の日付ということで=TODAY()が入力されています。

またF4に=DATEDIF(E4,$E$1,”Y”)と入力されています。

これで勤続年数が出力されます。

$E$1にしているのはF5以降にコピーする場合絶対値にしておく必要があります。








google-site-verification: google948a2bbbd2fd5164.html