エクセルで＝VLLOKUP関数の入ったセルの数を数えたい。

お世話になります。

少し変わった条件なのか、NETで調べてもズバリの回答が出てこないので質問させてもらいます。
エクセルで特定の列に入った＝VLOOKUP関数が入っているかどうかを知りたいのですが。
表の行には文字や数値が入っており、特定の（例えばH）列は空白かVLＯOUP関数入っています。

実際にやりたいことはA列に各行に入っているデータの数をカウントしたいのですが、VLLOKUP関数の入ったセルはカウント対象外にしたいのです。
つまり、VLOOKUP関数の入った行は空白以外のセルの数から「ー１」したいということです。

No.7 回答者： chonami 回答日時： 2022/03/18 13:34

普通はVLOOKUPの返り値が空白になるのはあり得ないのですが空白になっているなら、IFERRORなどで分岐しているか、後ろに＆””をつけているかもしくは0が返っているのを空白に見せているかです。

できればきちんとVLOOKUPの式も提示しておいてもらえると回答側も推測がつけやすくなります。（なのでなぜVLOOKUP関数を外したのか書いてほしいとも書きました）

本当の空白セルだけを数えたいなら

=ROWS(カウント範囲の列)-COUNTA(カウント範囲の列)

とかでできませんか？

まぁ、まるっと無視されていた身なのでこれ以上は言及しませんが。

この回答へのお礼

大変、大変失礼いたしました。
ウッカリ＃No4さんへの回答で記載したのをchonamiさんへの説明済みとおもってしました。

H列の式は、=VLOOKUP(D100,JOBﾘｽﾄ!D:E,2,FALSE)
でD列が空白なら見た目空白で、D列に入力した文字列がﾘｽﾄで引きあたれば文字列が入ります。
問題は別のシートから行のコピぺで入力された場合H列が空白で上書きされます。

やりたいことは、各行のデータ数（式のままのセルはカウントせずに文字列のセルのみ）をカウントしたいのです。

下記が詳細です。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
問題の列はH列で、ここにVLOOKUP関数が入っていて、この列の表記はA列の文字列からVLOOKUPで引き当てた文字列（顧客名）、か空白なのですが、空白に見えるセルにVLOOKUPで引きあたらなかった場合の空白と本当の空白があります。

この空白に見えるVLOOKUP関数の入ったセルを行のデータ数から外したい。
A列をリストから選択するとG列に顧客名が自動で入るのですが、別のシートから行でC&PするとG列が空白になってしまいます。
行のコピペが入力が必要なAZまでの列に一括で入力され便利なのでこれは禁止したくないのでこのような表になっています。

お礼日時：2022/03/18 14:11

No.6 ベストアンサー 回答者： fujillin 回答日時： 2022/03/17 13:35

No4です

補足を読みましたが、さっぱり要領を得ません。
こちらでは、質問者様のご覧になっている画面はわかりませんし、他のシートもわかりません。
質問者様が当然と思っていることでも、文章に記載のないことは知りません。
No2にも記しましたが、きちんと整理できさえすれば「条件が変わっても要領は同じ」でできると思います。

＞この空白に見えるVLOOKUP関数の入ったセルを行のデータ数から外したい。
よくわかりませんけれど、上記の「関数の結果が空白になっているセル」をカウントできればよいのなら、
　=SUMPRODUCT(ISFORMULA(セル範囲)*(セル範囲=""))
で可能と思います。
（関数の結果がエラー値になる場合を含む可能性があるのなら、No4の方法でエラーを回避する必要があります）

この回答へのお礼

何度もすみませんでした。

読み返してみると確かに思い込みが強く分かりにくいので、キチンと整理してみました。

最初の当方の思いは、空白セルでないセルの数から関数の入ったセルを引けば入力データ数になるハズでした。
しかし、空白でないセルにはVLOOKUPで引き当てのない空白のセルはカウントされず、該当文字列があったセルはカウントされます。

つまりこの引き当てのあったセルを含む行から関数セルを引くとデータ数がー１になってしまいます。

全くご回答のアドバイスの通りで、教えて頂いた式と組み合わせて、範囲内の列数から空白セルを引いて、更に関数を引いた数字が列数であればデータ数０と判断すれば良いことに気付きました。

本当に見放さずにありがとうございました。

お礼日時：2022/03/17 15:21

No.5 回答者： mike32 回答日時： 2022/03/16 23:02

セルに含まれている式を取得するには、FORMULATEXT()を使用します。

取得した式にVLOOKUPが含まれているかは、COUNTIF()を使用することで確認できます。

例）
セルH1にVLOOKUPが含まれている場合、-1。
含まれていない場合、0にするとしたら、下記のようになります。

=IF(COUNTIF(FORMULATEXT(H1),"*VLOOKUP*"),-1,0)

参考サイト：
セルに入力されている数式を取得する方法について
https://lilia-study.com/excel/kansuu/kensaku/for …
含まれているかどうかを確認する方法について
https://www.crie.co.jp/chokotech/detail/383/

この回答へのお礼

ご回答ありがとうございました。

当方も最初に”VLOOKUP”という文字列をカウントできないかと思っていたので、まさにご回答の式かと早々に確認してみたのですが、なぜかエラー（よく見る()の数が合わない等で表示されるエラー）で入力を拒否されてしまいます。
ご回答をコピペして行数をＨ5に変えただけなので何が問題かわかりません。

