[[20121020133512]] 『最終的にはVBAが欲しいがIFも勉強したい』(MONY) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『最終的にはVBAが欲しいがIFも勉強したい』(MONY)

いつもありがとうございます。
今回はIFについて(違うかも)教えて下さい。

ここに登場してくる方々ならVBAで楽々解決だとは思うのですが、それだとあまり勉強にならないので関数を教えて下さい。

今回は統計表についての質問です。

 A.......FGH....
1
2
3 タイトル行
4 A
:
10
11 B
:
13
14 C
:
17
18 タイトル行
19 D
:
82

↑このようなシートがあり、AからDの各項目の該当するセルに「1」と入力することになっています。入力はF列からです。

このシートにはチェックの欄があり、一列の合計が4になれば○という関数が入っています。
F1 =IF(F2=0,"",IF(F2<4,"−",IF(F2=4,"○","×")))
F2 =SUM(F4:F17)+SUM(F19:F82)

これですと、
A2 B0 C1 D1でも○になってしまうので
A1 B1 C1 D1=○にしたいのです。

自分で直すのにF2に関数を入れるのは止めてF1に
=IF($F$4:$F$10=1,$F$11:$F$13=1,$F$14:$F$17=1,$F$19:$F$82=1,"○","×")
と入れたら「多すぎる引数が入力されています」とエラーになってしまいました。

今度はSUMIFでF1に
=SUMIF($F$4:$F$10,"1",$F$11:$F$13,"1",$F$14$F$17,"1",$F$19:$F$82,"1",="○","×")
と入れても同じエラーでした。

多分、根本的な式の入力が違うのだと思うので、ヒントをください。
こういう場合に使うのはIFでもSUMIFでもないのでしょうか?

(MONY)


 F4:F10の中に「1」が一つだけあるか?
 F11:F13の中に「1」が一つだけあるか?
 F14:F17の中に「1」が一つだけあるか?
 F19:F82の中に「1」が一つだけあるか?

 4つの条件がすべて TRUE だった時「○」を表示するのですよね?

 一番最初の F4:F10の中に「1」が一つだけあるか?
 の確認は、SUM(F4:F10)=1 で良いと思います。
  F4:F10の中に 0.5が二つ入っている可能性 と言ったものを考えなくて良いのなら。。。

 いくつかの条件がすべて TRUE かどうかを確認するには
 AND関数が使えます。

 他にも方法はあると思いますが
 IF,SUM,AND 関数を使って実現する場合のヒントです。

 (HANA)

HANAさん

早速ありがとうございました!
まずはAND関数を見てみます。

>4つの条件がすべて TRUE だった時「○」を表示するのですよね?
その通りです。

また、0.5が入る可能性はないので大丈夫です。

(MONY)


【1】
カップ式の自動販売機でコーヒー、ミルク、砂糖の量を調節できるものを見たことがありませんか?

好みのコーヒー(コーヒーの量,ミルクの量,砂糖の量) 

と三つの要素の組み合わせで出来上がるコーヒーの味が決まります。
上記は「好みのコーヒー」を作るための関数です。
コーヒーの量、ミルクの量、砂糖の量の3要素が調節できます。
これらのカンマで区切られた3要素がエクセルの関数で言う「引数(パラメータ)」です。

IF関数の引数も3要素です。
ですが、例示のものは、カンマで区切られた要素数はそれを超えていますよね?

=IF($F$4:$F$10=1,$F$11:$F$13=1,$F$14:$F$17=1,$F$19:$F$82=1,"○","×")

【2】
「$F$4:$F$10=1,$F$11:$F$13=1,$F$14:$F$17=1,$F$19:$F$82=1」
のぶぶんが「条件判断」のつもりで書かれていると思いますが、
「$F$4:$F$10=1」を「$F$4:$F$10の範囲に1がひとつだけ入力されているならば」
というつもりで書かれているのであれば、これは間違いです。

このセル範囲に何がどのように入力される可能性があるのか、という説明も必要ですが、
「$F$4:$F$10の範囲に数値がひとつだけ入力されている」かつ、
「そのとき$F$4:$F$10の範囲に入力されているのは1がひとつだけである」という状況を
判定するには、
COUNT関数で数値の入力されているセルを確認し、COUNTIF関数でその範囲で1が入力されている
セルの数を確認する、という手順が必要です。

(みやほりん)


HANAさん

過去ログからAND関数を調べて、それらしいものを探して

=IF(AND$F$4:$F$10="1",$F$11:$F$13="1",$F$14:$F$17-"1",$F$19:$F$82="1",)"○","×",)
という式を入れてみましたが、エラーでした。当たり前か…基本が分かっていないので…

その後みやほりんさんのお返事を見ました。

コーヒーの説明、非常に分かりやすく私のレベルにピッタリでした。
引数というのは3要素なのですね、ありがとうございます。

