「重箱の中へ。（エクセル関数あつかい）」

　重箱の中へ。（エクセル関数あつかい）

このぺーじにあるものども。
◆VLOOKUPで…データあるのになんで#N/A??
　（リストにあるデータを確実にピックアップするには）

◆SUM&INDIRECTで…串刺し計算式がつくれない??
　（串刺し計算式を手軽に大量生産！）

◆速報・昭和は換算しなくてＯＫ！

◆いろいろな係のヒトを一気に抽出
　（さがしたい条件が多いときのちょっとラクな方法）

◆FINDで…“ない”が“ある”??
（すみません、LEFTではありませんでした。勘違いでした…）

◆EXACTで…#N/Aどうしは同じじゃない??
　～判定前にはエラー対策～

　→→→トップページへお戻りの際はブラウザバックでお願いしますm(__)m

◆VLOOKUPで…データあるのになんで#N/A??
　（リストにあるデータを確実にピックアップするには）

●解決法のレジメ：書式をリセットするべし。

この関数のあばうとな日本語訳。
VLOOKUP(検索値,検索範囲,行数,検索タイプ)
これはつまり
データだしてください
(このブツのデータがほしいんですORブツの名はここに書いてある。データをくれ,
データのってるリストはここだよ（ブツの名前はお約束どおり、一番左の列です）,
リストの左から○番目の列から、指定のブツのデータ抜書きしてほしいの☆,
探しかたはアバウトでいいよんORそのものずばりのデータじゃなかったらいらないからね)
…てなカンジなり。

つまりVLOOKUP関数は、リストから必要なデータを抜粋してくれる関数。
たとえば、出席番号と、中間テストの点数が入った表から、指定した出席番号のヒトの点数だけ抜き出したいときに使ったりする。

しかし最初に陥るヤな事態が、
あるはずのデータがひっとしてくれない!!!!

…つまりこんなんです。
若き日のるきあの（←言い過ぎ）再現ドラマでどうぞ。

「『ブツの名入れるセル』は指定した。リストの範囲も指定した。○番目の列ってのもちゃんと書いた。検索タイプも指定した。
さて、試しに出席番号を『ブツの名入れるセル』で指定したとこに入れてみよう、出席番号1ならあるから1っと…
えー、出てこないっ！　#N/Aってナニ?!
ブツの名入れるセルはちゃんとA1指定したし。リストの範囲もぴったりだし…。○番目の列ってのもちゃんとあるしデータ入ってるよ。検索タイプはこれでいいし。
出席番号1ってのリストにあるよ～？　せるごとこぴって検索かけたら（※）ヒットするし!! どーしてエラーなの？　データがないワケないのに～（泣）」

※…そのため、よくいわれる「末尾にスペースはいっている」とか、「スペルが間違っている」とかの原因ではないのです…。
この関数を断念する人は、ほとんどここで断念するはずです。
はい、るきあも一旦あきらめました（爆）。

しかしそのままというのもそこはかとなくムカつきました（←怖。）ため、いろいろ試してみたところ、解決法が見付かりました！

ずばり、書式をリセットするんです!!

上の例で行くと、方法はこうです。
・まず、リストから出席番号たち（←「ブツの名前」こと「キーデータ」）を一時避難させます。（点数とか、ほかのとこはそのまんまでＯＫ！）
　列ごとコピーして、内容をメモ帳（※…か、ワードパッド限定。詳しい理由は後述）にはりつけときます。
・この状態で、リストの「出席番号のはいってる列」を一旦オールクリアします。
　列を選択した状態で、『編集－クリア－すべて』をすると一列綺麗にオールクリアされます。セルに入ってる出席番号ともども、書式がクリアされてまっしろになります。
・ここに、テキストエディタから、避難していた出席番号たちを連れもどします
　テキストエディタで全部選択しコピー→もといた列にペースト。

これで書式のリセットは終わりです。
式（ともち、ブツの名前）が正しい場合、これででてくるはずです。

