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

┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘
気軽に・お得に・稼ぎたいなら「A8.net」に登録！！
https://px.a8.net/svt/ejp?a8mat=2TAC5M+14HJGI+1N3S+6AAFLV
┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘┘




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

人気ブログランキングへ




にほんブログ村



データベース

データベースの定義

・規則正しく整理されたデータの集合をデータベースと言います。

そこから必要なデータだけ抽出したり、集計したり、並び替えたりする機能を

「データベース機能」といい、エクセルにもその機能は備わっています。


データベースの作成上のルール

・データベースとして使用したい部分は、それ以外の部分との間に１つ以上の

空白行・空白列を作っておくこと。



上の例で黄色の部分（A1）が表題で、緑の部分（A2~H35）をデータベースとして

使用する場合、A1がデータベースとつながっているので、エクセルは緑の部分を

データベースとして認識できなくなります。



上のように2行目に空白行を挿入しました。

これでエクセルは緑の部分をデータベースとして認識できるようになります。

・先頭行は、項目名とする。

上記の表で3行目が項目名になっていますね。

このようにデータベースの1行目は必ず項目にする必要があります。

・1行に1件とする。

上記の表のように、1件に対して複数行にならないようにする。1件1行で記載する事

また項目行も1行にしましょう。

・同じ項目名は付けない

・途中に空白行を設けない

途中に空白があるとエクセルは空白行の手前までがデータベースだと認識します。

空白行以降は別物として認識されます。

・書式を統一する。

例えば半角・全角が混じっていたり、ひらがな・カタカナが混じっていたりすると

集計・抽出時に扱いにくいデータベースになります。

・セルを結合しない

結合セルがある場合は抽出とか並べ替えとかの際に動作しない場合があります。


見やすいデータベースにする。



データベースにする範囲を選択して、ホームタブの「テーブルとして書式設定」で

上のようなスタイルが表示されますので好きなスタイルを選んでください。


私は下のスタイルにしました。



上記の状態になると非常に使いやすい表になります。

（テーブル機能が使えるようになります）

まず見やすいですね。1行ごとに縞模様になっています。

また途中の行を削除しても縞模様は崩れません。

テーブル形式になっていない場合は、1行削除すると縞模様が崩れてしまいます。


またフィルターも自動でついてきます。




━━━[PR]━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
☆★☆★☆★☆★　定番！　お小遣いサイトモッピー　　　☆★☆★☆★☆★

　無料で簡単にポイントが貯められる！
　貯めたポイントは1ポイント＝1円として交換が可能！！

　↓↓↓さっそく無料登録する↓↓↓
　https://px.a8.net/svt/ejp?a8mat=2NQS9W+247I7M+2JSG+60H7N
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[PR]━━━
の

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

人気ブログランキングへ




にほんブログ村


配列数式


最後に一つ面白い例をあげて終わりにしたいと思います。



上記のような表があり、いまB2からJ10まで選択した状態で、数式バーに

=A2:A10*B1:J1と入力されています。

ここで「Shift」＋「Ctrl」＋「Enter」を押してください。



上記のように配列によって九九の計算式が出来ました。

このようなことも配列を使用すれば簡単に作成できます。



配列のデメリット

・多用すると動作が重くなる

・数式の意味が作成者でないと分からない場合があるので、

　配布を考えている場合は使用しない方が良い

・表を使う方が早い場合もある


配列というのはちょっと特殊な使用方法ですね。

別に知らなくても支障はありません。通常にExcelを使用する限り、

ほぼ使用することは無いと思います。

上のデメリットにあるように数式の意味が作った人しか分からないので、

配布用には適していませんね。


【閑話休題】

ここで少し配布用のエチケットとしていくつか。

１、印刷の必要がある場合は、印刷の設定をしておくこと。
　
　　（用紙の設定・印刷範囲の設定・印刷タイトルの設定）表示と印刷は