>「$F$4:$F$10=1」を「$F$4:$F$10の範囲に1がひとつだけ入力されているならば」
というつもりで書かれているのであれば、これは間違いです。

ご指摘の通りの認識です。間違いですね。

そうなるとCOUNTIF関数なのですね。AND関数、COUNT関数、COUNTIF関数を調べてみます。

(MONY)


 >一番最初の F4:F10の中に「1」が一つだけあるか?
 >の確認は、SUM(F4:F10)=1 で良いと思います。
 と書きましたが、前提として
  各セルには「1」が入力されるか。入力されない。
 を想定しています。

 範囲をSUMした結果を確認するので 1が複数入力されていた時はもちろん分かりますが
 複数値が入力されていて、たまたま合計が1になった場合はチェック出来ません。
  1より大きい値が入力されていた場合は分かりますが。

 「1以外の値は入力されていないか?そして、入力されているのは1か」の確認が必要であれば、
 みやほりんさんが書いておられる方法に成るでしょう。

 いくつか数式を組み合わせる必要が有る時は
 一つずつ作ってみるのが良いと思います。
 たとえば
 1.F83セルに F4:F10 の中に「1」が一つだけある時 TRUE に成る式
 2.F84セルに F11:F13の中に「1」が一つだけある時 TRUE に成る式
 3.F85セルに F14:F17の中に「1」が一つだけある時 TRUE に成る式
 4.F86セルに F19:F82の中に「1」が一つだけある時 TRUE に成る式
 5.F87セルに F83,F84,F85,F86がすべて TRUE の時、TRUE に成る式
 6.F88セルに F87セルが TRUE の時"○"を表示する式

 (HANA)


 =IF($F$4:$F$10=1,$F$11:$F$13=1,$F$14:$F$17=1,$F$19:$F$82=1,"○","×") 

 $F$4:$F$10=1
 $F$11:$F$13=1
 $F$14:$F$17=1
 $F$19:$F$82=1
 ,で区切っているので、4つの数式が有る事になります。
 よって因数は"○","×"を含めると、6個の因数となります

 $F$4:$F$10=1を考えるに6個のセルに、1が何個あればいいのかが読み取れません
 =sum(($F$4:$F$10=1)*1)  sum関数で何個か
 =countif($F$4:$F$10,1)  1が何個あるか
 ......

 AND$F$4:$F$10="1",$F$11:$F$13="1",$F$14:$F$17-"1",$F$19:$F$82="1",)"○","×",)
 関数は()の中に因数をいれるこれが基本です

 それぞれの関数には意味をもった因数を入れる事になってますよ。
 ただ だらだらといれれば、いいというものではありません、

 (NANAKO)


 こんにちは。

 > 過去ログからAND関数を調べて、それらしいものを探して 
 ということですが、エクセルのヘルプを見た方がいいですよ。
 「調べものはまず原典参照」です。
 ヘルプには、関数の意味も 引数の指定方法も 使用例も 関数に
 よってはエラーになったときの対応方法も書いてあります。

 > 引数というのは3要素なのですね、ありがとうございます。 
 そうですね。if関数の引数は3つですね。
 ほかに有名な関数では、vlookup関数の引数が4つ(4つ目は省略可)。
 today関数の引数は0個(なにも指定しない)です。
 ちなみに、and関数の引数は1つ〜30個の間で好きなだけ指定してOKです。

 −佳−


 数式の引数がわからなくなった時は、=数式(例えば=IF)まで入力してから
 Shift+Ctrl+Aを押すと必要な引数が表示され、確認することができます。
 ただ、引数がいくつまでとか基本的な書き方とかはやっぱりヘルプで勉強された方がいいと思います。

 (コナミ)


まずはHANAさん

ありがとうございます。
>各セルには「1」が入力されるか。入力されない。

 を想定しています。
その通りです。該当する項目に「1」を入れることになっているので「1」以外を入れることはないのですが、まかり間違ってテンキーの1のつもりで4を入れてしまうことはあるかもしれませんので、1より大きな数字が入っていれば分かるのであれば十分だと思います。

欲張りなので、HANAさんとみやほりんさんが教えて下さった、両方の方法で作ってみようと思います。

すみません、もう少し時間をください。

それから、NANAKOさん
>それぞれの関数には意味をもった因数を入れる事になってますよ。

 ただ だらだらといれれば、いいというものではありません
すみません、基本が分かっていないので勉強します。因数の意味すら…

圭さん
> 「調べものはまず原典参照」です
了解しました。ヘルプでしっかり調べます。

それと、因数は3つ以外は使えないと思い込んでいて、3つ以上あったらどうしよう困ったと思っていたところにvlookupやandなら3つ以外の方法もあると聞いて安心しました。

みなさん、ありがとうございました。
頑張ります。

(MONY)


HANAさん