それより重要な見落としに気付きました。
下記#No4さんに詳細を書いたのですが、要するにH列にはVLOOKUP関数で引き当てた文字列と関数が入って空白に見えるセルと、本当の空白があり、この文字列セルはカウントし、空白に見える関数は対象外にしたい、が目的でした。

本当に申し訳なく、お詫びします。

お礼日時：2022/03/17 13:13

No.4 回答者： fujillin 回答日時： 2022/03/16 17:33

No2です。

＞=SUMPRODUCT(NOT(ISFORMULA(C5:AI5))*(C5:AI5<>""))
＞としてみたのですが＃N/Aとなってしまいます。
まさか、エクセルのバージョンが2011以前ってことはないだろうと思いますので・・

セルC5:AI5の中に＃N/Aの値（＝エラー値）が含まれていませんか？
その場合は「(C5:AI5<>"")」の判定でエラーになります。
単純に、「式の入っているセル数」がカウントできれば良いのであれば・・
　=SUMPRODUCT(ISFORMULA(C5:AI5)*1)
を用いて頂ければ宜しいでしょう。


『セル範囲の中で、空白以外で、かつ、関数の入っていないセルの数』場合で、エラー値（多分、関数の結果だと思いますが）が含まれている場合には、そのセルはカウントに入れなくてもよいのなら、
　=SUM(NOT(ISFORMULA(C5:AI5))*IFERROR(C5:AI5<>"",0))
を入力して、Ctrl + Shift + Enter で確定してください。（←必須です）
（条件分岐は配列計算をしてくれないので、配列数式にする必要があります）

※ VLOOKUP関数の側でエラー処理をしておけば、配列数式にする必要はなくなるものと思います。
※ そのままでも、配列数式にしない方法もないことはないですが、式が無駄に長くなるのでやめておきます。

この回答へのお礼

何度もの早々のご回答ありがとうございます。
エクセルのVer.は2016です。

ご回答を検証してみて式のみが検出されることを確認し、
=COUNTA(E5:AZ5)-(SUMPRODUCT(ISFORMULA(E5:AZ5)*1))で
うまく行ったと思ったのですが、実シートで確認してみた結果、誠に、誠に申し訳ないことに質問が違っている（大間抜けな）ことに気付きました。

問題の列はH列で、ここにVLOOKUP関数が入っていて、この列の表記はA列の文字列からVLOOKUPで引き当てた文字列（顧客名）、か空白なのですが、空白に見えるセルにVLOOKUPで引きあたらなかった場合の空白と本当の空白があります。

この空白に見えるVLOOKUP関数の入ったセルを行のデータ数から外したい。
A列をリストから選択するとG列に顧客名が自動で入るのですが、別のシートから行でC&PするとG列が空白になってしまいます。
行のコピペが入力が必要なAZまでの列に一括で入力され便利なのでこれは禁止したくないのでこのような表になっています。

顧客名が入っている場合に「手入力の文字列」と空白に見える「関数で引き当てた文字列」と「本当の空白セル」があるということに気付きませんでした。

関数で引き当てた文字列のセルがVLLOKUPになていることに気付きませんでした。

本当に申し訳なく、このレベルなのでご容赦くださいませ。。。。

お礼日時：2022/03/17 13:01

No.3 回答者： chonami 回答日時： 2022/03/16 12:41

Ctrl＋Shift＋＠を押せば、数式が入っているセルは数式が表示されますがそういうことではないんですよね？

VLOOKUP関数のセルをカウントしたくない理由も書いていただくと別視点からの回答もつくかも知れません。

No.2 回答者： fujillin 回答日時： 2022/03/16 11:29

こんにちは

関数の種類をVLOOKUP関数に限定するとVBAでないと難しそうですが、

『セル範囲の中で、空白以外で、かつ、関数の入っていないセルの数』
を求めたいというのであれば、
　=SUMPRODUCT(NOT(ISFORMULA(セル範囲))*(セル範囲<>""))
で求められると思います。

『セル範囲の中で、数値で、かつ、関数の入っていないセルの数』
であるならば、
　=SUMPRODUCT(NOT(ISFORMULA(セル範囲))*ISNUMBER(セル範囲))
といった感じで。

※ 式中の「セル範囲」には同じ範囲を指定しておく必要があります。

上記以外の条件でも、同じ要領で算出可能と思います。

この回答へのお礼

早々のご回答ありがとうございます。

＞『セル範囲の中で、空白以外で、かつ、関数の入っていないセルの数』
を求めたい・・・
のでご回答の式をコピペして
=SUMPRODUCT(NOT(ISFORMULA(C5:AI5))*(C5:AI5<>""))
としてみたのですが＃N/Aとなってしまいます。

5行目に入っているのは複数の、文字列、●、空白と、1つのVLOOKUP関数です。このVLOOKUP関数が入っている場合と空白になってしまう場合があります。
日々の表への追加入力でこのような状態になってしまいます。

何か条件抜けているのでしょうか？

お礼日時：2022/03/16 16:43

No.1 回答者： めくりや 回答日時： 2022/03/16 10:15

COUNTIF関数で、できなきゃマクロ組むしかない。