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

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

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

人気ブログランキングへ




にほんブログ村


CELL関数


セルの情報（位置・書式・内容等）を取得する関数です。

【構文】

=CELL(検査の種類,対象範囲)・・・対象範囲は省略可能

検査の種類とは（検査の種類は「”」で囲むこと

・”address”・・・対象範囲の左上のセル位置を表示します。

・”col”・・・対象範囲の左上のセルの列番号を表示します。

・”color”・・・負の数を色で書式設定されている場合は「１」、それ以外は「０」を表示します。

・”contents”・・・対象範囲の左上のセルの値を表示します。

・”filename・・・”対象範囲を含むファイルのフルパス名を表示します。

・”format”・・・セルの表示形式を表示します。詳細は省きます。

・”parentheses”・・・セル内の値を()で囲む書式設定がされている場合は「１」、

　　　　　　　　　　　　　それ以外は「０」を表示します。

・”prefix”・・・セルの文字位置を表示します。詳細は省きます。

・”protect”・・・セルのロック状態を表示します。

・"row"・・・対象範囲の左上のセルの行番号を表示します。

・”type”・・・セルのデータタイプを表示します。詳細は省きます。

・”width”・・・小数点以下を切り捨てた整数のセル幅を表示します。

例）

今H4に「=CELL("contents",A5)」という関数が入力されています。

この式はA5のcontents（セルの値）を表示せよという関数なので

「新川　英雄」が表示されます。



今回はH4に「=CELL("address",A3:F26)」という関数が入力されています。

Addressなので範囲の左上のアドレスということで$A$3が表示されています。

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

人気ブログランキングへ




にほんブログ村

INDIRECT 関数の応用


リストボックスで使用

下のような名簿があったとします。

この名簿でA2〜A20までの範囲に「男」という名前を付けています。

A21〜A24までの範囲に「女」という名前を付けました。

H2に性別のリストボックスを作成します。

データタブからデータの入力規則を選択します。



次に「入力値の種類」で「リスト」を選択し、「元の値」に「男,女」と入力して「ＯＫ」します。



下のように「男」と「女」が選択できるリストボックスが出来ました。



次にH5の名前の部分ですが、ここも入力規則で同じようにリストを選択します。



元の値のところに「=INDIRECT($H$3)」と入力して「OK」します。

そうすると性別によって名前のリストが変化します。

性別で「男」を選択した場合はA2〜A20のリストが、性別で「女」を選択した場合は

A21〜A24のリストが表示されます。



上は性別で「女」を選択した時です。

これは非常に便利です。

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

人気ブログランキングへ




にほんブログ村


INDIRECT関数


セルの参照を返します。参照するセルを間接的に指定する関数です。



・E2には=INDIRECT(C2)と入力されています。

まず「C2」を見に行くと「A2」というセル番号

（ここが間接的にという意味です）が

入力されているので、「A2」を見に行くと「10」が入力されているので

E2に「10」と表示される。

・E4には=SUM(INDIRECT(C4))と入力されています。

C4には「A2:A4」という範囲が指定されているので、この範囲でSUM関数を

適用すると「60」になります。

・E6には= INDIRECT(C4)と入力されています。

答えは#VALUE!になっていますね。

INDIRECTはセル範囲ではなくセル番号を指定する必要があるので、

エラーを返します。

・E8には= INDIRECT(C8)と入力されて、答えは#REF!になっています。

これもC8の「10」はセル番号ではないのでエラーを返します。


しかしこの関数は一体どういう時に使用するのでしょうか。

単にセルを参照するだけならば「=A2」とすればA2の値を参照できますね。


この関数の最大のポイントは参照するセルのアドレスを文字列で

指定できることです。

INDIRECT（参照文字列）ということが出来るのです。

例えば先ほどの表でE2に入力されている「=INDIRECT(C2)」ですが、

「=INDIRECT(“A”&2)でも同じ結果が得られます。

今回は直接「A2」を検索しています。

”A”というのは文字列ですがINDEX関数を使用すると

（A2）として認識してくれます。



今、E13に=INDIRECT(“B” & MATCH(C13,C1:C11,0))と入力されています。

MATCHの部分はC13の「鉛筆」はC列の何番目かということで、

答えは「3」になります。

ですからINDEX関数でB3を参照せよということになります。

結果として「本山工業」が表示されます。

これも以前一度掲載しました「OFFSET関数とMATCH関数」の

組み合わせと同じで、左側の検索として使用できます。


関数も１つだけ単独で使用するのではなく、組み合わせて使用すると

意外なことが出来る場合があります。

自分の希望する計算が出来る関数がない場合は

色々工夫して作ることも可能だと思います。

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

人気ブログランキングへ




にほんブログ村

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と組み合わせて使用される場合が多いです。