もしもそれでもだめなようなら、こんどは同じようにして、『ブツの名入れるセル』も書式をリセットしちゃいます。

ここまですれば（式＆ブツの名前が正しい場合）ぜったいヒットします。
ぜひぜひお試し下さい！

☆応用　特殊な出席番号の場合
0で始まる出席番号の場合、これだけだと連れ戻し後、アタマの0が落とされて困った事態になります。
あまつさえ、数字によっては勝手に日付にされたりもします。
これを防ぐには「オールクリア」の直後に、出席番号入れる列に「書式－セルの書式設定－文字列」をします。
それから出席番号たちを連れ戻します。
なおこの対応をした場合、同じ書式設定なおし（オールクリアから。）を『ブツの名入れるセル』にも行う必要があります。お忘れなく！

●推測される、この事態がおきる原因
どうやらエクセル関数VLOOKUPは、「書式がちがう。だからこれは違うデータ！」とみなしてしまうちょっと困った判断基準が内蔵されてるようです。
厳しい…。

●解決法確立のいきさつ。
検索かけてもヒットするデータどうしの何が違うのか。智恵熱だして（←日本語としては間違ってるそうです…（汗））考えたところ『書式しかない』との結論に達しました。
じゃあキーデータいれてるところの書式をかっぱいで（←超カオス的表現ですみません）しまおうと思い、色々試しました。
データが入ったまま書式をクリアしようとしても上手く行かない。データによってはとんでもないもんに変換されてしまう（アタマの0がなくなったり、日付になっちゃったり…）。
『ならば避難させてしまおう。』とりあえずエクセルの別のシートに避難。
すると、クリアは（もちろん）綺麗にできました。
しかし、出席番号を連れ戻したら…
クリアしたはずの書式が、そのとたん元に戻りました（←そして、ひきつづき点数は出てこない）。
だからって「値を張り付け」を使うと、やっぱり0がなくなったりします。
『なんとか出てる文字だけ忠実に、書式もなくしてゲットしたい!!』
ここでうかんだのが、メモ帳です。
メモ帳は、文字に書式を持たせられません。
もしエクセルからなにかコピペすると、セル内容として表示されている文字だけがでてきて、書式も、セル内容を出すのにいれてる式もどっかに消えてしまいます。
が、この場合は、逆にこれが都合がよかったのです。
かくして上の方法が確立されました。

●追記
しかもこの事態、イヤなことに使いはじめの頃に頻発するような。
慣れてくとなくなるような。なんでだろ…。

●あふたーふぉろー
2006.04.09　後日『エクセルの学校』様にて勉強させていただきましたところ、類似の事例と解決法があるのが見つかりましたので（勝手に）ご紹介させていただきます。

…キーデータが数字。正しく入ってるはずなのにヒットしない、という場合…
こちらです …つまり、キーデータの入ってるセルのなかみが「自分的には数字！」の場合には、
1．そこの書式をなおす
2．しかるのち「データ」->「区切り位置」->「完了」
これで「自分には数字にみえて、エクセル的には文字！」だったキーデータは一括、数字に直せて、めでたくヒットするようになる。というわけなのです。
（初めて知りました。感動！）

ただ、キーデータが数字でなくて文字の場合はモンダイのようです。
こちらです …こちらはキーデータの入ってるセルのなかみが「自分的には文字！」の場合の未解決事例です。
入力に間違いもないし…並びだってちゃんと並んでるし…
この場合は、このコラムで紹介の方法で解決できるはずです。
（あまりに過去のものなので、レスはしていないのです。でも気にかかる…）
たしかに荒っぽいけど（その上テキストファイル使うなんて邪道といわれそう…）、その分確実で、なんも考えずにすみます（爆）。