　　１００％にしておくことが原則

２、余分なシートは削除する。

　　記入済みのシートは別として、未使用のsheet2とかsheet3はそのまま

　　残さない。
　
　　もらった相手は、シートがあることで念のために見る必要がある。

　　相手に余分な作業をさせないために不要なシートは削除してから

　　配布するようにしましょう。

　　また記入済みシートのタブには必ず名前を付けましょう。

３、縦に長い表の場合スクロールすると見出し行が見えなくなる。

　　ウィンドウ枠の固定を設定しておきましょう。

４、リンクが張ってある場合は、その参照が相手の環境では参照できる

　　のかどうかを確認しておく必要がある。

５、改ページプレビューのまま配布されている。

　　これは本当によくあるのですが、この状態で配布される方の意図が

　　よく分かりません。

　　通常表示の状態で保存して配布しましょう。

６、私もよくあるのですが、ヘッダー・フッターが設定されている。

　　これは問題のない場合もありますが、例えば右上に「日付」が設定

　　されている場合。

　　その日の日付固定で入力される方がいらっしゃいます。

　　自動でその日の日付を取得できるように設定してあればそれほど

　　問題になりませんが、自分が作成した日付をいちいちヘッダーに

　　入力される場合、配布先はヘッダーまで気づかないので、印刷して

　　初めてヘッダーがあることに気づくということになります。

　　このあたりも配布先に配慮して問題のないヘッダー・フッターか、

　　確認する必要があります。

７、フォントの統一、数字の統一。基本的なことですが、フォントが

　　混じっていて見難い場合があります。

　　また数字も半角と全角が混じっている場合も見難いです。

　　確認しましょう。

８、計算結果欄があるのに、計算式が入っていない。

　　「合計欄」も手書きで入力させるつもりでしょうか。

９、項目によっては入力規則を設定して、自動で全角・半角が切り替わる

　　ようにしてあげると配慮が感じられていいですね。

１０、セルのロック・またはシートのロック。間違って余分なところに

　　　入力させないようにするために、入力するセルを限定して、

　　　その他のセルはロックする。

１１、入力に際し、入力方法がややこしい場合などはセルに「コメント」を

　　　貼り付けておくと良い。

１２、セルに書式設定をする場合ですが、行数が増えると予想される

　　　場合は、列ごと同じ書式設定をしておくこと。

　　　現在入力されている行だけ書式設定してあれば、Excelは、

　　　それ以降に関して別の書式という認識になるので、

　　　相手先で行数が増えると予想される場合は列で書式設定して

　　　おきましょう。

　　　そのあとのタイトル行だけ修正　　すればＯＫです。

１３、行数が固定しない場合、罫線は引かない。

　　　大きな表の場合とか後からデータを追加するような場合は最初から

　　　罫線は引かないようにしましょう。

　　　余分なファイルサイズを使用します。

　　　「枠線印刷」を使用しましょう。

１４、Excelのバージョンの問題です。

　　　新しいバージョンのExcelを古いバージョンで開くと使用できない

　　　機能等があると再現されません。

　　　特に2003以前のバージョンを使用している場合は気を付けましょう。





━━━━━━━━━━━━━━━━━━━━━━━━━
■ 約200万サイトが使っているA8.net
━━━━━━━━━━━━━━━━━━━━━━━━━
・HPやBLOGに広告を掲載して副収入を得ませんか？
・無料で始められて1000円以上でお振込み。
・いま多くの方に利用されているエーハチネットは
　PCとスマートフォンからご利用いただけます。

▼詳細はこちら
https://px.a8.net/svt/ejp?a8mat=2TAC5M+14HJGI+1N3S+6AANBN
━━━━━━━━━━━━━━━━━━━━━━━━━

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

人気ブログランキングへ




にほんブログ村


配列数式

もう少し複雑なことをしてみましょう。

C列の商品名が「ボールペン」の合計金額を出してみましょう。

