Excel こまもの市場


ここはExcelの基本操作と関数の細々部分を寄せ集めたページです。
個人的に感動したツールなどコメントとして付け加えたりしています。参考下さい。



+-- 目次 --+

No.007 ブック名とシート名を関数で得る方法!?   

No.006 範囲から最大値の列の見出しと行の見出しを表示する   

No.005 1行置きにセルに色を付ける

No.004 3軸グラフの作成方法

No.003 入力金額を金種毎金額と枚数を求める

No.002 文字列からある特定文字以降の文字列を抽出

No.001 ワイルドカード文字


Close

No.007 ブック名とシート名の取得 !?  

 こまものシリーズ、メールで「変わったテクニックで面白い!」といったお言葉を頂きました。
その通りです。こまものは当たり前のことではなく変わったものを特集しようと考えて開設しました。
さぼり癖から伸び悩みはありますがメールや掲示板で応援を頂き俄然張り切る単純なMambowでした。

.... ^^

NO.007は、以前QAを開設しているときにブックの名前とシートの名前を知る方法を教えてといった質問を頂きました。今回メールで同じような質問を頂きましたので参考としてこの場にアップします。

■ ブック名とシート名はCELL関数で結果を得ることができます。
  書式
     CELL(検査の種類,対象範囲)
 