F83に=IF(F4:F10=1,"◎","×")
F84に=IF(F11:F13=1,"◎","×")
F85に=IF(F14:F17=1,"◎","×")
F86に=IF(F83:F85=◎,"○")
と入れたら#VALUE!のエラーが出たので調べて、やり直し、

F83を=IF((SUMF4:F10=1),"◎","×")
に直してみたら#NAME?のエラーが出たので調べてやり直し、

F83を=IF((SUM(F4:F10=1)),"◎","×")
に直してみたらまた#VALUE!のエラーが出たので調べてやり直し。

F83を=AND(F4:F10=1,TRUE)で#VALU!

     =AND(F4:F10<2)で#VALU!

ヘルプを見ても分かりません(泣)HANAさん助けて〜

(MONY)


 えっと。。。SUM関数は

 SUM(F4:F10)の結果が「1」だったら、F4:F10の中に「1」が一つだけだとわかるな。
 と言う考えです。

 SUM関数が使えそうかと思える理由は
  「1」が二つあったら、結果は「2」になりますし
  打ち間違えて「4」が一つ入力されていたら、結果は「4」
 になるからです。

 上記に納得出来たら実際の式↓を考えます。
 >1.F83セルに F4:F10 の中に「1」が一つだけある時 TRUE に成る式

 F83セルには =SUM(F4:F10)=1 の式を入れて下さい。
 F83セルには TRUE とか FALSE とか表示されると思います。

 まずはF4:F10セルの値を色々変更して、目的の数式が出来ていることを確認してください。

 (HANA)


HANAさん

ありがとうございます。
コメントを読み返してF83=SUM(F4:F10=1)でダメ、=SUM(F4:F10)=1と手で入力したらTRUEとやっと出ました。

関数の挿入で、関数を選ぶと小さなウインドウが出てきて、項目が幾つか出てくるやり方で入力すると、いつも上手くいきません。

手で数式を入力したら成功しました。

これでF84〜F86を入力したら成功しました。

F87を=IF(F83:F86=TRUE,"○")としたらエラー、

     =IF(((F83:F86)=TRUE),"TRUE")としたらエラー、もう少しですね。

(MONY)


 おっと、衝突しちゃいましたが まずは書こうと思っていたものから。。。

  追記:
 F4:F10【が】1かどうかを確認するのではなく
 F4:F10の合計【が】1かどうかを確認します。

 SUM(F4:F10=1) の式を無理やり解釈すると
 F4:F10が1かどうかを確認したもの を 合計 しています。
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ここがSUM関数のカッコの中に入っている。

 やりたいことと違ってきてますよね?

 >関数の挿入で、関数を選ぶと小さなウインドウが出てきて、項目が幾つか出てくるやり方で入力すると、いつも上手くいきません。 
 関数のカッコの中に、何を入れないといけないか 考えて下さい。

 今回(SUM関数の引数として)入れないといけなかったのは
  どの範囲を合計しますか?
 と言う問いかけに対する答えです。
 答えは F4:F10 の範囲ですよね?

 だから、SUM関数自体は SUM(F4:F10) が完成です。
 その後、「これが1と等しいか?」を確認するので SUM(・・・)=1 になります。

 4番目までできたら、次は5番目を作って下さい。
 > 5.F87セルに F83,F84,F85,F86がすべて TRUE の時、TRUE に成る式
 この段階では、まだIF関数は使いませんよ?

 前も書きましたが、すべての値がTRUEの時、TRUEを得るには AND関数を使って下さい。
 面倒だとは思いますがまずは一つずつ 段階を追って下さい。

 ちなみに、引数ってのが 数式のカッコの中身の事です。

 (HANA)

HANAさん

できました!
ANDでできました。5行目は=AND(F83:F86)としてできました。
ありがとうございます。

> 関数のカッコの中に、何を入れないといけないか 考えて下さい。
分かりました(汗)すみません。ここが難しいのです…

SUMというと今までオートSUMしか使ったことがなかったので、これでまた一つ応用できるようになりました。

そして、6行目に=IF(F87,"○")と入れたらできました!
試しに4つの項目の1つにしか「1」を入れなかったら、該当するセルだけがTRUEになり、あとはエラーになり機能していることが確認できました。
○のところもエラーになっていたので×を追加して=IF(F87,"○","×")としたら、×も出ました!
ありがとうございます!

これで良しとせず、次はCOUNTIFに挑戦してみます。

(MONY)


 あっっ。。。

 >5行目は=AND(F83:F86)としてできました。 
 そうなんですが =SUM(・・・)=1 を入力したセルがばらばらで
 F83:F86 の様に範囲が指定できない場合を想定してみて下さい。

 2003までの場合、AND関数は引数を30個まで指定できます。
 ヘルプも使用例も確認して下さい。
 例として
 >=AND(TRUE, TRUE)
 こんなのがあると思います。

 そこで、作成した =AND(F83:F86) を同じ様に変更してみて下さい。
 これから、作業セル−F83:F87− を使わない式に変更して行きたいと思います。

 (HANA)