さて、キーデータが「0ではじまる出席番号」とか、特殊なものの場合、新しくデータを追加したときにソゴが起きる可能性があります。
おそらく、この事例はその問題が起きている事例です
こちらです
早い段階でコラム紹介の方法をやっておくと、恐らくこれは予防できるはずです。
この問題が起きる原因は、「新しくキーデータを入れたセルには、『これまであったキーデータ入れセル』に設定されていた書式が設定されていない」ことだと思われます。
これは、キーデータ入れセルのくる一列に、最初っから有無を言わさずオールクリア、書式設定をかけてしまえば、予防できるはずです。
（もちろんそのさいモレのないよう、オートフィルタは外し、非表示セルもぜんぶ表示させておく必要はあります）

●でもでも書式かえたくないんですけど…の場合。（実例。）
さて書式をリセットするこの方法ですと、
「『ブツの名を出すセル』のあるシートが納品書、『ブツのデータのってるリスト』は納品物内訳書なので、すでにプリントアウト用の書式がついちゃってるよ…」
という場合、困ります。
書式だけ、過去のぶんからコピペとか、書式リセット前のファイル取っといてコピペという手段もあるけど、正直面倒です。
こんなときは、バックグラウンドで処理するようにすればＯＫです。

つまり
・『内訳書』からのデータをコピペする用のシート（データしーと）を準備。データが入っていたら消し、各セルの書式はあらかじめ、整えておく。
・準備済みのデータしーとに、納品物内訳書から『コピー→形式を選択して貼り付け－値』で、データをコピペ
・納品書に書き込みたいデータをとりまとめるシート（ぷれ納品書しーと）を用意。そこでVLOOKUPによるデータ汲み出しを行っておく。
・納品書シートには、ぷれ納品書しーとに出てきた数字だけを、=やリンク貼り付けなんかでもって来る
（→で…納品書シートは、こうして数字ださせた奴をプリントアウト。いっぽう納品内訳書は、もらったままの、もとのをプリントアウト。）
　…このようにすれば、データも毎回楽に確実に出せるし、提出書類の書式もそのまんまにしておけるのです。
データ処理部分とデータ表示部分はわけとくのがキホンのようです。

◆SUM&INDIRECTで…串刺し計算式がつくれない??
　（串刺し計算式を手軽に大量生産！）

●解決法のレジメ：文字列として式を構築→テキストとして再貼り付け。

INDIRECTは、セル参照を文字列で作れます。
とても便利なので、ある日SUMと組み合わせて串刺し計算式を作ろうとしました。
すると、計算結果がエラーになります。
原因はINDIRECT。どうもINDIRECTは、串刺し計算式のカタチの参照は返せないらしいのです。
このとき、集計項目が500くらい（ホントに）あったので、串刺し計算のところをすべて直接入力するのは不可能でした（るきあのＭＰが足りないから（爆））。

ここでひらめいたのが、式を（式としてでなく、一旦純粋な）文字列で作り、文字列をエクセルにはりつけなおすという方法でした。

まず、そのシートの端っこを使って、すべての串刺し計算式を「文字列として」作りました。
こんなカンジです…

　　　　　　　　　J　　　　　　　　　　　　　　　　　　K
１　="SUM(しーと1:しーと10!A"&K1&")"　　　11
２　="SUM(しーと1:しーと10!A"&K2&")"　　　18
３　="SUM(しーと1:しーと10!A"&K3&")"　　　21
４　="SUM(しーと1:しーと10!A"&K4&")"　　　25

…そしてこれらすべてをコピー。メモ帳に貼り付けます。
するとメモ帳には

=SUM(しーと1:しーと10!A11)
=SUM(しーと1:しーと10!A18)
=SUM(しーと1:しーと10!A21)
=SUM(しーと1:しーと10!A25)

（実際やるときはもっと対象が多いため、こういうのがずらーっと何行も並ぶことになり壮観です。）
これをすべて選択、コピーして、
串刺し計算式を本来入れたかったところにペースト。
（注：そこの書式はあらかじめ「標準」にしておくこと。すくなくとも「文字列」だけはダメです。）
すると、なんと文字列としてコピペたはずの串刺し計算式が、ちゃんと式として稼動します！
串刺し計算式がいっぱいあっても、これでＯＫです！