F37に{=sum(IF(C2:C35=”ボールペン”,F2:F35,0))}と入力すると

下のように「13800」になります。

黄色の部分だけが対象です。



上記のようになります。

もちろん通常はSUMIFでも同じ結果が出ます。


配列定数というのは、行と列で表した定数の集合のことを言います。

配列定数で使用できるデータは「数値」「文字列」「論理値」「エラー値」があり

同じ配列定数の中で別のデータ型が使用できます。

配列定数の入力方法



今A1からE1まで選択して数式バーに{={1,2,3,4,5}}と入力されています。

両端の{ }は「Ctrl」＋「Shift」＋「Enter」で配列数式として認識させたものです。

間の{ }は直接入力しています。

上記は1行5列の場合です。

下は2行5列の場合です。行を変える際は間に「；」を入れてください。




何故このような説明をしているかというと、「配列定数」は数式内で配列的な

データを表現したいからです。

通常は上記のような表を作って、そこに計算式を入力して結果を出していく

という方法を使用します。

しかしこの配列定数を使用すると上記のような表が無くても結果が出せるのです。


例）

下のような表があり、E4に封筒の単価を出したい場合、数式のように

「=VLOOKUP(D4,A2:B5,2)」で「110」という結果が得られます。



ここで配列定数を使用するとどうなるか。



E4に{=VLOOKUP(D4,{"2穴パンチ",650;"鉛筆",300;"封筒",110;"消しゴム",100},2)}

という数式が入っています。

この表を見てわかるように先ほどの参照表はどこにもありませんね。

数式の中で表が完成しているのです。

これを「配列数式」と言います。

数式の中で表が完成しその中で検索して結果を出しています。

配列定数を使用するとこのようなことが出来ます。

ちょっとした表ならば上記のような形で計算できますね。

また新たに行・列を追加したくない場合はこのようにすれば結果が出せます。




━━━━━━━━━━━━━━━━━━━━━━━━━
■ 約200万サイトが使っているA8.net
━━━━━━━━━━━━━━━━━━━━━━━━━
・HPやBLOGに広告を掲載して副収入を得ませんか？
・無料で始められて1000円以上でお振込み。
・いま多くの方に利用されているエーハチネットは
　PCとスマートフォンからご利用いただけます。

▼詳細はこちら
https://px.a8.net/svt/ejp?a8mat=2TAC5M+14HJGI+1N3S+6AANBN
━━━━━━━━━━━━━━━━━━━━━━━━━

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

人気ブログランキングへ




にほんブログ村


配列数式


配列とは、複数セルの集合体です。

「配列数式」とは複数セルを対象に１つの数式を作成することです。

配列を使用することにより、表を使用しなくても数式の中に仮想の表を作り

数式を完成させることが出来ます。

注意点：配列数式の対象範囲は同じ行数・同じ列数であること。

複数のデータをセットにして扱う機能だと考えてください。

例）下の表で、今F2に「=D2*E2」という関数が入力されています。

F3には「=D3*E3」というように順番に入力されています。



ここでF列に配列数式を使用してみましょう。

一旦F2からF35に入力されている数式を削除してください。

削除して改めてF2からF35迄選択して、数式バーに「=D2:D35*E2:E35」と

入力してから「Ctrl」＋「Shift」＋「Enter」を押してください。



数式バーの表示が{=D2:D35*E2:E35}になりましたか。

配列数式になると{}で囲まれます。またF2からF35のどこのセルを

選択しても{=D2:D35*E2:E35}になっています。

配列数式を使用すると一つ一つのセルに数式を入れる必要が無く、

一度に入力できます。

またこの数式は個別で、例えばF10だけ変更しようとしても変更できません。

誤って消去したり出来ないわけです。

今度はF37に「=SUM(D2:D35*E2:E35)と入力して「Ctrl」＋「Shift」＋「Enter」を押してください。