HANAさん

>あっっ。。。
というのを見て、笑ってしまいました。また何か違ったのですね。

7行目に=AND(F83,F84,F85,F86)を入れてやり直してみました。
そして8行目に=IF(F89,"○","×")として、できました!

こういう意味ですよね?(また違いますか?)

それからCOUNTもやってみました。
I列の1行目に=COUNT(I4:I10)
同じように2行目、3行目とやって、5行目に=SUM(I83:I86)=4 6行目に=IF(I87,"○","×")
7行目はANDで=AND(I83,I84,I85,I86)
8行目は=IF(I89,"○","×")
で、できました!

7行目をCOUNTIFにできるよう、頑張ってみます。

(MONY)


 >こういう意味ですよね?(また違いますか?) 
 そうそう、そういう意味です。合ってます。

 F89に =AND(F83,F84,F85,F86) の式が入っています。
 そして、このセルを使って =IF(F89,"○","×") の式を作りました。
 この数式内の、この部分は.../~~~ AND(F83,F84,F85,F86) の事ですよね?

 なので
 =IF(AND(F83,F84,F85,F86),"○","×")
 と書けるのですが、納得行きますか?

 この調子で、F83:F86に入っている数式と置き換えていくと
 作業セルがいらない式が完成します。

 当初 (MONY)さんが作成して、うまくいかなかった式と
 どこが違うか 見比べてみて下さい。

 みやほりさんが書いておられる方法は
 >それからCOUNTもやってみました。 〜〜
 とは少し違うと思います。

 式を作成する前に、どういう仕組みでチェックをしようとしているのか
 もう一度組み立ててみて下さい。

 I4:I10の中に入力されている値は一つである。・・・・・COUNT関数で数えた結果が1−−−−−−−(イ)
 I4:I10の中に入力されている「1」は一つである。・・・・COUNTIF関数で「1」を数えた結果が1−−(ロ)
 両方を満たした時、I4:I10の中に「1」が一つだけ入力されている。−−−−−−−−−−−−−−−(ハ)

 こんな感じです。

 (イ)の式、(ロ)の式を作って(セルに、TRUE,FALSEが表示されるもの)
 二つのセルから AND関数を使った(ハ)の式を作ります。
 この(ハ)のセルと、SUM関数を使ってやった F83のセルが 同じ段階です。

 >I列の1行目に=COUNT(I4:I10)  
 >同じように2行目、3行目とやって、5行目に=SUM(I83:I86)=4
 これだけだと、「1」以外の値が一つずつ入力されていた時も
 ○ になっちゃいいます。(涙)

 (HANA)

HANAさん!!

できました!!
=IF(AND(SUM(F4:F10)=1,SUM(F11:F13)=1,SUM(F14:F17)=1,SUM(F19:F82)=1),"○","×")ですね。
やりました!
ありがとうございます。

当初私がデタラメに作った式には、ANDもSUMも入っていなかったので倫理?が成り立っていなかったのですね。なるほど、よく分かりました。

>いくつか数式を組み合わせる必要が有る時は

 一つずつ作ってみるのが良いと思います。
こうおっしゃった意味が良く分かりました。

HANAさんを涙目にしてしまってスミマセン。COUNTの方は、頭の中を整理して落ち着いてもう一度考えてみます。

(MONY)


 >=IF(AND(SUM(F4:F10)=1,SUM(F11:F13)=1,SUM(F14:F17)=1,SUM(F19:F82)=1),"○","×")ですね。 
 そうです!!

 完成した式を
 >関数の挿入で、関数を選ぶと小さなウインドウが出てきて、項目が幾つか出てくるやり方
 で確認してみて下さい。

 式が入力されているセルをアクティブにして、数式バーの左側にある[=]or[fx]を押します。
 まずは IF 関数
   論理式  AND(SUM(F4:F10)=1,SUM(F11:F13)=1,SUM(F14:F17)=1,SUM(F19:F82)=1)
   真の場合 "○"
   偽の場合 "×" 
 と、きれいに三つの引数に分かれますね?

 論理式の所に入っている数式の結果が TRUE の時 「○」
 FALSE の時「×」が表示されます。

   IF関数は
    論理式の結果(TRUE か FALSE)に応じて、指定された値を返します。
    論理式には、結果が TURE または FALSE になる値、もしくは数式を指定します。

   と言った使い方が書いてある様に
   論理式の所に TRUE か FALSE になる様な数式(等)を指定しないといけないです。
   IF関数を使うお約束なので、従う他はありません。

 この時、数式バーを見ると 全体が太字で表示されていると思います。
 ウィンドウを表示させたまま、数式バーで「AND」のあたりをクリックして
 カーソルを持って行って下さい。

 今度は「AND(SUM(F4:F10)=1,SUM(F11:F13)=1,SUM(F14:F17)=1,SUM(F19:F82)=1)」部分が太字になって
 ウィンドウはAND関数の表示に変わります。
   論理式1 SUM(F4:F10)=1    = TRUE
   論理式2 SUM(F11:F13)=1   = TRUE
   論理式3 SUM(F14:F17)=1   = TRUE
   論理式4 SUM(F19:F82)=1   = TRUE
                              = TRUE
 こんな感じで、きちんと4つの引数に分かれて(IFの時も表示されていると思いますが)
 右側に各引数の結果と、その下に全体としての結果が表示されます。

 数式作成中に「AND関数の結果は、TRUEになるはずなのに、なぜかFALSEになる」なんて事態になったら
 このウィンドウを表示させて、どこの引数がFALSEになっているのか 確認してみると良いと思います。

 さらに、SUMの所をクリックすると
   数値1   F4:F10           ={1;0;0;0;0;0;0}
     数値2                    = 数値
                              =1
 と言った感じで、この部分だけの結果がさらに確認できます。

 今回、各セルに書き出して一つずつ確認しながら作成しましたが
 その後、このウィンドウでどこに何をどの様に指定するのか
 やったことを確認してもらえると
 直接このウィンドウでも数式が作れるようになると思います。
   AND関数のウィンドウの表示なんて、F83:F86 と同じですよね?

 そして、どこに何を指定するのかわかるようになると
 直接入力でも、使用方法に則った数式を完成させる事が出来る様になると思います。

 (HANA)


