おっ -2ページ目

おっ -2ページ目

ホームピグアメブロ

芸能人ブログ人気ブログ

15Jul
- ピボットで複数ある列の重複リストをユニークに
  ピボットテーブルの分析するデータのある場所の選択に複数のワークシート範囲というのがあります。これを使って重複しないリストを作ることができます。さて、先日、これを使った表を作っておりました。どんなのかというと、下のような表を合算して平均やら合計やらを確認します。（正しい使い方かはわかりませんが。。。）１．[データ]-[ピボットテーブルとピボットグラフレポート]を選択します。２．ウィザード1/3で複数のワークシート範囲を選択します。３．ウィザード2a/3のページフィールドの作成方法を"指定"にします。４．ウィザード2b/3で統合する範囲を指定して追加していきます。ここでは、東京の表Ａ４～Ｄ７と大阪の表Ａ１１～Ｄ１５をそれぞれ追加します。その際、ページフィールド名を付ける場合はそれぞれ任意の名前を書き込みます。（画像では"大阪"と書かれた箇所、範囲一覧で追加したのをクリックして記入します）５．ウィザード3/3で書き出すセルを指定して完了です。すると、こんな感じのピボットテーブルができあがります。いわゆる、東京と大阪の店舗と商品が同じものを合計した表です。ちなみに、先のウィザード2b/3で指定したページフィールド名は下の図のような感じで選択リストになります。さてさて、ここで注目するのはセルＧ６～Ｇ９です。重複を省いた店舗名がリスト化されているのです。これを使うと、複数行にまたがって存在する重複のあるデータリストから重複しないリストを作ることができるということです。例えば下図、セルＡ１９～Ｃ３８のように重複した複数行のデータがあるとします。ここから重複しないリストを作るには？先ほどの複数のワークシート範囲を使い、範囲一覧で２列ずつ範囲選択して追加していくのです。一つ目：Ａ７：Ｂ２７　・・・　Ａ列が行、セル７が列として認識される二つ目：Ｂ７：Ｃ２５　・・・　Ｂ列が行、セル７が列として認識される三つ目：Ｃ７：Ｄ２２　・・・　Ｃ列が行、セル７が列として認識されるこれでＡ、Ｂ、Ｃ列が項目として認識され、２つ上の表が完成します。２つ上の黄色のセルのところが重複しないリストというわけです。指定する範囲はひとつ上のセルから指定します。ここでは７行目の空白セルからの指定です。８行目から範囲指定をすると８行目がデータとして認識されないので。フィールド名のあるリストの場合はそこから指定すればＯＫです。ちなみに、データの個数は正しくないのでご注意。-----■　参考　■-----重複しないリストをつくるリストから重複するデータをはぶいた件数（個数）をカウントする
14Jul
- 重複しないリストをつくる
  重複だらけのリストから重複しないリストをつくるにはどうすればよいか。エクセルには便利な昨日が備わっています。（私が使っているのはExcel2003）思いつく方法は３つ。■　１．フィルタオプションを使う　（超便利）■　２．ピボットテーブルを使う　（ちょっとメンドクサイ）■　３．関数を使う　（めっちゃメンドクサイ）このようなダブりのあるリストから、重複を除いたリストをつくります。-----------------------１．フィルタオプション-----------------------[データ]-[フィルタ]から「フィルタオプションの設定」を選択します。すると、フィルタオプションの設定、というウィンドウが開きます。・　指定した範囲(O)　を選択・　リスト範囲(L)　に重複したリストを選択します・　検索条件範囲(C)　に重複した列のフィールド名を選択します・　抽出範囲(T)　には結果をはきだすセルの先頭セルを指定します・　重複するレコードは無視する(R)　にチェックを入れます後はOKボタンを押すだけです。楽勝です！-----------------------２．ピボットテーブル-----------------------[データ]-[ピボットテーブルとピボットグラフレポート]を選択します。項目を"ここに行のフィールドをドラッグします"というところにドラッグします後はどこかにコピペすればOK。-----------------------３．関数-----------------------いつものごとく配列を使います。（Ctrl + Shift + Enter）{=INDEX($A$2:$A$11,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$1:$A$10),ROW($A$1:$A$10),""),ROW(A1)))}入力し終わったらCtrl + Shift + Enterで確定させます。オートフィルでずるずると下に伸ばします。#NUM!とでるところまでが重複しないリストです。#NUM!が表示されないようにするにはISERR関数を使ってみてください。これ、あんま実用的じゃないね。めんどくさいし。-----■　参考　■-----ピボットで複数ある列の重複リストをユニークにリストから重複するデータをはぶいた件数（個数）をカウントする
