2006/12/09
吟遊詩人とエクセル通信
─────────────────────────────────── ┌─────────────────────────────┐ │ ☆★☆吟遊詩人とエクセル通信☆★☆ │ │ │ │ 発行者：吟遊詩人 │ └─────────────────────────────┘ ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ [2006/12/09-Vol.00008] ☆☆☆メールマガジンの『文字や記号がずれて読み難い』方は☆☆☆ Outlook Expressをお使いの場合、オプション→読み取り→ フォント押して[ＭＳゴシック]に設定して下さい。Macや他の メーラーをお使いの場合Osaka等倍など「等倍フォント」をお勧めします ∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽ 不定期配信ですが、なるべく隔週で発行したいと思ってます。＾＾； ∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽∽ ☆ご挨拶 ご覧頂きます、メルマガはホームページ「吟遊詩人とエクセル」が基本と なっております。 解説の中で、しばしば、画像が必要な物は、ホームページ参照が発生します この事を、ご理解頂きまして、少しでも分かりやすいメルマガを目指してい きます。 今後とも、宜しくお願い致します。 吟遊詩人 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ 吟遊詩人とエクセル便りは、エクセル関数に関する色んな使い方を中心に取 り上げて行きたいと思ってます。 さらに、発行者、吟遊詩人がネットサーフィンして感銘を受けたサイトの紹介 や日々の暮らしの中で、発行者が思う事や、皆さんに聞いて頂きたい内容等盛 りだくさんで広く情報を提議して行きたいと思っております。 どうぞ、宜しくお願い致します。 ぺこ <(＿ ＿)> 吟遊詩人とエクセル（関数やシェイプ図を初心者を対象に解説しております） http://ginyusijin.hmc6.net/ 関数Ｑ＆Ａ掲示板 （サイト内の関数やシェイプに関するお問い合わせ） http://ginyusijin.hmc6.net/cbbs/cbbs.cgi BBS 火魔人の掲示板です。（サイト内の交友掲示板です。） http://human0.com/bbs/mkres5.cgi?lark メールアドレス：suekun@msn.com（表題を表示して頂かないと削除されます） ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ こんにちは 吟遊詩人です。 会社の大番頭さんが定年退職を控えて、色々業務の引継ぎに悪戦苦闘してます。 言い訳ですが、そんな訳でまたしても前回から少し発行に日が空いてしまい 大変申し訳なく思ってます。＾＾； ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔⇔ ☆★☆関数の使い方教室☆★☆ 説明に中で使われる「」『』は強調表示です このコーナーで使う関数のサンプルを下記ページで用意しております。 合わせてご利用下さい。＾＾ 解説および、サンプルＤＬ先：http://ginyusijin.hmc6.net/page045.html ◆第7回 ◇VLOOKUPの活用『検索行列関数』◇ 今回の関数はエクセル関数の中では比較的使用頻度の高いVLOOKUPをやります。 但し、今回は少しすっきりしません。＾＾； それは作業列を一列要するからです。 シート関数の場合、複雑な作業を行わせようとした場合、一度データーを 加工する必要がしばしば出てきます。 この処理を本来なら関数の中に関数をネストして処理したい所ですが、 後で修正が効かないくらい関数を長く続けるより、メンテナンスを考えれば 適当に作業用のスペースを作って処理する事も大切だと思います。 では、本題に入って♪ みなさんはVLOOKUPで検索をかけようとした時に、検索範囲に同じ名前が 複数存在していて困った事はないですか？ 例えば果物で言うと、仕入れ単価違う複数の「りんご」が範囲に存在する 場合とか・・・ もちろん検索する側も、何番目のりんごを検索したいのかはっきりする 必要性がありますが、検索範囲にも複数「りんご」があると困りますよね。 そんな場合は検索範囲に作業列を一列追加します。 以下の様な表があったとします。 A B C D E F G H 1 集計表 仕入れ集計 2 品名 個数 単価 小計 品名 単価 3 りんご 20 2000 りんご 100 4 みかん 30 6000 みかん 200 5 ばなな 15 2250 ばなな 150 6 かき 35 8750 マンゴ 400 7 りんご 25 3000 かき 250 8 パイン 12 3600 りんご 120 9 すいか 18 6300 パイン 300 10 みかん 22 3960 すいか 350 11 ぶどう 27 5940 キュイ 380 12 りんご 31 4960 みかん 180 13 ぶどう 220 14 りんご 160 15 16 F列〜G列の仕入れでは同じ名前の果物を数回仕入れてる場合がありますが、 仕入れの度に単価が違います。 それを現状の在庫を個数を下に仕入れ単価を掛けて集計したいと思います。 当然A列〜D列の表では、すでに売れてしまって在庫に無い物もあります。 この時、必要なルールは以下の通りです。 1、複数回仕入れている物は、仕入れた順番に品名を並べる。 2、在庫に無い物は省略して良い。 こうした時、「A3」「A7」「A12」にりんごがありますが、それぞれ仕入れ 単価が違うので、別々に小計を出さないといけません。 単純なVLOOKUPでは単価が拾い出せませんね。 そこで、G列に列を一列挿入して、以下の様にします。 範囲はF列〜H列です。 挿入したG列の「G2」セルに「検索用」とでも記入して、 「G3」セルに、『=F3&COUNTIF($F$3:F3,F3)』と記入して下方に必要行だけ フィルコピーします。 関数の内容は、「＝F3」そのままF3セルにある品名ですね。 「＆」は文字を繋げる演算子ですからF3の「りんご」に文字を繋げる事に なります。 そしてその繋げる文字は、「COUNTIF($F$3:F3,F3)」で返された値ですね。 では「COUNTIF($F$3:F3,F3)」の意味は？ COUNTIF＝範囲の中で条件に一致する物の数を数えるです。 その引数の中身のCOUNTIF(検索する範囲,検索する物)ですから、例で行くと $F$3からF3の間でF3（りんご）がいくつあるか数えます。 当然答えは「1」です。＾＾； でも大事なのはこの範囲の指定の方法です。 範囲指定の方法が『$F$3:F3』となってます。後ろ半分は「＄」マークが 付いてないですね。つまり相対参照。コピーすると参照先が変化するって 事です。（相対参照、絶対参照についてはホームのセルの参照に仕方 http://ginyusijin.hmc6.net/page010.htmlをご覧下さい） この範囲指定の方法で下方へフィルコピーすると、範囲の中身は、 G4セル=$F$3:F4 G5セル=$F$3:F5 G6セル=$F$3:F6 G7セル=$F$3:F7 と変化していきます。 こうする事で、その行までに検索したい値が何回あったかを正確に数える 事が出来るのです。 この名前と出現回数を「＆」で繋げて表示する事で同じ名前の品名を個別に 管理する仕組が出来ました。 以下の表がその完成版です。 F G H 1 仕入れ集計 2 品名 検索用 単価 3 りんご りんご1 100 4 みかん みかん1 200 5 ばなな ばなな1 150 6 マンゴ マンゴ1 400 7 かき かき1 250 8 りんご りんご2 120 9 パイン パイン1 300 10 すいか すいか1 350 11 キュイ キュイ1 380 12 みかん みかん2 180 13 ぶどう ぶどう1 220 14 りんご りんご3 160 あとは、C列にVLOOKUPを使って単価を引っ張って来るだけですね。 この時エラー処理として（検索した物が無かった場合など）IF関数の中に ISERROR関数を入れて処理します。 C3セルに入れる関数は以下の通りです。 =IF(ISERROR(VLOOKUP(A3&COUNTIF($A$3:A3,A3),G3:H14,2,0)), "",VLOOKUP(A3&COUNTIF($A$3:A3,A3),G3:H14,2,0)) 長くなったので途中で改行してますが、普通のエラー処理をしたVLOOKUPですね。 ただ少し違うのは、VLOOKUPの中で検索する部分の設定を先ほどの作業列（G列） と同じように、「A3&COUNTIF($A$3:A3,A3)」としてる部分です。 同じようにA列で何度目の品名かを数えて検索してます。＾＾ あとはそのままC列を下方にフィルコピーすれば、以下の様な表が完成します。 A B C D E F G H I 1 集計表 仕入れ集計 2 品名 個数 単価 小計 品名 検索用 単価 3 りんご 20 100 2000 りんご りんご1 100 4 みかん 30 200 6000 みかん みかん1 200 5 ばなな 15 150 2250 ばなな ばなな1 150 6 かき 35 250 8750 マンゴ マンゴ1 400 7 りんご 25 120 3000 かき かき1 250 8 パイン 12 300 3600 りんご りんご2 120 9 すいか 18 350 6300 パイン パイン1 300 10 みかん 22 180 3960 すいか すいか1 350 11 ぶどう 27 220 5940 キュイ キュイ1 380 12 りんご 31 160 4960 みかん みかん2 180 13 ぶどう ぶどう1 220 14 りんご りんご3 160 15 （D列は単純な演算子、掛け算をしてるだけですね） 今回は以上です。＾＾ 分からない部分がある方は、分からない部分を 明記して、メールでご質問下さい。もしくは関数Q&A掲示板でも結構ですよ。＾＾ ◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□ シートでサンプルとしてDL出来るようにしました。 今回は以下のページより、「061209.lzh」をダウンロードして下さい。 http://ginyusijin.hmc6.net/page045.html ◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□◆◇■◇◆□ ====================================================================== ☆★☆気になるサイト☆★☆ このコーナーは、私、詩人がネットサーフィンをして発見したサイトや ご紹介したいサイトを取り上げて、皆さんにご紹介いたします。 ★今週の気になる話題 セレブ御用達のカジュアルなファッション アバクロンビー＆フィッチ（Abercrombie&Fitch）を激安で販売♪ 「アバクロ激安通販」 http://suekun.k-server.org/ 先日、詩人は久しぶりにゴルフに行ってきました。 その時一緒に回ったのが、アメリカに支店を持つ会社の部長さんでした。 その部長さんは37歳と若くとてもカジュアルで素敵な井出達でした。 「素敵なウェアーですね。」彼にそう話しかけると「アバクロだよ」って、 「えっ？アバクロ？？？」正直この時私はこのアバクロと言う物を 知らなくて、「へぇ〜」としか返事出来ませんでしたが、自宅に帰って 部長さんの素敵なファッションが気になってぐぐってみました。 するとアバクロってアメリカでは、 セオドア・ルーズベルト、ジョン・F・ケネディ、アーネスト・ヘミングウェイ ハリウッド等では、ブリトニースピアーズ、ブラッド・ピット、ベッカム そして日本でも、キムタク、中居くん、浜崎あゆみなどが愛用している 日本未上陸のアメリカブランドの洋服なんですね。 さすが有名どころが愛用してるブランドだけあってデザインもいいけど、 お値段も大変良くて、ちょっと簡単には買えないかな？と思っていましたら ありました。＾＾ アメリカのアウトレット経由で通販販売しているサイトが♪ それがめちゃ安なのです。 こんなお値段で本当にいいの？って感じで私も数着買いましたが、 とても気に入りました。 そこで折角なので、私の様にアバクロの良さを知らない方の為に 自分でその通販サイトの紹介ページを作成しました。 良かったら皆さんも覗いてみて下さい。＾＾ 吟遊詩人 ====================================================================== ====================================================================== ☆★☆読者からのお便り☆★☆ 今週は読者からの便りはありませんでした。 みなさん関数に関することやその他どんな話題でも結構なので投稿して下さい。 お便り待ってます。＾＾ 投稿は下記のメールアドレスからどうぞ。 suekun@msn.com ====================================================================== ====================================================================== ☆★☆詩人コラム☆★☆ 『気ままに随筆』 いよいよ今年も終りですね。 みなさん、忘年会や色んな行事に目白押しでしょうね。 各言う私も今週はスケジュールが一杯です。 今週は会社の忘年会と取引先との忘年会、ゴルフコンペと明日からほとんど 休み無くイベントが入ってます。 今日も子ども会主催の地域の公園のお掃除＆焼き芋大会でしたが、 あいにくのお天気で中止となりました。＾＾； そんな中で、少し気になったのがイジメ問題に対する環境庁の対応でした。 先日子供が文部科学省配布のチラシを一枚持ってきました。 内容は子供と多く対話して、わが子の変化に気づきイジメを早期に把握して わが子を守って下さいって言う内容でした。 言ってる事はわかります。 しかし、行政が言うよりずっと当の保護者達は真剣に子供の事を考えてます。 そのチラシがどれ程の効果があるのか？ なんかやってる事が、世間からの批判の責任逃れの為の方策ばかりで、 真剣に事に取り組んでいる様に思えないのは私だけでしょうか？ もちろん色んな問題の原因には家庭での教育も多く関係してると思います。 だけど、それぞれの家庭は生活を抱えてそれぞれの事情に合わせて 出来うる限りの事をしてると思うのです。 もちろん、子育て初心者の我々新米親たちも勉強不足の部分は否めませんが 我々新米親も間違った教育を施さないよう当然努力しますが、 親にチラシを配るのでは無く、もっと学校教育に関与する先生やその関係者 の方々に有効な指導をして欲しいものです。 先生は「聖職」と考えておられる先生方が一体どれ程残っておられるのか？ 子供達の作る将来に不安を覚える今日この頃です。 ===================================================================== パソコンのすべてが速くなる、ベストセラー統合加速ユーティリティー<BR> 「速PACK2005 SE」。<BR> http://af1.mag2.com/m/af/0000062932/001/s00000000065002/014 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ このメールマガジンの購読申し込み/解除は以下のページでどうぞ。 http://ginyusijin.hmc6.net/page044.html 吟遊詩人とエクセル http://ginyusijin.hmc6.net/ ********************************************************************** このメールマガジンは、インターネットの本屋さん『まぐまぐ』 を利用して 発行しています。http://www.mag2.com/ (マガジンID: 0000189968 ) **********************************************************************