HANAさん

おはようございます。
HANAさんが懇切丁寧に教えて下さったお蔭で、ウインドウの使い方がやっと分かりました!

IFの中にANDとSUMを入れるなんて、私にしてはもの凄く高度な数式で思いもよりませんでした。
エクセルは数学的ですが、式を入力するときはとても国語的なのですね。
正しい日本語を数字と記号に変えて入力すれば、難しい計算もできるようになるのですね。

昨日は数式が出来たことが嬉しくて、HANAさんは凄い人とずっと感動していました。
ありがとうございました。

さて今日は…

(MONY)


 >エクセルは数学的ですが、式を入力するときはとても国語的なのですね。 
 そうですね。
 なので、エクセルが言ってくることも、文字だけ追ったのではわかりにくいですが
 「何かを教えてくれてるんだ」と思って、読み取ってあげて下さい。

 ヘルプには使用例も書かれていると思いますので、それと照らし合わせながら読んでいくと
 少しは分かりやすくなるのではないかと思います。

 今回【関数は組み合わせて使える】と言う事もわかったと思いますので
 どんどん組み合わせやってみて下さい。

 ただし、関数の中に関数を入れられる数は限りがありますので 注意して下さい。
  2003以前でしたら最大7個。2007,2010では 64個です。

 関数の中に関数が入っているものを「入れ子」とか「ネスト」とか言います。
 今回↓の様な式になっていますね?
 IF(AND(SUM(・・・)=1,SUM(・・・)=1,SUM(・・・)=1,SUM(・・・)=1),"○","×")
 IF関数の中に、AND関数が入っていて、その中にSUM関数が入っています。
 こういうのが ネスト で

 AND関数の中で、SUM関数が4つ使ってありますが
 これは SUM関数4つがネストされている訳ではありません。
 AND関数の中のSUM関数は、引数です。

 途中経過で =IF(I89,"○","×") と言うIF関数を作りましたが
 この I89,"○","×" が「引数」と言うのと同じです。

 ・・・表現が難しいのですが、違いが分かってもらえますか。。。?
 AND(SUM(・・・),SUM(・・・)) は AND関数の中に2つのSUM関数が入っています。
 AND(SUM(SUM(・・・)))      だと AND関数の中にSUM関数が入ってさらにその中にSUM関数が入っています。
 前者の様な式で、SUM関数がどんどん増えていくのは問題ありません。
  AND関数の制約で、増やせる数に限りはありますが。。。
 後者の様な式では、AND関数の制約ではなく ネストできる数の制約を受けます。

 IF関数の引数は、3個あって
 AND関数の引数は、2003までの場合 最大30個 です。
 佳さんが書いておられますが
 VLOOKUP関数の引数は 4つ(4つ目は省略可)で
 TODAY関数の引数は0個(なにも指定しない)です。

 IF関数で IF("○","×",I89) と勝手な順番で書いたのではうまく機能しないのと同様に
 他の関数も、それぞれ決められた書式があります。
 良く使うものは、すぐに覚えてしまうと思いますので心配しなくて良いと思いますよ。
 その代り、不安な内は こまめに確認してもらうのが良いと思います。

 >HANAさんは凄い人とずっと感動していました。 
 いやいや、数式を作ったMONYさんが凄いんですよ。

 さて今日は・・・
 昨日作ったものよりも、数式が長くなると思いますが
 一つずつやっていけば、きちんと出来ると思います。

 先にしっかり、日本語で考えてみて下さいね。

 (HANA)

HANAさん