もし、文字列のままで式が稼動しない場合は
・そこの書式を「標準」になおして、メモ帳からのコピペをやり直す
・シート再計算をする（←意外と盲点）
これを試してみてください。

●推測される、これができるメカニズム
エクセルには『文字列がセルに入力された場合、その内容を独自に評価し、内容を適切なものに書き換える（※）』機能があるためだと思われます。
（※ただしそのセルの書式が、文字列でない場合）

たとえば…
『数字をセルに入力していたらいきなり日付に直された』（←よくあるイヤなパターン）
『メールの文面に記載されている数字をそのまんまエクセルシートにコピペしたら、勝手に日付にかえられてしまった』（←前略ぱたーん２）
これらはよくあるぱたーんですが、これと同じように、
『コピペされてきたテキストが関数のカタチをしていたら、エクセルはそれを関数として認識して、書き直す』のだと思われます。

◆速報・昭和は換算しなくてＯＫ！

実例
まずセルの書式を日付－1989/12/23（←数字はうろ覚えです。つまり年が西暦4ケタ表示）にする。
そのセルに昭和年号の日付をS48/3/1（←アタマがSなのがミソ。）と入力。
すると、セル内容は勝手に1973/3/1に直される。

まさかSつけただけで直してくれるとは!!　あとで計算しようと思って、メモのつもりで入れたのに…大感動でした。
日付系の自動直しがおこって、こんなに嬉しかったのは史上初めてです。
それまではわざわざ計算式とかで換算してましたが、昭和についてはもうそれがいらないと思うと何だかすごく嬉しかったのと…
いままで書籍で見たことがなかったためので速報致しました。
（すでにどこかのサイト様にのってそうですが…）

◆いろいろな係のヒトを一気に抽出
　（さがしたい条件が多いときのちょっとラクな方法）

「えっと、今日の放課後説明聞いてもらう人たちは、飼育委員と図書委員と放送委員と美化委員と体育委員か。
　全委員名簿から抽出するには、IF文入れ子に…するとややこしいよな…OR使うのも…なんかめんどいな…」

こういうときは、こんなかんじの数式をどうぞ（C列）。
　　　　A　　　　　　　B　　　　　　　　　　　　　　C
１　石川五右衛門　美化　　=IF(ISERROR(FIND(B1,"飼育図書放送美化体育",1)),"","今日")
２　石川さゆり　　　給食　　=IF(ISERROR(FIND(B2,"飼育図書放送美化体育",1)),"","今日")
３　家野ねこら　　　放送　　=IF(ISERROR(FIND(B3,"飼育図書放送美化体育",1)),"","今日")

計算を実行するとこんなんなります
　　　　A　　　　　　　B　　　　　　　　C
１　石川五右衛門　美化　　　　　今日
２　石川さゆり　　　給食
３　家野ねこら　　　放送　　　　　今日

たとえソートしても結局、対象の委員の出現場所はバラバラだし、オートフィルタ使ってると“けんしょうえん”の足音が聞こてくる（←書いといてなんだがヤな表現）ので、これは便利です。

ただし、この名簿のB列にもしも何も書いてないのがあると…?!
FINDの解説も含めて、詳しくは次のコラムをご覧ください。

◆FINDで…“ない”が“ある”??

FIND関数のおおざっぱな日本語役。
何文字目にあるかさがしてください(
探してほしいのはこいつなんですけど,
この文字列のなかからみつけてほしいんです,
ちなみに捜索は文字列の○文字目以降からってことでたのんます)

この場合もしも“こいつ”が空白（“”であらわされるところのもの）だと、FINDは1をかえしてきます。
“この文字列”に空白（“”であらわされるところのもの）を含ませていなくてもです。
上の例で行きますと…

