[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セルの合計』（あんどー）
例えばC2に"Bed"と入力したら
D2にはA列の"B""e""d"に該当するB列の数値"2""7""4"の合計"13"がでる。 というようなことはできないでしょうか？
A B C D 文字 数値 検索文字 合計数 A 1 Bed 13 B 2 C 3 D 5 E 4 F 4 G 2 a 2 b 2 c 2 d 4 e 7 ・ ・ ・ ・ ・ ・
[[20061124143503]] 5120
『検索条件を満たすセルの合計』（超EXCEL初心者）
を参考に作成してみましたが、大文字と小文字の区別ができませんでした。
◆これで、いかがでしょうか？ =SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13) (Maron)
ありがとうございました。
といった場合にはどうなるのでしょう？
（というよりもそんなことが可能なのでしょうか???）
例）検索文字"Bed"
文字間が2つあるので合計数は2+1+7+1+4で"15"
文字数はLEN関数で調べられます。 その数から-1すれば文字間数が分かると思いますが。 ・・・そんなのでは駄目ですかね？
(HANA)
でもそれってどこに入れるの？
=SUMPRODUCT( 略 ) で出た数値 ＋ 文字間の数 というのではダメなんでしょか〜？ 足し算するだけではないということですか？ (かなれっと)
改行コードがある場合、数から除きたいなら =LEN(CLEAN(C2)) こういうので文字数を数えるのはどうですか？
(HANA)
=IF(C2="","",SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)+LEN(C2)-1)
この式にどうかくの？
こんにちは〜♪
改行コードも足すのでしたら
(HANA)さんの >=LEN(CLEAN(C2))
を使って、こんな感じでしょうか？
=SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)+LEN(CLEAN(C2))-1
。。。Ms.Rin〜♪♪
衝突しちゃいましたが、現在 「LEN(C2) として文字数を求めると、改行があった場合 改行数も含めた数を教えてくれるから困った」 という事ですよね？
私は、 「改行を含めずに文字数を知りたければ LEN(CLEAN(C2)) とするのはどうですか？」 と書いたつもりですが、 =LEN(CLEAN(C2)) の数式でも期待する文字数が得られませんか？
得られないのなら、現在の式に組み込むより 文字数に関して、期待する数を得られる数式を作るのが先です。
(HANA)
=SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)+LEN(CLEAN(C2))-1
↑
どうやらやりたいことが似ているようです。
チンプンカンプンなので自分にもわかるように教えていただけないでしょうか？
（超EXCEL初心者）
超EXCEL初心者さん、うっかりしてたら流れちゃいました。 えっと、まだ見て居られますかね？
取り敢えず、質問のやりっぱなしはいただけませんね。 レスをつける立場としては、上手く行かなかった場合は 「上手く行かなかった」と言うレスが欲しい物です。
ご質問の件ですが、簡単に言うと MID関数でC2セルの中の文字を一つずつ取り出して その一つずつの文字に関して、EXACT関数でA2:A13と等しいものをさがし おなじ行のB2:B13の値をSUMPRODUCT関数で合計しています。 >LEN(CLEAN(C2)) この部分に関してはCLEAN関数をヘルプで調べてみて下さい。 それでも分からなければ再度ご質問をお願いします。
(HANA)
すみません。 以後気をつけます。
わからないところが2つあります。 1つ目は MID(C2,COLUMN(1:1),1)の COLUMN(1:1)です。 ヘルプを見ると "列番号を調べるセルまたはセル範囲への参照を指定します" とあるけれども、自分の頭ではセル番号ではいけないのかなぁと思い 試しにやってみたら正しい結果を得られませんでした。
2つ目は LEN(CLEAN(C2)) ヘルプには "データ ファイルの先頭や末尾に含まれている印刷できない制御コードを削除することができます"とありました。 印刷できない制御コードというのが改行コードを示しているということですよね。 その制御コードってどこで知ることができますか？
（超EXCEL初心者）
現在これで↓進んでます。
=IF(C2="","",SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)+LEN(CLEAN(C2))-1）
To あんどーさん
無事計算されてますか。良かったですね。
他の方の記入と混ざらないように、ご自身の文章の最後には
ご署名をお願いしますね。
To 超EXCEL初心者さん >1つ目は COLUMN(1:1)です。 セル範囲で「1:1」と言うのは、「１行目」を表します。 ワークシートの列数はA列からIV列まで在りますので、 COLUMN(1:1) は COLUMN(A1:IV1) を表します。 ○試してみて上手く行かなかった時は、どこのセル番号を入れましたかね？ セル番号が入ると、少しは何か分かりやすいですかね？
ここから先は、SUMPRODUCT関数の話になってしまいますので 続きの話が必要なら、そのように仰って下さい。 一応、SUMPRODUCT関数の解説ページです。 https://www.excel.studio-kazu.jp/lib/e3h/e3h.html
>2つ目はLEN(CLEAN(C2)) >その制御コードってどこで知ることができますか？ これは、ご質問の意味がよく分からないのですが 「コード番号が知りたい」と言う事ですかね？ [[20040721074954]]『ALT＋ENTERを文字列として入力したい』（Miya）
「制御コードが入っているかどうかを確認する方法 が知りたい」ですかね？ それとも、もっと単純に「制御コードとは何か が知りたい」のですか？
(HANA)
これは「１行目」を表しているんですね。 試してみて上手く行かなかった・・・ということで質問していたんですが、 どうやら自分は意味のないお試しをしていたようです。（忘れてください）
>COLUMN(1:1) は COLUMN(A1:IV1) を表します。 ということがわかったので、これに関してもう一つ質問です。
MID(C2,COLUMN(1:1),1
↑
なぜココに使うのかがわかりません。
MID関数のヘルプを見ると書式はMID(文字列,開始位置,文字数)となっているんですが、 開始位置？ 何がなんだかよくわかりません。 ご教授願います。
2つ目のLEN(CLEAN(C2)) >「コード番号が知りたい」と言う事ですかね？ まさにその通りです！ [[20040721074954]]『ALT＋ENTERを文字列として入力したい』（Miya） で解決です。ありがとうございました。
（超EXCEL初心者）
>ここから先は、SUMPRODUCT関数の話になってしまいますので と言うことなのですが、一応リンク先はご確認いただけましたかね？
SUMPRODUCT関数は「それぞれの（条件に対する）結果を合計する」
と考えると、イメージが分かりやすいかもしれません。
今回“それぞれの”とは、
MID関数の中にあるCOLUMN(1:1)→ COLUMN(A1),COLUMN(B1),・・・,COLUMN(IV1)と
EXACT関数の中にある A2:A13 → A2,A3,・・・,A13
の事です。
MID(__,___________,_).1 =======.3
>SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)
EXACT(~~~~~~~~~~~~~~~~~~~~~,~~~~~~).2
まず、COLUMN(A1)に関して
1.MID関数が、C2から１文字目(COLUMN(A1))を１文字抜き出します。
2.EXACT関数で、抜き出した一文字と同じ文字をA2:A13から探します。
ここでも、A2と等しいか？ TRUE or FALSE
A3と等しいか？ TRUE or FALSE
A4と等しいか？ TRUE or FALSE
一セルずつ、A13まで見比べて、TRUEかFALSEを判断します。
3.その結果に、B2:B13の値をかけ算します。
このかけ算の方法は、上から順に組み合わせてかけ算します。
つまり、A2と等しいか？ TRUE or FALSE の結果 ×B2の値
A3と等しいか？ TRUE or FALSE の結果 ×B3の値
A4と等しいか？ TRUE or FALSE の結果 ×B4の値
この要領で、B13までかけ算をします。
この時、TRUEは「1」 FALSEは「0」として計算されるので
A列が等しかった行のみTRUE「1」×B列同行の値=B列同行の値
（他の行は等しくない FALSE「0」×B列同行の値=0）
次に、COLUMN(B2)に関して
1.MID関数が、C2から２文字目(COLUMN(B1))を１文字抜き出します。
2.EXACT関数で、抜き出した一文字と同じ文字をA2:A13から探します。
ここでも、A2と等しいか？ TRUE or FALSE
A3と等しいか？ TRUE or FALSE
A4と等しいか？ TRUE or FALSE
一セルずつ、A13まで見比べて、TRUEかFALSEを判断します。
3.その結果に、B2:B13の値をかけ算します。
このかけ算の方法は、上から順に組み合わせてかけ算します。
つまり、A2と等しいか？ TRUE or FALSE の結果 ×B2の値
A3と等しいか？ TRUE or FALSE の結果 ×B3の値
A4と等しいか？ TRUE or FALSE の結果 ×B4の値
この要領で、B13までかけ算をします。
やはりA列が等しかった行のみTRUE「1」×B列同行の値=B列同行の値
同様に、COLUMN(IV1)と言うと・・・256ですね・・・256文字目まで
それぞれについて「A列が等しかったB列同行の値」と言うのを求め、合計をします。
と、書いてはみましたが・・・しっかりイメージして下さい。 最初は、リンク先の様な小さなサンプルで確認してもらうのが良いと思います。 そちらのイメージがしっかり出来れば、こちらのイメージも少しは分かりやすくなるかもしれません。
(HANA)
>ここから先は、SUMPRODUCT関数の話になってしまいますので と言うことなのですが、一応リンク先はご確認いただけましたかね？
確認しました。
今日はずっとにらめっこしてました。 頭の中がごちゃごちゃになって、一度まっさらに戻して再度チャレンジ！!!! ちょっと分かったかも！と思いきや あれれれ？？？ の繰り返しでようやく理解できるようになりました。
HANAさんの説明とってもわかりやすいです。 すげっ!!!って思いました。
1つ疑問に思ったことがあります。 256文字以上あった場合どうするんだろう？と・・・ 自分は単純にCOLUMN(1:1)の（1:1）の部分を（1:10）とかにすればいいのかな？ と思ってやってみたんですけど、256文字目までしかカウントされずに失敗に終わりました。 なぜダメなのですか？
（超EXCEL初心者）
それは、COLUMN関数だからだと思いますよ。 （この発言にはとても自信は無いのですが・・・・） COLUMN(1:10)はCOLUMN(A1:IV10)を表しますが COLUMN(A1)もCOLUMN(A10)もA列である限り答えは「１」です。
このままで256文字以上を数えたければ、 まず、256文字目までを数え 256+COLUMN(1:1)に変更した式で257文字目から501文字目 までを数えた物とで足し算するのはどうでしょう。 なんだか、姑息な手段ですが。（笑）
あるいは、行であれば65536行存在しますので 一覧表を列方向に伸ばすことにして [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [1] 文字 A B C D E F G a b c d e [2] 数値 1 2 3 5 4 4 2 2 2 2 4 7 [3] [4] 検索文字 合計数 [5] Bed 13 =SUMPRODUCT(EXACT(MID(A5,ROW(A:A),1),B1:M1)*B2:M2) これで理論上65536文字目まで数えられると思います。
(HANA)
だいぶご無沙汰になってしまいました・・・
>一覧表を列方向に伸ばすことにして
・
・
・
>=SUMPRODUCT(EXACT(MID(A5,ROW(A:A),1),B1:M1)*B2:M2)
↑これだと今度は一覧表の文字と数値が256個分しか持てないってことですよね？
一覧表の文字がアルファベットではなく漢字だったら足りないですね。
>256+COLUMN(1:1)に変更した式で257文字目から501文字目 >までを数えた物とで足し算するのはどうでしょう。 >なんだか、姑息な手段ですが。（笑）
この方法がbest！なのではないかと思いました。 >256+COLUMN(1:1)に変更した式 とはこれ（↓）で合ってますか？
=SUMPRODUCT(EXACT(MID(C2,COLUMN(1:1),1),A2:A13)*B2:B13)+SUMPRODUCT(EXACT(MID(C2,256+COLUMN(1:1),1),A2:A13)*B2:B13)+LEN(CLEAN(C2))-1
それとももっとスマートな式にできるのでしょうか？
それと新たな質問です。 C2の検索文字の欄は複数行ある文章です。 その行の中で一番大きい値を合計数に出したいということはできますか？
例 ABCDEFGHIGK LMNO PQRSTUVWXYZ
それぞれの行の合計は 1行目：15 2行目：7 3行目：20 だったら3行目の値を合計数にだす。
これってできますか？
（超EXCEL初心者）
こんばんは〜♪
元々は、違う方の質問ですから 別スレッドを立てた方がいいですね。。。
>例 > ABCDEFGHIGK > LMNO > PQRSTUVWXYZ
> それぞれの行の合計は > 1行目：15 > 2行目：7 > 3行目：20 > だったら3行目の値を合計数にだす。
こちらだけです。。。
こんな表の場合です。。。
A B C D E [1] 文字 数値 検索文字 作業列 合計 [2] A 1 ABCDEFGHIJK 12 66 ←C3セルの1行目 LMNO 17 54 ←C3セルの2行目 PQRSTUVWXYZ #DIV/0! 231 ←C3セルの3行目 [3] B 2 [4] C 3 [5] D 4 [6] E 5 [7] F 6 [8] G 7 [9] H 8 [10] I 9 [11] J 10 [12] K 11 [13] L 12 [14] M 13 [15] N 14 [16] O 15 [17] P 16 [18] Q 17 [19] R 18 [20] S 19 [21] T 20 [22] U 21 [23] V 22 [24] W 23 [25] X 24 [26] Y 25 [27] Z 26
D列に作業列を作ります
D2セルへ
=1/LARGE(INDEX((MID($D$2,ROW($1:$100),1)=CHAR(10))/ROW($1:$100),),ROW(A1)) 下へコピーします。
E2セルへ =SUMPRODUCT(EXACT(MID(LEFT(C2,D2),COLUMN(1:1),1),A2:A27)*B2:B27) E3セルへ =SUMPRODUCT(EXACT(MID(LEFT(MID(C2,D2+1,D3-D2-1),D2),COLUMN(1:1),1),A2:A27)*B2:B27) E4セルへ =SUMPRODUCT(EXACT(MID(MID(C2,D3+1,LEN(C2)-D3),COLUMN(1:1),1),A2:A27)*B2:B27) カン違いならゴメンナサイ!!
。。。。Ms.Rin〜♪♪
ふたたび〜です。。。Ms.Rin
> それぞれの行の合計は > 1行目：15 > 2行目：7 > 3行目：20 > だったら3行目の値を合計数にだす。
これは、最初の様に文字間を 1 と として足すんでしょうか？
でしたら 式を訂正します。上の式の最後に文字間分足します。
E2セルへ =SUMPRODUCT(EXACT(MID(LEFT(C2,D2),COLUMN(1:1),1),A2:A27)*B2:B27)+D2-2 E3セルへ =SUMPRODUCT(EXACT(MID(LEFT(MID(C2,D2+1,D3-D2-1),D2),COLUMN(1:1),1),A2:A27) *B2:B27)+D3-D2-2 E4セルへ =SUMPRODUCT(EXACT(MID(MID(C2,D3+1,LEN(C2)-D3),COLUMN(1:1),1),A2:A27)*B2:B27) +LEN(C2)-D3-1
でした。。。♪♪
>元々は、違う方の質問ですから >別スレッドを立てた方がいいですね。。。
おっしゃる通りです。 あんどーさん、お邪魔してスミマセン。
続きは [[20070124142059]] 『一つのセル内に複数行で書かれている文章を1行ずつに分ける』 （超EXCEL初心者） でやっていこうと思います。
。。。。Ms.Rin〜♪♪さん、聞きたいことが山ほどありますのでよろしくお願いします。 あんどーさん、おじゃましました。失礼します。m(__)m
（超EXCEL初心者）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.