検査の種類  必要なセル情報の種類を、半角の二重引用符 (")で囲まれた文字列として指定します。次に、検査の種類として指定できる文字列と、それを指定したときの結果を示します。

検査の種類 戻り値
address 対象範囲の左上隅にあるセルの参照を表す文字列。
col 対象範囲の左上隅にあるセルの列番号。
color 負の数を色で表す書式がセルに設定されている場合は 1、それ以外の場合は 0。
contents 対象範囲の左上隅にあるセルの内容。
filename 対象範囲を含むファイルの名前 (絶対パス名) を表す文字列。対象範囲を含むファイルがまだ保存されていない場合、結果は空白文字列 (") になります。
format セルの表示形式に対応する文字列定数。それぞれの表示形式に対する文字列定数は、以下の一覧表に示します。負の数を色で表す書式がセルに設定されている場合、結果の文字列定数の末尾に "-" が付きます。正の数またはすべての値をかっこで囲む書式がセルに設定されている場合、結果の文字列定数の末尾に "()" が付きます。
parentheses 正の数またはすべての値をかっこで囲む書式がセルに設定されている場合は 1、それ以外の場合は 0。
prefix セルに入力されている文字列の配置に対応する文字列定数。セルが左詰めの文字列を含むときは一重引用符 (')、右詰めの文字列を含むときは二重引用符 (")、中央配置の文字列を含むときはキャレット (^)、均等配置の文字列を含むときは円記号 (\)、そのほかのデータが入力されているときは空白文字列 (") になります。
protect セルがロックされていない場合は 0、ロックされている場合は 1。
row 対象範囲の左上隅にあるセルの行番号。
type セルに含まれるデータのタイプに対応する文字列定数。セルが空白の場合は "b" (Blank の頭文字)、セルに文字列定数が入力されている場合は "l" (Label の頭文字)、そのほかの値が入力されている場合は "v" (Value の頭文字) になります。
width 小数点以下を切り捨てた整数のセル幅。セル幅の単位は、標準のフォント サイズの 1 文字の幅と等しくなります

  1. 検査の種類は filename です。   式は求めたいセルに   =CELL("filename",A1)  を埋め込みます。範囲はどこのセルでも良い。

    結果は絶対パス名で表されます。     →  D:\tool\[koma007.xls]Sheet1

  2. 次はブック名とシート名をセパレートします。最初にブック名です。

    数式
     =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
                     CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

    結果は   → koma007.xls

    MID関数で抜き出す文字列は CELL("filename",A1)=D:\tool\[koma007.xls]Sheet1 です。
    ブック名の開始位置は"["の次からになるので  FIND("[",CELL("filename",A1))+1 です。
    ブック名の文字数は、FIND("]",CELL("filename",A1)) 先頭から閉じ括弧 "]" までの文字数から FIND("[",CELL("filename",A1))-1) 先頭から入り括弧 "[" までの文字数を差し引いた文字数がブック名となる。

  3. 次はシート名です。

    数式
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
                LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

    結果は → Sheet1
    ブックの説明を元にシンキングして下さい。理解できる方は掲示板などで解説してくれるといいですね。  よろしくおねがいします。


    尚、ブック名は koma007.xls シート名は Sheet1 を使用しています。

    Sampleブックの欲しい方はメールでお申し込み下さい。


No.006 最大値の列の見出しと行の見出し  

 下図のように範囲から最大値と対象セル位置の列の見出し「月」と行の見出し「担当者」を表示する関数です。
説明を読む前にこの課題を考えてみて下さい。簡単なようで難しいことがわかると思います。
えっ!「簡単じゃん」....  お見それです。   ^^

■ セルB3:M10の範囲から最大値を割り出しそのセル位置の列と行の見出しを表示するサンプル

  1. 結果を表示するセルは月→セルB13 担当者→セルB14 個数の最大値→セルB15 です。
  2. 使用した関数はその右横に表示しています。
  3. 月と担当者は配列数式を宣言します。

個数も配列数式で求める場合は =INDIRECT(ADDRESS(SUM(IF(B3:M10=MAX(B3:M10),ROW(B3:B10))),SUM(IF(B3:M10=MAX(B3:M10),COLUMN(B3:M3)))))     となります。 


No.005 一行置きにセルに色を付ける  

 条件付き書式設定で1行置きにセルに色を付すことができます。

■ 一行置きにセルに色を付したサンプル

  1. セルB1からF13を選択する。
  2. メニューバー「書式」→「条件付き書式」を選択する。
  3. 条件付き書式の設定ダイヤログ上図の内容の通り設定します。
    ※ =MOD(ROW(),2) の結果を H列 にサンプリングしています。ROW()は自分のいるセル行を求めます。そのセル行をMOD関数 2 で 除した余りを求めます。 条件付き書式の数式から色を付す条件が TRUE の時.... となるので MOD(余りを求める関数) 1(奇数行) の時に色が付く条件となります。
偶数行の時ですって...   数式を  =NOT(MOD(ROW(),2))  否定形にすると反対になる。 


No.004 3軸グラフの作成  

 Excelでグラフを作成する場合2軸が基本と。数の通り操作によって3軸のグラフを描くことができます。

■ 3軸の票とグラフサンプル

  1. A1:G9を選択し「折れ線グラフ」で基本グラフを作成します。
  2. メニューバー「ツール」→「オプション」 のグラフタブを選択し 「補完してプロットする」をチェックします。
  3. 件数1から件数3の系列をそれぞれ2軸にします。 件数1の折れ線グラフをダブルクリック「データ系列の書式設定」ダイヤログ「軸」タブを選択し「第2軸」 をチェックする。この操作を件数2、件数3同様に行います。
  4. 数1から件数3のグラフを棒グラフに変更、 対象グラフをポイントし右クリック、ショートカットメニュー「グラフの種類」を選択し変更します。
   この操作で作成したグラフが上図になります。


No.003 金種枚数の計算  

 入力した金額の金種毎に用意する枚数を計算する。

■ 入力した金額から金種毎の金額・枚数を求めるサンプル


セルC2の IF(ROW()=2, は先頭の金種計算(金種10000)の判断です。
 真の時は、$A$2-MOD($A$2,B2)) を計算します。 
この式は 348,898 を 10000 で除した余り 8898 を入力金額から差し引いた金額を求めています。 
結果 340,000 です。
 偽の条件は 2件目となるセルB3(5000)以降の計算です。
金種5000のセルC3の偽の条件式 ($A$2-MOD($A$2,B3))-SUM($C$2:INDIRECT("C"&ROW()-1)))
を見ると ($A$2-MOD($A$2,B3)) は 348,898 − 余り3898(348,898/5000) = 345,000

SUM($C$2:INDIRECT("C"&ROW()-1)) は 求めた金種の集計です。セルC2から INDIRECT("C"&ROW()-1)
 カレントセルの前の行までを計算します。前の行は 340,000 になるので 
345,000 − 340,000 = 5,000 この金額が金種5,000の金額です。
以下同様に計算します。


No.002 特定文字以降の文字列抽出  

 文字と数値が一体になっている文字列でその区切りが「$」でとなっている。
その文字列と一体になった数値の抜き出し方とその文字列の中に含まれる数値をダイレクトに加算する場合の数式は数の通りです。
 ※区切り文字は $ でなくてもよい。



■ $ をワイルドカードのように区切り文字として使用した例

  1. B列が抽出値 抜き出した数値は文字として扱われるため VALUEで文字列を数値として扱っている。また、FIND関数で+1しているのは $ の次の文字... の意味です。
  2. セルD2は 配列関数での合計です。セルB8は 抜き出した値の合計です。スタイル的には抜き出した後の合計の方が落ち着きますね。  (経過が見れる。)


No.001 ワイルドカード文字    

次のワイルドカード文字は、フィルタの比較検索条件として、文字列のあいまい検索、置換の時に使用します。


文字 検索対象
? (疑問符) 任意の 1 文字
たとえば、「インターフェ?ス」と入力すると "インターフェイス" や "インターフェース" が検索されます。
* (アスタリスク) 任意の数の文字
たとえば、「Win*」と入力すると "Win98" や "Win2000" が検索されます。
~ (チルダ) (?、*、または ~ の前に入力) 疑問符、アスタリスク、またはチルダ
たとえば、「fy91~?」と入力すると "fy91?" が検索されます。

■ワイルドカードを使用しての SUMIF関数