●実例。
「え？　このリスト間違ってる？　家野さん委員やってないって?!　しかたないな修正しよう…」
　　　　A　　　　　　　B　　　　　　　　　　　　　　C
３　家野ねこら　　　　　　　　=IF(ISERROR(FIND(B3,"飼育図書放送美化体育",1)),"","今日")
（とりあえずB3の内容をデリートキーで消した。）
「これで空白が出てくるよね」

しかし結果はこうなります。
　　　　A　　　　　　　B　　　　　　　　　　　C
３　家野ねこら　　　　　　　　　　　　　今日

「…は??」

B3には何も入っていないはずなのに、ヒットするとははて面妖ナリ（爆）。
"飼育図書放送美化体育"のどこに""が潜んでいるのか??
FINDの結果を見ると、1とでてます。
どうやら、""をFINDで探した（してしまった）場合には、かならず1が帰ってくるようになっているようです。

これを防ぐには、=IF(B3="","",IF(ISERROR(FIND(B3,"飼育図書放送美化体育",1)),"","今日"))のようにするか…
最初から、B列にソートをかけて、委員が空白のヒトたちをどけとくしかないようです。

●理由の考察
…じつは見当つきませんでした。多分仕様なんだと思います（爆）

◆EXACTで…#N/Aどうしは同じじゃない??
　～判定前にはエラー対策～

●実例。
たとえば、こんな場合です。

とあるガッコにおいて、本校・支部校同時に、ある資格試験をおこなうことになりました。
よって、対象者リストをチェックすることになりました。
各生徒の保有資格リストを管理している部と、全校の生徒リストを管理しているセクションは別ですが（…ちょっぴしあめりかちっく??）、もちろん漏れや余剰があってはまずいので、ダブルでチェック＆修正することにしました。

・資格者リストにのってるひとが、全校生徒リストにのっているか？
・全校生徒リストにのってるひとが、資格者リストにのっているか？

これには、おなじみVLOOKUP関数を使います。

　　　　A　　　　　　　　　　　　　　　　　　　　　　B
１　虎縞　三郎　　　=VLOOKUP(A1,もうひとつのりすとのなまえ入ってる列,1,0)

両方のリストで、名前のはいってるとこのとなりなどに列を追加したりして、こうしたカンジの式を入れます。
“もうひとつのリスト”の、名前の入ってる列“だけ”を対象範囲にして、さがす列はもちろん1列め、としているのです。こうすると、対象さえあれば検索できるので楽です（ちなみにコレで、「ある」場合には名前が表示されます）。
両方のリストでコレをやって、#N/Aがなければふたつは同じメンバーが乗っているのでＯＫということになります。
（もちろんそういうウレシイことは極めてレアですので、普通は#N/Aなくなるように、ふたつのリストにのってる名前を増やしたり減らしたり…もちろんエビデンスを確認しながらですが…修正します。）

このとき、ちょっとモンダイが発生しました。
資格者リストは、一枚のシートに資格を所有する生徒の名前が全部のっていたのですが…
全校生徒リストは、本校と支部校でシートがわかれていたのです。
このとき、複数のシートをデータをひとつのシート上にコピペで集め、検索用シートをつくって検索するのも方法ですが、面倒だし危険でもあります（メンバー多いほど貼り付け間違いがこわいです。ここがほんとに終わりでいいのかと…２回貼り付けちゃいないかと…）。

なので、こうしました。
（資格者リストです）
　　　　A　　　　　　　　　　　　　　　　　　　　　　B　　　　　　　　　　　　　　　　　　C
１　鯖虎　三毛　　　=VLOOKUP(A1,本校りすとのなまえ入ってる列,1,0)　=VLOOKUP(A1,支部校りすとのなまえ入ってる列,1,0)

もし、B列、C列ともに#N/Aが出れば、そのヒトは全体生徒リストの本校シートにも、支部校シートにものってない＝全体生徒リストに載ってない（＝ヤバいっていうか要チェック）ということになります。

「そーか、同じかどーかがモンダイか。じゃここは、EXACTを使って集計しよっと」と思いつきました。