{=SUM(D2:D35*E2:E35)}となり、340600と表示されましたね。



これは合計金額の合計を出しています。

通常でしたら「=SUM(F2:F35)」で出せますね。

各合計金額を出す必要が無く、すべての合計金額だけ必要な場合は

{=SUM(D2:D35*E2:E35)}一回で計算できます。

F列は不要になります。

もう一つ、以前に紹介したSUMPRODUCTを使用しても同じ結果が得られます。

「=SUMPRODUCT (D2:D35*E2:E35)」で同じ結果が出ます。

上記の表ではあまり有難みは分からないのですが、大きな表になった場合は

一つ一つ数式を入れていくと大量の異なる数式が必要なところが一つですみます。




━━━━━━━━━━━━━━━━━━━━━━━━━
■ 約200万サイトが使っているA8.net
━━━━━━━━━━━━━━━━━━━━━━━━━
・HPやBLOGに広告を掲載して副収入を得ませんか？
・無料で始められて1000円以上でお振込み。
・いま多くの方に利用されているエーハチネットは
　PCとスマートフォンからご利用いただけます。

▼詳細はこちら
https://px.a8.net/svt/ejp?a8mat=2TAC5M+14HJGI+1N3S+6AANBN
━━━━━━━━━━━━━━━━━━━━━━━━━

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

人気ブログランキングへ




にほんブログ村



LEN関数


指定した文字列の文字数を返します。

半角・全角の区別なく1文字でカウント。

スペース、数字、句読点も1文字でカウントします。



今G4に=LEN(A4)と入力されています。A4の文字数を数えます。

答えは「５」です。

スペースも1文字でカウントするので「５」になります。


ASC関数・JIS関数

ASC関数・・・全角文字を半角文字に変換

JIS関数・・・半角文字を全角文字に変換

【構文】

=ASC(文字列)

=JIS(文字列)




今H4に=ASC(G4)と入力されています。

G4の全角文字列を半角に変換せよという命令です。



次はH5に=JIS(G5)と入力されています。

G5の半角文字列を全角に変換せよという命令です。


また似た様な関数として

「UPPER」・・・英字の小文字を大文字に変換

「LOWER」・・・英字の大文字を小文字に変換

「PROPER」・・・英単語の先頭文字を大文字にして、

2文字目以降を小文字にします。




カラーミーショップはリアルな店舗を持っていなくても、ネットを通じて商品を販売する楽しみを多くの人に実感してもらえるショッピングカートです。これまでに23万を超えるユーザー様からお申込みをいただいております。

カラーミーショップなら誰かに（大手に）頼らなくても、あなた好みのネットショップを作れます。あなたがセレクトした商品や作った商品の魅力を伝え、それを欲しいと思ってくれる人にお届けしてみませんか。

30日間、無料でお試しいただけるので、初心者の方やネットショップ作成サービスで悩んでいる方も、安心してご利用いただけます。

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

人気ブログランキングへ




にほんブログ村


FIND関数


指定された文字列を他の文字列の中で検索し、文字列の位置を返します。

【構文】

=FIND(検索文字列,対象,開始位置)

開始位置は省略できます。省略した場合は「１」になります。



今G4に=FIND("　",A4,1)と入力されています。

「"　"」は全角スペースのことです。

この式はA4の中に全角スペースは何文字目ですかという意味で、答えは「３」です。

このことを応用して下のように変更します。



=LEFT(A4,FIND("　",A4,1)-1)とLEFT関数を追加しました。

これでA4の文字列で左から2文字抽出せよという命令になります。

FINDの答えは「３」なので「−1」することにより「２」になり、「岩本」が表示されます。

これを使用すれば名前から「姓」だけ抽出することが出来ます。

（但し姓と名の間にスペースが入っている場合です）

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

人気ブログランキングへ




にほんブログ村


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関数」の

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


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

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

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

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