ネストと引数の違いは何となくわかりました。
ありがとうございます。

また、2010なので64個までいれられるのですね。

今日はずーーーっと考えていてやっと少しできたのですが、数式を書いていたらプレビューが上手くいかず全部消えてしまったので、明日またご報告します。

宜しくお願いします。

(MONY)


HANA先生

おはようございます。今日も宜しくお願いします。

> I4:I10の中に入力されている値は一つである。・・・・・COUNT関数で数えた結果が1−−−−−−−(イ)

 I4:I10の中に入力されている「1」は一つである。・・・・COUNTIF関数で「1」を数えた結果が1−−(ロ)
 両方を満たした時、I4:I10の中に「1」が一つだけ入力されている。−−−−−−−−−−−−−−−(ハ)

 こんな感じです。

 (イ)の式、(ロ)の式を作って(セルに、TRUE,FALSEが表示されるもの)
 二つのセルから AND関数を使った(ハ)の式を作ります。
 この(ハ)のセルと、SUM関数を使ってやった F83のセルが 同じ段階です。

これをよーく読んでここで勉強してきたことと照らして、K列で作ってみました。
K83=COUNT(K4:K10)
K84=COUNTIF(K4:K10,1)=1
K85=AND(K83,K84)

なのでK85=AND(COUNT(K4:K10),COUNTIF(K4:K10,1)=1)

ここまでがこれまでの例のF83のセルと同じ段階、ということは、これと同じようにK11:K13と、K14:K17と、K19:K82を3つ作ると言うことですよね?

そして、次の段階に進んでいくということですよね??
違ってますか?

(MONY)


 >数式を書いていたらプレビューが上手くいかず全部消えてしまったので
 そうなんですよね。私も時々やっちゃいます。

 途中でプレビューをはさんでおくと、ブラウザの戻るボタンで
 戻れる事があるので、良いと思います。

 >K83=COUNT(K4:K10) 
 惜しいっ!!

 これだと、数えているだけですよね。
 作らないといけないのは
 >I4:I10の中に入力されている値は一つである。・・・・・COUNT関数で数えた結果が1−−−−−−−(イ) 
                                 ~~~~                                         ~~
 ここの下線の所(表示がずれてないかな?)の所が忘れられてます。

 たとえば、K4セルに「1」 K5セルに「4」を入力してみて下さい。
 K83が2 , K84が1 , K85は TRUE になります。
 でも、値が二つ入力されているので 本当は FALSE にする予定ですよね?
 だから、こんな時は K83に FALSE と出る様な式にしておかないといけません。

 これはお約束なんですが、0以外の値は TRUE として扱われます。
 一旦 K83 の式を消していろんな値を入れてみて下さい。
 K84セルに TRUE となっている時、
  K83に0を入力してみる・・・・・・・・K85は FALSE
  K83に0以外の値を入力してみる・・・・K85は TRUE
  K83に文字(AAAA等)を入力しれみる・・・K85は TURE
 今回はこのお約束の出番は無さそうですが 別の機会に使えると思いますので
 頭の片隅にでも 置いておいてもらえると良いと思います。

 K84の式は作りなおして、K4:K10に色々な値を入れたりして確認してみて下さい。
 セルに値が一つだけ入っている時に、TRUE になれば OK です。

 その他の所は、それで ばっちり ですよ。

 (HANA)

HANA先生

朝の報告を書く前に、K4に1、K5に1と入れたらK83が2、K84K85がFALSEと出たので、できた!と思ったのです・・・
試しにK5に4と入れたら、あ・・・TRUEになった…

一つだけは「=1」だから(きっと)K83=COUNT(K4:K10)=1なのですね。

でも試しにK83の式を消してK5:K10に「1」「5」「11」と入れたら ん?TRUEになってる…

今度は「5」「11」と入れたらFALSE、で「1」と入れたらTRUE、分かった!「1」が入っていればTRUEになってしまうのですね!
それはK84に(K4:K10,1)と入れたからですね!なるほど〜!!

K84セルに TRUE となっている時 K83には0を入れてもAAAを入れても22を入れても、K85はいつもTRUEでした。。

K83=COUNT(K4:K10)=1としてK4:K10に「1」「2」「5」と入れたら、K83はFALSE、K84K85はTRUEになってしまった・・・

もう少し時間をくださいー

(MONY)


HANA先生

あ、分かった。
K85を直していなかったからでした。=1を追加して=AND(COUNT(K4:K10)=1,COUNTIF(K4:K10,1)=1)にしたら、FALSEになりました。

(MONY)


 もしかして
 (○´ω`○)ゞ
 ↑状態でした?

 そういう落とし穴もあるので、気を付けて下さい。
 完成するまでは、置き換えた式は他のセルに作った方が良いかもしれません。

 もう少し慣れてくると、関数のウィンドウが使える様になって
 式と結果が同時に見られるようになると思いますので。

 >あ、分かった。 
 その調子です!!

 (HANA)


HANA先生

私はいつも (○´ω`○)ゞ状態です・・・