　　　　A　　　　　　　B　　　C　　　　　D
１　虎縞　三郎　　　　　　#N/A　=EXACT(B1,C1)
２　鯖虎　三毛　　#N/A　　　　　=EXACT(B2,C2)
３　隣野　黒斑　　#N/A　　#N/A　=EXACT(B3,C3)

つまり、D列でTRUEが出たものが要チェック。

結果の予想はこんなんです。
　　　　A　　　　　　　B　　　C　　　　　D
１　虎縞　三郎　　　　　　#N/A　FALSE
２　鯖虎　三毛　　#N/A　　　　　FALSE
３　隣野　黒斑　　#N/A　　#N/A　TRUE

ところがどっこい、実際の結果は。
　　　　A　　　　　　　B　　　C　　　　　D
１　虎縞　三郎　　　　　　#N/A　#N/A
２　鯖虎　三毛　　#N/A　　　　　#N/A
３　隣野　黒斑　　#N/A　　#N/A　#N/A

…どもならんです（泣）。

…この場合、むしろEXACTよりはISERRORを使ったほうが正解でした。
すなわち
　　　　A　　　　　　　B　　　C　　　　　D
１　虎縞　三郎　　　　　　#N/A　=IF(AND(ISERROR(B1),ISERROR(C1)),"要ちぇっく","ok!")
（以下略）

こうすると
　　　　A　　　　　　　B　　　C　　　　　D
１　虎縞　三郎　　　　　　#N/A　ok!
２　鯖虎　三毛　　#N/A　　　　　ok!
３　隣野　黒斑　　#N/A　　#N/A　要チェック

とみごとに（やっとこさ？）求めていたような結果が出るわけです。
あとはオートフィルタをつかってD列が要チェックのものだけ表示させ、ひとつずつチェックしていくことになります…

●理由の考察
これも、仕様のようです。
EXACTなのになんで～エラー同士ならいっしょじゃん～とか思いますが…。
EXACTに限らず、ANDやORなどでも、判定したい対象にエラーが入ると、なにはともあれエラーしか返ってきません。
検索などで調達したデータにエラーが出ることが予想され、なおかつそのデータでなにか判定をする場合には、まずISERRORなどでエラー対策をすることが必要のようです。
正直、この仕様のせいでたいてい式が2.5倍の長さになるので、ヤなカンジです。これのためにだけ、IFの入れ子を一階層増やさなければなりませんし…
製作者の意図が、この仕様に関してはマジで謎というか、疑問です。
そういうものなのかもしれませんが…。

　→→→トップページへお戻りの際はブラウザバックでお願いしますm(__)m 　　

[PR] ｜自動車保険｜監視ｶﾒﾗ｜消費者金融｜スピリチュアル｜八王子｜調布｜三郷｜久喜｜中国｜ SEO対策｜消費者金融｜車買取｜テンプレート｜沖縄旅行｜免許合宿｜二輪｜引越し｜プレゼント｜ゴルフ会員権｜留学｜レーシック｜マッサージ｜ FX｜アフィリエイト｜ FX｜ホームページ制作｜デイトレード｜ハワイ旅行｜タイ｜バンコク｜ハワイ　レンタカー｜ベストハワイホテルレーツ｜バリ島｜ Hawaii hotels｜ Hawaii Activities｜ bhhr｜ハワイホテル｜テキスト広告｜

【運営会社「パラダイムシフト」サービス】ハワイ現地オプショナルツアー（リラックマ） - ビジネスクラス航空券 - 格安航空券(1) - 格安航空券(2) - 海外ホテル - 韓国旅行 - タイムシェア - ホテル予約
無料ホームページ - 携帯ホームページ - 無料ホームページ作成 - レンタルサーバー - ブログ - ヴィラ - ハワイ　コンドミニアム - バリ島　ホテル - プーケット　ホテル - レピュテーション・マネジメント・ツール - ハワイ　ブログ - Timesell - 国際通話 - ホノルルマラソン - サイト監視 - 風評被害 - ホテル比較 - Delta - ホテル予約