09Jul
- ウォーターフォールグラフ（waterfall）
  使う機会がないのですが、とりあえず。ウォーターフォールなグラフです。一番左の棒が全体を表していて、そこから右の棒が滝のように徐々に落ちて行くイメージのグラフで、全体の構成を表しています。このグラフは先の階段グラフと同じで、積み上げ棒グラフを使用しています。なので、表の作り方がすべてです。この表では、グレーのセルに数値を入力します。（グレーの部分がグラフ化される）セル（Ｂ３：Ｂ７）には数式が入っています。ただの引き算です。この表は全体が１００なので、B3　=　B2　-　C3　→　100 - 20 = 80以下オートフィルでずるる、といく。　これで表が完成です。※ちなみに、C2とC7はブランクです。--------------------■　グラフを作る　■--------------------続いて、グラフを作成します。■１．表全体を選択します。■２．積み上げ縦棒を選択してグラフを作ります。■３．グラフを整えます。グラフオプション：・軸→Y/数値軸(V)　チェックOFF・目盛線→Y/数値軸、目盛線(O)　チェックOFF・データラベル→ラベルの内容：値　チェックONプロットエリアの書式設定：・輪郭　なし・領域　なしデータ系列の書式設定：・棒の間隔(W)　50データラベル：・系列"値"の要素1～4の値ラベル削除・系列"値"の配置→ラベルの位置　内側上データ要素の書式設定：・系列"値"の要素1～4のパターン→輪郭、領域なし・要素5の棒の色を要素1と合わせるこれで完成です。-----■　参考　■-----逆ウォーターフォールもどき階段グラフ世に言う？階段グラフ。公定歩合みたいな？！
04Jul
- 条件を付けて最大値やら最小値やら平均値やらを
  条件を付けて最大値やら最小値やら平均値やらを求めるには？？たとえば、Ａ列に品物、Ｂ列に値段の記載のある表があったとして、・　Ａ列が"りんご"の一番高い値段（最大、max）を求める、とか・　Ａ列が"りんご"の一番安い値段（最小、min）を求める、とか・　Ａ列が"りんご"の平均の値段（平均、average）を求める、とか・　Ａ列が"りんご"の合計の値段（合計、sum）を求める、とか・　Ａ列が"りんご"の個数（カウント、count）を求める、とかそんな感じで条件をつけて計算をするには？今回も配列を使います。　Ctrl + Shift + Enter　のやつです。一応、最後のほうにデータベース関数を使った式も書いておきます、あまり好きじゃないけど。この表からa　の　最大値を求めるには、{=max((A1:A8=D2)*B1:B8)} もうひとつの方法としては{=max(if(A1:A8=D2,B1:B8))} 最小値を求めるには{=min(if(A1:A8=D2,B1:B8))}平均値を求めるには{=average(if(A1:A8=D2,B1:B8))}合計値を求めるには{=sum(if(A1:A8=D2,B1:B8))}これは　sumif　と同じ。個数を数えるには{=count(if(A1:A8=D2,B1:B8))}これは　countif　と同じ。 ※　ここで紹介した数式は入力確定時に　Ctrl+Shift+Enter　をします。　　そうすると数式の前後に｛｝がつきます。このような感じで。------これとは別に、データベース関数なるものを使う方法があります。ここで使ってる関数はDmaxDminDaverageDsumDcount こいつはちょっとした準備が必要です。先ず、もとのリストにフィールド名を付けます。図でいうと、Ａ１の"item"とＢ１の"qty"です。次に、検索用のセルを作ります。図でいうと、Ｄ１とＥ１です。で、検索条件をＤ２に入れます。図の例では　a　ですが、b　にかえれば　b　の結果が返ってきます。数式は図の通り。最大値を求めるDmaxを例にとると、=DMAX(A1:B9,B1,D1:E2)DMAX(フィールド名を含むもとのリスト,計算する値のフィールド名,検索条件を記載したセル範囲)という感じです。