もしかしてできたかも!?
一つずつ関数を入れていって省略の度に列を変えたのでN列になったのですが、

N83=IF(AND(AND(COUNT(N4:N10)=1,COUNTIF(N4:N10,1)=1),AND(COUNT(N11:N13)=1,COUNTIF(N11:N13,1)=1),AND(COUNT(N14:N17)=1,COUNTIF(N14:N17,1)=1),AND(COUNT(N19:N82)=1,COUNTIF(N19:N82,1)=1)),"○","×")

これで合ってますか???

(MONY)


 良い感じですね〜。

 一つのセル範囲毎に考えていったので、その様になると思います。

 で、完成した式を見て「結局?」を考えてみて下さい。

 AND(AND(・・・),AND(・・・),AND(・・・),AND(・・・))
 って事は、結局 すべての評価(引数にしたものの結果)が TRUE の時 "○" ですね。

 たとえば
 AND(OR(・・・),OR(・・・),OR(・・・),OR(・・・))
 だと、そういう訳にはいきませんが。。。

 一区画ずつ AND関数でくくられていると わかりやすい気もしますが
 そこまでしてなくても良い様な気もします。

 (HANA)

HANA先生

 (○´ω`○)ゞ
もう少し時間をください・・・

(MONY)


 はい、ゆっくりどうぞ。

 途中経過の式をセルに入力していると思いますが
  COUNT(N4:N10)=1  COUNTIF(N4:N10,1)=1  COUNT(N11:N13)=1 ・・・・
 それらの8セルを見て下さい。

 で、○にしたいときの8つのセルはどんな値の時で
 どうなっていたら、×にしたいのか。。。。

 今までは 2つのセル毎に見てましたよね。
 2つのセルが両方 TRUE で、次の2つのセルが両方 TRUE で、次の2つのセルが両方・・・
 ・・・ってことは!!?

 (HANA)

HANA先生

おはようございます。

難しい…
すみません、私には難しいのです・・・

「結局?」は全てがTRUEだったとき○で一つでもFALSEがあったら×にしたい

のですが、セルのある範囲の中に「1」が一つだけ入っていたら という条件にしたいから・・・

=IF(AND(O4:O82,1)=4,"○","×")と、また訳の分からない式を作ってしまう・・・
これじゃない、ANDだけ?
それだとCOUNTで数えた意味がないし・・・

(MONY)


 おっと、難しく考えすぎです。
 >「結局?」は全てがTRUEだったとき○
 なので、今  AND関数の中に  いくつかのAND関数が入ってますが
 内側のAND関数からは出して良いと思います。

 AND(AND(条件1,条件2),AND(条件3,条件4))
 と
 AND(条件1,条件2,条件3,条件4) は、同じ結果になりますね?

 (HANA)

HANA先生

ありがとうございます。
書いた後にそう思って、COUNTの方は出してみましたが、COUNTIFが上手くいきませんでした。引数が違っているようなので、上手くいった式と小さなウインドウで照らし合わせてもう少し頑張ってみます。

=IF(AND(AND(COUNT(O4:O10,O11:O13,O14:O17,O19:O82)=1),COUNTIF(O4:O10,O11:O13,O14:O17,O19:O82,1)=1)))

としました。

COUNTIFの方は、最後のカンマ1が「1が」という意味、イコール1が「一つだけ」という意味になると思うので、私が書いた式だと引数が多すぎるのでしょうね。

では何で区切れば良いのだろう?""で区切ってみたらダメでした。

確か""は文字列で使うのでしたよね。

括弧?スラッシュ?何だろう、もう少し頑張ってみます。

(MONY)


>O4:O10,O11:O13,O14:O17

これは O4:O17 と書くのが普通ですが、どうしても分けて書きたいですか?
また、対象となる範囲が
>O4:O10,O11:O13,O14:O17,O19:O82

だとすると、O4:O82 のセル範囲でO18だけがイレギュラーなセルとの
認識のようですが、O18に必ず1以外が入力されているのならば、

COUNTIF(O4:O82,1)=1

とかいても良さそうですけれども。
(みやほりん)


みやほりん様

ありがとうございます。
どうしても分けて書きたい という訳ではなく、分けた範囲に「1」が一つだけ入った状態をチェックしたいのです。

なので、分けて書かなくても「1」が一つだけ入っている事が確認できればOKです。

それから、18行目は数字を何もいれない行(タイトルが入っている)なので、全てひっくるめてしまってよいのですね、それは盲点でした。

そうなると
=IF(AND((AND(P4:P82)=1,AND(P4:P82,1)=1)))ダメか・・・
あ、COUNTが入ってない

もうちょっと待ってください

(MONY)


 あれ?
 作業セルの所まで戻ってみて下さい。

 範囲の個数を少なくしましたが以下の様になりましたよね?
 K83=COUNT(K4:K10)=1
 K84=COUNTIF(K4:K10,1)=1 
 K85=AND(K83,K84) 

 K86=・・・
 K87=・・・
 K88=AND(K86,K87)

 K89=AND(K85,K88)

 最後の式の K85,K88 を置き換えて
    =AND(AND(K83,K84),AND(K86,K87))
 ですが、結局 K83,K84,K86,K87 すべてが TRUE の時 TRUE になれば良いだけなので
    =AND(K83,K84,K86,K87)
 この段階で、それぞれのセルの数式ともう一度置き換えです。

 (HANA)

HANA先生

>範囲の個数を少なくしましたが以下の様になりましたよね?

 K83=COUNT(K4:K10)=1
 K84=COUNTIF(K4:K10,1)=1 
 K85=AND(K83,K84) 

 K86=・・・
 K87=・・・
 K88=AND(K86,K87)

 K89=AND(K85,K88)

そうです、その通りです。

>最後の式の K85,K88 を置き換えて

    =AND(AND(K83,K84),AND(K86,K87))
 ですが、結局 K83,K84,K86,K87 すべてが TRUE の時 TRUE になれば良いだけなので
    =AND(K83,K84,K86,K87)
 この段階で、それぞれのセルの数式ともう一度置き換えです。

=AND(COUNT(K4:K10)=1,COUNTIF(K4:K10,1)=1,COUNT(K11:K13)=1,COUNTIF(K11:K13,1)=1)
置き換えました。

そうすると・・・
N83=IF(AND(AND(COUNT(N4:N10)=1,COUNTIF(N4:N10,1)=1),AND(COUNT(N11:N13)=1,COUNTIF(N11:N13,1)=1),AND(COUNT(N14:N17)=1,COUNTIF(N14:N17,1)=1),AND(COUNT(N19:N82)=1,COUNTIF(N19:N82,1)=1)),"○","×")
これと比べると、ANDが一つ省略された形になるのですね。

なので、
=IF(AND(COUNT(K18:K24)=1,COUNTIF(K18:K24,1)=1,COUNT(K25:K27)=1,COUNTIF(K25:K27,1)=1,COUNT(K28:K31)=1,COUNTIF(K28:K31,1)=1,COUNT(K33:K96)=1,COUNTIF(K33:K96,1)=1),"○","×")
これで合ってますか???

(MONY)


合ってないですね、数字を入れたら×になりました。

ギブアップです、お願いします。(泣)

(MONY)


 >数字を入れたら×になりました。 
 どこに、何の値を入れたのでしょう??

 作業セルはまだ残っていますか?
 どの部分が FALSE になっているのか、確認してみて下さい。

 あるいは、関数の小さいウィンドウを表示させて
 ADN関数の数式を確認してください。
 どこかの引数が FALSE になっていると思います。
 どこがFALSEになっていますか?

 (HANA)

HANA先生

数字は、各項目に1を一つずつ入れました。

最初のIFを開くと倫理式がFALSE、
次のANDを開くと倫理式1のみTUREで倫理式2以下6までFALSE、倫理式7と8はTRUEになってます。??

できた〜〜!!!
よく見たら、数字が違っていました。範囲の指定がどういう訳かめちゃくちゃでした(なんでだろう??)

やっとできました!
HANA先生、長々とお付き合い下さってありがとうございました。

(MONY)


 出来ましたか、良かったです。

 >よく見たら、数字が違っていました。範囲の指定がどういう訳かめちゃくちゃでした
 関数を編集状態(数式バーの中にカーソルがある状態)にすると、
 使用しているセルが色つきの線で囲われます。
 数式バーでは、セル番地の文字色が同じ色に変わります。

 これで確認すると、変なセルを参照しているのが
 簡単に見つかったかもしれません。

 関数のウィンドウも便利に使ってもらえると良いと思います。

 今回たくさん悩まれたと思いますので、その分 力がついていると希望します。
 今度はMONYさんが先生になって 知っている事を教えてあげて下さい。
 その内知らない事まで聞かれる様になってしまうので^^;
 それを調べてまた教えてあげる事で、MONYさんの実力もどんどん上がっていくと思います。

 (HANA)

HANA先生

おはようございます。
忘れないうちにと思い、あれから何度か1から数式を入れ直してみました。
今回は両方学ばせて頂きましたが、HANA先生が最初にご提案下さったSUMの方を使うことにしました。

ゼロからの出発でHANA先生には沢山の時間を割かせてしまって、申し訳ありませんでした。
お蔭様でしっかり身に着いたと思いますし、エクセルのスタートラインに立てた気分です。

タイトルに「VBAが〜」と書きましたが、自分の分からないそんな事をするよりも、1歩ずつ着実にまずは基礎からしっかりと学んだ方が良いと思い直しました。

改めて、ありがとうございました。
今後とも宜しくお願い致します。

(MONY)


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.