27Jun
- 勤務表から時間帯ごとにいる人数をカウント
  勤務表から時間帯ごとに何人いるかカウントする表を作ってみました。10時台には何人いますよ、って感じの。表のようなある日の勤務表があります。そこには・出勤時間・退勤時間・休憩時間（入り時刻と戻り時刻）のデータが記載されています。（休憩時間を差し引く必要がなければ出勤、退勤だけでOK）同じシート、別のシートどこでもよいので時間の間隔を入れます。その横に"人数"というフィールドを作成し、数式を入れます。今回は配列を使います。(数式を確定させるときに　Ctrl + Shift + Enter)先ずは図をみてください。J5セル、だいぶ長い数式が入ってますが、=(SUM(IF($B$5:$B$24<>"",IF(CEILING($B$5:$B$24,$I$4)<I5,1,0)))-SUM(IF($C$5:$C$24<>"",IF(FLOOR($C$5:$C$24,$I$4)<I5,1,0))))-(SUM(IF($D$5:$D$24<>"",IF(FLOOR($D$5:$D$24,$I$4)<I5,1,0)))-SUM(IF($E$5:$E$24<>"",IF(CEILING(CEILING($E$5:$E$24,$I$4)+"0:01",$I$4)<=I5,1,0))))大きくは4つに分解されます。①(SUM(IF($B$5:$B$24<>"",IF(CEILING($B$5:$B$24,$I$4)<I5,1,0)))②-SUM(IF($C$5:$C$24<>"",IF(FLOOR($C$5:$C$24,$I$4)<I5,1,0)))③-(SUM(IF($D$5:$D$24<>"",IF(FLOOR($D$5:$D$24,$I$4)<I5,1,0))④-SUM(IF($E$5:$E$24<>"",IF(CEILING(CEILING($E$5:$E$24,$I$4)+"0:01",$I$4)<=I5,1,0))))式としては（①-②）-（③-④）という感じで①②はその時間にいた在籍人数を、③④は休憩に入った人数を計算しています。使用している関数を説明しておくと、----------------CEILING　切り上げ関数です。指定した基準値の倍数でのうち最も近い値に切り上げてくれます。　式）Ceiling(数値,基準値)　例）Ceiling("8:05","0:30") = 8:30----------------FLOOR　切り捨て関数です。指定した基準値の倍数でのうち最も近い値に切り捨ててくれます。　式）Floor(数値,基準値)　例）Floor("8:05","0:30") = 8:00.では、ひとつずつ見ていきます。①(SUM(IF($B$5:$B$24<>"",IF(CEILING($B$5:$B$24,$I$4)<I5,1,0)))もし、出勤の列が空でなかったら、　そしたら、もし出勤の列の値を0:30($I$4)単位で切り上げた値が8:30(I5)未満だったら　　1を返します、そうでなければ0を返します。　　　そして、その数字たちを合計します(Sum)これで、出勤の列の個数（人数）がでます。ただし、ここでは退勤は加味されていないので、その分を引かなければなりません。それが②です。②-SUM(IF($C$5:$C$24<>"",IF(FLOOR($C$5:$C$24,$I$4)<I5,1,0)))もし、退勤の列が空でなかったら、　そしたら、もし退勤の列の値を0:30($I$4)単位で切り捨てた値が8:30(I5)未満だったら　　1を返します、そうでなければ0を返します。　　　そして、その数字たちを合計します(Sum)これで、退勤の列の個数（人数）がでます。なので、①から②を引くとその時間帯にいた人数が計算されます。③④も同じ原理です。12:00-13:00が休憩時間の場合でタイムカードが12:01-12:59だった場合は、③-(SUM(IF($D$5:$D$24<>"",IF(FLOOR($D$5:$D$24,$I$4)<I5,1,0))12:01を切り捨てて12:00にします。④-SUM(IF($E$5:$E$24<>"",IF(CEILING(CEILING($E$5:$E$24,$I$4)+"0:01",$I$4)<=I5,1,0))))12:59を切り上げて0:01追加して切り上げることで13:00台でカウントをします。③12:00以降　に1がたつ④13:30以降　に1がたつので、③-④をすると12:00-13:00の間だけ1が残るのです。で、（①-②）-（③-④）をするとその時間帯の人数が算出されるということです。下の図が完成図数式を入力したら最後に Ctrl + Shift + Enter　をします。これをすることで数式の前後に　｛　｝が付き、配列化されるのです。↑これ、忘れると正しい結果がでませんので。
19Jun
- 補助円グラフ付き円グラフ（値、パーセント）
  補助円グラフ付き円グラフを作るには？その２。ちなみに補助縦棒グラフ付き円グラフも同じです。前回は表の位置から補助グラフを作りましたが、□・値が一定以下のものを対象として補助グラフ化□・表の値が全体のn％以下のものを大将として補助グラフ化の2つを試します。表のようなやさいの数量データがあるとします。今回は以下2つのでグラフ化します。１．値が10未満２．値の割合が10%未満■--Step 1--■最初にグラフのもととなる表を作ります。前回の位置とは違い表の並び順はなんでもOKです。では早速、、表を選択してグラフウィザードを立ち上げ補助円グラフ付き円グラフを選択しグラフを作ります。するとなんとなく、なグラフができあがります。■--Step 2--■＿＿＿先ずは"値"から。・どこでもよいので円グラフをＷクリック・[データ系列の書式設定]が開くのでオプションタブを開く・　系列の分割を"値"に変更します。・　[補助プロットに次の値未満をすべて含む]を10にします。　※[補助プロットのサイズ]で補助円グラフのサイズ調整をする　※[棒の間隔]でお互いの円グラフの距離を調整する・データラベルタブで"分類名"と"パーセンテージ"にチェックを入れるこれでとりあえずグラフはできあがり。＿＿＿次は"パーセント"を。・どこでもよいので円グラフをＷクリック・[データ系列の書式設定]が開くのでオプションタブを開く・系列の分割を"パーセント値"に変更します。・[補助プロットに次の値未満をすべて含む]を10にします。（10%）　※[補助プロットのサイズ]で補助円グラフのサイズ調整をする　※[棒の間隔]でお互いの円グラフの距離を調整する・データラベルタブで"分類名"と"パーセンテージ"にチェックを入れるこれでとりあえずグラフはできあがり。 2つのグラフを比較してみてください。なんとなく違いがわかりますね。-----■　参考　■-----補助円グラフ付き円グラフ（位置）
17Jun
- 補助円グラフ付き円グラフ（位置）
  補助円グラフ付き円グラフを作るには？ちなみに補助縦棒グラフ付き円グラフも同じです。表のような使用食材の数量データがあるとします。ここでハイライトしたいのは、・肉類の比率が高いこと・肉類の内訳がどうなっているかということにします。■--Step 1--■最初にグラフのもととなる表を作ります。ここでは位置（並び順）をベースとした補助グラフの作り方でいきます。この場合、大事なのは補助グラフにするデータは下に配置することです。図にある表では、牛、豚、鶏、羊を肉類としてひとくくりとし補助グラフ化するため、表の下に配置（記載）しています。では早速、、表を選択してグラフウィザードを立ち上げ補助円グラフ付き円グラフを選択しグラフを作ります。するとなんとなく、なグラフができあがります。■--Step 2--■・どこでもよいので円グラフをＷクリック・[データ系列の書式設定]が開くのでオプションタブを開く・[補助プロットに含む値の個数]を補助円グラフの系列数に合わせる　※ここでは牛、豚、鶏、羊の4種類あるので値を　4　にする　※[補助プロットのサイズ]で補助円グラフのサイズ調整をする　※[棒の間隔]でお互いの円グラフの距離を調整する・データラベルタブで"分類名"と"パーセンテージ"にチェックを入れるこれでとりあえずグラフはできあがり。■--Step 3--■肉類にフォーカスさせるために、メインの円グラフは肉類以外の色を落とします。円グラフの扇形を2回ゆっくりクリックすると該当場所だけ選択ができるので、選択したらＷクリックし領域の色を変更します。次に、メイン円グラフの肉類に該当する切片だけ少し切り離します。要領は上のと同じ、その部分だけ選択した後にグッと右に引っ張る感じです。補助円グラフの色を適当に変更すれば完成です。実は、メインの円、補助円ともに系列をドラッグ＆ドロップで移動できたりします。ドラッグ＆ドロップで移動させた時点で[系列の分割]は"ユーザー設定"になります。-----■　参考　■-----補助円グラフ付き円グラフ（値、パーセント）
11Jun
- 逆ウォーターフォールもどき階段グラフ
  ウォーターフォールに似たような、逆さまの、階段グラフです。（積み上げ縦棒）ここでは、地方税、国税、特別税、消費税のバーが税合計の内訳を表しています。地方税＋国税＋特別税＋消費税＝税合計いちおう、比率を見るグラフです。■--Step 1（表の作成）--■下図のような表を作ります。先ずは"数値"の列□　・地方税、国税、特別税、消費税は手入力、□　・税合計は地方税、国税、特別税、消費税の合計（sum関数）□　・原価は手入力□　・利益は手入力次、"系列1"の列□　・税合計、地方税は0□　・国税は数値列[地方税]＋系列1列[地方税]（87.44＋0）□　・特別税は数値列[国税]＋系列1列[国税]（71.04＋87.44）□　・消費税は数値列[特別税]＋系列1列[特別税]（16.4＋158.48）□　・原価は数値列[消費税]＋系列1列[消費税]（14.29＋174.88）□　・利益は数値列[原価]＋系列1列[原価]（80.83＋189.17）要は、国税からはひとつ上の数値セル＋系列1セルの値を入れるということ。■--Step 2（グラフの土台作成）--■表ができたら、その表をもとにグラフを作ります。使うグラフは"積み上げ縦棒"です。□　１．　グラフウィザードで積み上げ縦棒を選択□　２．　系列を追加（系列1）し、表の系列1列の値をセット□　３．　系列を追加（系列2）し、表の数値列の値をセット□　４．　項目軸ラベルに表の項目列の値をセットこれで、下のような積み上げ縦棒グラフができます。 ■--Step 3（グラフの整形）--■最後、薄い水色の縦棒をＷクリックします。（データ系列の書式設定）パターンタブで・輪郭　なし・領域　なしにします。これで出来上がり。-----■　参考　■-----ウォーターフォールグラフ（waterfall）世に言う？階段グラフ。公定歩合みたいな？！
10Jun
- グラフでガントチャートをつくる
  Excelのグラフでガントチャート（Gantt Chart）を作ってみました。実用性はあまりないので見て楽しむ程度のものでしょうか？できあがりのイメージはこんな感じです。■--Step 1--■まず、下のような表を作ります。（項目は適当なので気にしないで）項目、開始日、完了日は手入力です。期間は計算式　完了日－開始日　で、表示形式は"数字"です。■--Step 2--■次に、積上横棒グラフを作ります。・1つめの系列は表の[開始日]です。・2つめの系列は表の[期間]です。・項目軸ラベルは表の[項目]を指定します。すると、下のような積み上げ横棒グラフができあがります。■--Step 3--■最後、薄い水色の横棒をＷクリックします。（データ系列の書式設定）パターンタブで・輪郭　なし・領域　なしにします。これで出来上がり。
- 横棒グラフの項目軸ラベルを表の並び順に
  横棒グラフを（バーグラフ）作ったところ、項目軸ラベルが表の順番と逆になってしまいました。↑これが↓こんなになった。こいつを表の並び順のように一番上がA、下がHとなるように表示したい、ただそれだけですが意外と悩みました。で、どうするかというと□１．　項目軸ラベルをＷクリック（軸の書式設定）□２．　目盛タブを開く□３．　[軸を反転する(R)]と[最大項目でY/数値軸と交差する(M)]にチェック入れるすると並びかわります。こんな感じに。
15Apr
- セル内での改行を削除する（＋よく使うショートカットキー）
  Alt+Enter　で1つのセル内で改行をすることができるが、その改行を解除、削除したい時にはどうするか？□　１．　該当するセルを選択する□　２．　Ctrl+Hで[検索と置換]ウィンドウを表示する□　３．　"検索する文字列(N)"の入力ボックスにカーソルを合わせる□　４．　Ctrl+J　を入力する（Ctrlキーを押しながらJキーを1回押すだけ）　　　　　※見た目は何も変化ありません□　５．　"置換後の文字列(E)"は空欄のまま[置換]ボタンを押すExcelでよく使うショートカットキーをメモッとく。よく使うショートカットのほとんどは左手だけで完結できるもので、右手はマウスから手が離れないので右手を使う必要のあるショートカットは使わない。。Ctrl 1　　･･･　[書式設定]ウィンドウを表示するCtrl a　　･･･　すべてを選択するCtrl c　　･･･　コピーCtrl v　　･･･　貼り付けCtrl x　　･･･　切り取りCtrl h　　･･･　[検索と置換]ウィンドウを表示するCtrl s　　･･･　上書き保存Ctrl w　　･･･　ブックを閉じるCtrl z　　･･･　直前の操作を元に戻すCtrl g　　･･･　ジャンプ（セルの選択画面を表示する）Ctrl -　　･･･　行列セルの削除Ctrl +　　･･･　行列セルの挿入Ctrl ;　　･･･　今日の日付を入力Ctrl 矢印キー　･･･　データが途切れるセルまでジャンプCtrl Shift 矢印キー　･･･　データが途切れるセルまで選択---□　Excel 2002 ショートカットキー　□---http://www.microsoft.com/japan/enable/products/keyboard/excel2002.mspx---□　Excel 2003 ショートカットキー　□---http://www.microsoft.com/japan/enable/products/keyboard/excel2003/default.mspx
03Apr
- 計算式を入れるとセルに数式が表示されてしまう・・・
  計算式を入れるとセルに数式が表示されてしまう・・・とか　とりあえず、最近　悩んだExcelのことを少々、、、□　１．　セルに計算式を入れると数式そのものが表示される□　２．　文字情報になっている数字を数値として認識（変換）させる□　３．　ツールバーアドインが認識されなくなった---◇　１．セルに計算式を入れると数式そのものが表示される見た目は数字なのにExcelのセルに計算式を入れると、なぜかその数式が表示されてしまいました。なぜでしょう？しばらく悩みました。。[ツール][オプション][表示]タブ>ウィンドウオプションの数式にはチェックが入ってません。。今回の答えは簡単でした。すべてのセルの表示形式が文字列になっていました。・セルを全選択してセルの書式設定を開きます（Ctrl+1）・表示形式から「標準」を選びますこれだけです。---◇　２．文字情報になっている数字を数値として認識（変換）させる見た目は数字なのに数字として認識していない場合に、そのセルを数字として認識させるには、１つだけであれば・該当セルを選択・F2を押す・Enterを押すこれで大丈夫のはずです。大量にある場合、例えば1列丸ごととか、複数列とかの場合は、・空白セルをコピーします（表示形式が"標準"のセル）・数字として認識させたいセルをすべて選択します・右クリック→[形式を選択して貼付]を選びます・[貼り付け]で"値"にチェック、[演算]で"加算"にチェックを入れOK押しますこれでいけます。---◇　３．ツールバーアドインが認識されなくなった自作のツールバーアドインが動かなくなりました。↓こんなのとか。http://ameblo.jp/xls/entry-10070713267.htmlアドインの場所を移動すると動きません。その場合は・[ツール][アドイン]で該当するアドインのチェックをOFFにします・[ツール][ユーザー設定][ツールバー]タブで該当するツールバーを削除します・[ツール][アドイン]で[参照]ボタンを押してアドインが保存されてる場所を指定しますこでれ大丈夫と思います。最近のExcelネタでした。
19Mar
- 複数の列で条件に一致する行をカウントする
  複数の列でそれぞれの検索条件を設定して、条件が一致した、もしくは含んだ行をカウントするにはどうする？□　①　SUMとIFをつかう□　②　SUMPRODUCTとFINDをつかう下図のようなデータがあるとします。※Ａ列に商品が、Ｂ列に産地が記入されたデータここで、商品がりんごで産地が青森の件数をカウントしたい場合にどうするか？とりあえず思いついた２つの方法を。①は条件に完全に一致する行をカウントします。　　例えば、産地の条件に”青森”と指定したら”青森県”はカウントされない。②は条件を含む行をカウントします（あいまい検索）　　例えば、産地の条件に”青森”と指定したら”青森県”もカウントされる。先ずは①、SUMとIFは下図の通り。={SUM(IF(A2:A8=A12,IF(B2:B8=B12,1,0)))}商品（A2～A8）がりんご（A12）だったら産地（B2～B8）が青森（B12）かどうかを確認し、そうだったら1を返します。違ったら0を返します。それを配列にいれて、Sumで合計してます。なので式を入力する際にはShift + Ctrl + Enterをします。（これをしないと#VALUE!になる）※Shift + Ctrl + Enterをすると数式の前後に{}がつくIFはワイルドカードが使えないのであいまい検索はできません。よって、産地の条件を”青森”とした場合には”青森県”は引っかかりません。次に②、SUMPRODUCTとFINDは下図の通り。=SUMPRODUCT((ISNUMBER(FIND(A17,A2:A8)))*(ISNUMBER(FIND(B17,B2:B8))))これは、FIND関数を使ってるので産地の条件に”青森”を指定した場合、”青森県”もカウントします。あいまい検索が可能、ということです。場面によって使い分けですな。他にSUMPRODUCTを使った例：http://ameblo.jp/xls/entry-10073835124.htmlhttp://ameblo.jp/xls/entry-10073848203.html
- 複数行ごとに色をつけて、みやすく
  http://ameblo.jp/xls/entry-10080631877.html　のつづき。1行ごとではなく、複数行ごとに色をつけるには？2行おきの場合は、こんな感じで条件付き書式の数式をセットします。=OR(MOD(ROW(),4)=0,MOD(ROW(),4)=1)ただし、1行目はだめです。なぜなら上の式をひもとくと、ROW()で行番号を取得して4で割った時の余りが0か1の場合に色を付けるようになります。なので、1行目　：　1÷4＝余り1　　･･･　一致　　＝色がつく2行目　：　2÷4＝余り2　　･･･　不一致＝色はつかない3行目　：　3÷4＝余り3　　･･･　不一致＝色はつかない4行目　：　4÷4＝余り0　　･･･　一致　　＝色がつく5行目　：　5÷4＝余り1　　･･･　一致　　＝色がつくということになり、2行目から2行おきになります。表のスタート位置が大事ってやつです。
17Mar
- １レコード毎にセルに色を付けて見やすく
  [書式]-[条件付き書式]を開く。リストボックスから「数式が」を選択するその右側に=MOD(ROW(A1),2)と入力する。「書式」ボタンを押して「パターン」タブをクリック。好きな色を選ぶ。[OK]を押す。条件を設定したセルを「書式のコピー」して、反映させたいセルをいっきに選択する。ROWは行番号をかえすので、ROW（A1)で１が返ってくる。MODは割り算の余りを返すので、余りのでるセルに色がつく。なので=MOD(ROW(A1),3)とすると、2つ色ついて1つが色なし、が繰り返される。
06Mar
- 退職人数と在籍人数を時系列で計算する
  名簿に退職日が入っていれば、時系列で退職人数と在籍人数を計算できます。使うのはまたCOUNTIFですが。。↓こんな感じの名簿があるとしてどこかに年月日のリストをつくります。↓月　とかかれているセルのような感じで月の1日の日付リストです。※日付は1日にしておく　（表示形式は何でもよいので書式でdayを表示しなくてもOK）退職人数はcountifをつかいます。=COUNTIF([退職日],">="&[計算したい対象月])-COUNTIF([退職日],">="&DATE(YEAR([計算したい対象月]),MONTH(計算したい対象月)+1,1)-1)たとえば、画像にある2007/4/1の退職数を計算したい場合は、=COUNTIF($B$2:$B$11,">="&D5)-COUNTIF($B$2:$B$11,">="&DATE(YEAR(D5),MONTH(D5)+1,1)-1)2007/4/1以降の数を数え、2007/4/30までの数を数え、引くことにより該当月の数を出しています。COUNTIF($B$2:$B$11,">="&D5)ー＞2007/4/1以降の件数をカウント-COUNTIF($B$2:$B$11,">="&DATE(YEAR(D5),MONTH(D5)+1,1)-1)ー＞2007/4/30までの件数をカウント">="&DATE(YEAR(D5),MONTH(D5)+1,1)-1)Year(日付)　で年を抜き出すMonth(日付)　で月を抜き出す。MONTH(D5)+1　で抜き出した月の翌月にするDATE(YEAR(D5),MONTH(D5)+1,1)　で翌月の1日の日付にする最後に-1をして当月末の日付にする※年がかわっても大丈夫です※下図は2007年12月の例（画像Cap間違えた。。。）在籍人数のカウントも同じような感じでcountifを使って算出しています。↓の画像参照。
05Mar
- Webクエリが便利になっていた
  [データ]－[外部データの取り込み]－[新しいWebクエリ]でホームページの表データをExcelに取り込めるんです。テキスト形式でもHTML形式でも取り込め、更新も簡単なので意外と便利です。これは、htmlのtableタグを判別して読み込む仕様なので（たぶん）、むかしのExcelでは取り込みたい表がホームページの何番目の表なのかホームページのhtmlソースを表示して<table>の数を数えてたりしてました。なんでソースから調べるかというと、隠れテーブルが表からだと判別できなくて、指定番号を間違えると関係ないのを読み込んでしまうんです。さらに、バナー広告が追加されたり削除されたりすると表位置がズレて、これまた関係ないのを読み込んだりします。ということがあったのですが、今日、数年ぶりに利用するシーンがあって使ってみたら使い勝手が良くなってました。□・　簡易ブラウザが立ち上がり読み込むサイトを指定できる□・　読み込む表をチェックON/OFFで指定できるこの２つはとても便利ですね。ちなみにExcel2003なので、最近のことというわけでもなく・・・。。
04Mar
- セルの数字をカンマ付きの千単位表示にするには？
  １．セルの書式設定（Ctrl+1）でセルの書式設定を開く２．「ユーザー定義」を選択する３．"種類"の下のテキストボックスに以下のように入力する　　　#,##0, ;[赤]-#,##0,0のうしろにカンマ（,）をつければOK。ちなみに[赤]で赤字、[青]なら青字、[緑]なら緑字になる。ほんと、ただのメモ。
- 空白セルに直上セルの値を入れる
  空白セルに直上セルの値を入れたいときに、手入力、手でコピペをしていたら時間がいくらあっても足りませんね。。ということで、使い捨てのVBAです。一応2008-03-04 10:04:17とあわせて使ってます。１．[ Alt ]押しながら[ F11 ]を押す（とVB Editorが起動する）２．下のSub aaa()　から　End Subまでをコピペ（数字は変更する）３．[ F5 ]を押す（と実行される）４．コピペしたSub aaa()　から　End Subまでを削除※VBAで処理をした時はUndoがきかないので注意※プログラム実行後にコピペしたコードを削除しないで保存するとExcel起動時にマクロ確認画面がでてしまう。Sub aaa()　For i = 2 To 709　　If Cells(i, 9) = "" Then　　　　Cells(i, 9) = Cells(i - 1, 9)　　End If　NextEnd SubFor i = 2 To 709　'　2行目～709行目（縦方向）に移動Cells(i, 9)は、Cells(行(縦方向)，列(横方向))の意味なので、Cells(2, 9)は I2 セルを指してます。なので、　　If Cells(i, 9) = "" Then　　　Cells(i, 9) = Cells(i - 1, 9)の　9　は対象列（A,B,C,D,･･･）を数えて指定します。A=1、B=2、C=3、･･･I=9という感じ。頻繁に利用するのであれば、セルの指定と最終行の検索ができるようにしてツールバーにボタンを付ければいい。
- 列を追加する
  膨大なリストに列を1行ずつ挿入したい時は、VBAで一括処理すると楽です。１．[ Alt ]キー押しながら[ F11 ]を押します。（VB Editorが開く）２．下のSub～End Subまでをまるごとコピーします。Sub aaa()　For i = 237 To 2 Step -1　'237は最終行、2は先頭行。　　Rows(i).Insert　NextEnd Sub３．最終行（ここでは237）と先頭行（ここでは2）の数字を適宜変更します。４．Sub aaa　と　End Sub　の間をクリックした後に[ F5 ]キーを押します。５．使った後にコードを削除します。　　（削除しないとExcel起動時にマクロ許可画面がでてしまう）最終行を自動判別する関数をつくってもいいのですが、たったの3行なので使い捨てのが楽。行を追加したい時はRows(i).InsertをColumns(i).InsertにすればOKです。