[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データから抽出するアルゴリズム』（たなっぺ）
データからうまく抽出する関数を作り新しいデータを作る函数を作成しようと思っていますがうまく行きません。
シート1には店の種類とIDが書かれています
00001 スーパー
00002 スーパー
00003 コンビニ
のような感じです。
シート2には店のIDと各人が店を利用した回数が書かれています。
空欄 00001 00002 00003
佐々木 1 0 1
佐藤 2 1 0
高橋 0 0 1
このシートから各人がすべてのスーパーを使った回数の総和（上に書かれている範囲の場合佐々木さんは1回、佐藤さんは3回、高橋さんは0回）を表す関数を作りたいのですがなかなか思いつきません。ちなみにデータは上に書いてあるよりずっと大きいです。
また、シート1を店ごとに分けてスーパーだけのデータを作ってsumif函数を使うという方法も考えましたがこちらも未完成です。できたらシートを加工する方法、しない方法両方知りたいのでよろしくおねがいします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
関数は不得手ですが練習してみました。作業「行」を使います。
Sheet2 の下のほうのB列、たとえば B10 に =VLOOKUP(B1,Sheet1!$A$1:$B$1000,2)="スーパー" これを右に必要なだけフィルコピー。
で、佐々木さんは =SUMPRODUCT((B$10:D$10=TRUE)*(B2:D2)) これを下にフィルコピー。 なお、 D や 10 は実態に合わせて。
たぶん専門家さんなら、作業「行」の式を直接、佐々木さんの式に組み込んで、作業「行」なしにできるんだと思います。
（β） 2015/07/25(土) 05:47
いじくっているうちに、作業「行」なしで結果がでました。
佐々木さん =SUMPRODUCT((TRANSPOSE(Sheet1!B$1:B$3="スーパー"))*(B2:D2)) これを Ctrl/Shift/Enter で確定させて下にフィルコピー。
ただし、シート1の登録の数と順番がシート２の１行目と同じであることが条件ですが。 B$3 の 3 と D2 の D は、必要なだけ大きくしてもいいですが、双方、矛盾なく大きくしてください。
（β） 2015/07/25(土) 08:11
2行目にVLOOKUPで種類を書き込みます（行の追加）。
A B C D 1 00001 00002 00003 2 スーパー スーパー コンビニ 3 佐々木 1 0 1 4 佐藤 2 1 0 5 高橋 0 0 1
合計を得たいセルに 配列数式で、
=SUM(MMULT(B3:D5,1*TRANSPOSE(B2:D2="スーパー")))
とします。
この式の中に、VLOOLUPなどで、2行目の機能を組み込むのは難しいのかも知れない。
専門家にこのあたり教えて頂きたい気持ちもあるが、
同時に、余り複雑なものにしないほうが良いという気持ちもあります。
（案外簡単にできるのかも。専門家ではないのでお許しを）
・店舗の種類を書き入れたり、
・各人別に求めて、合計を求める
ことで良いのではないでしょうか。
それが支障があるというなら、非表示にして隠せば良いわけですし。
何よりも、早く手軽に結果を出すことを目指したほうがよいでしょう。
（γ） 2015/07/25(土) 09:22
作業「行」は、γさんの案です。
E3: =SUMPRODUCT((B$2:D$2="スーパー")*B3:D3) 下にフィルコピー
βさんの最初の回答そのものだったかも？
（マナ） 2015/07/25(土) 10:00
E3: =SUMIF(B$2:D$2,"スーパー",B3:D3)
（マナ） 2015/07/25(土) 11:14
（γ） 2015/07/25(土) 14:39
>>βさんのTRUEの方もダメでした。 >>B$2:D$2="スーパー" のところがvlookupで得られたデータと整合させているのがまずいのでしょうか？
最初にアップしたほうですね。
下のほうに（例では10行目のB10）に =VLOOKUP(B1,Sheet1!$A$1:$B$1000,2)="スーパー" これを右に必要なだけフィルコピー こうした上で、 佐々木さんは =SUMPRODUCT((B$10:D$10=TRUE)*(B2:D2)) これを下にフィルコピー。 なお、 D や 10 は実態に合わせて。
なんですが、実際に試されたのはどんな場所にどんな数式だったんでしょうか？
（β） 2015/07/25(土) 19:06
SHeet1とSheet2がある新規ブックの標準モジュールに以下のマクロを貼りつけて実行してみてください。 ↑で申し上げた式がSheet2にセットされて、それぞれの値がでているはずですが？
Sub Test()
With Sheets("Sheet1")
.Cells.Clear
.Range("A1:B1").Value = Array("'0001", "スーパー")
.Range("A2:B2").Value = Array("'0002", "スーパー")
.Range("A3:B3").Value = Array("'0003", "コンビニ")
End With
With Sheets("Sheet2")
.Cells.Clear
.Range("A1:D1").Value = Array("", "'0001", "'0002", "'0003")
.Range("A2:D2").Value = Array("佐々木", 1, 0, 1)
.Range("A3:D3").Value = Array("佐藤", 2, 1, 0)
.Range("A4:D4").Value = Array("高橋", 0, 0, 1)
'ここから式をセット
.Range("B10:D10").Formula = "=VLOOKUP(B1,Sheet1!$A$1:$B$1000,2)=""スーパー"""
.Range("F2:F4").Formula = "=SUMPRODUCT((B$10:D$10=TRUE)*(B2:D2))"
End With
End Sub
（β） 2015/07/25(土) 19:18
http://www1.axfc.net/u/3506863
にアップしたのでよろしくお願いします。
シートもひとつにまとめておきました。
（たなっぺ） 2015/07/26(日) 18:11
練習用ブック拝見。
レイアウトは、βが想定しているものではないですね。
それはさておき。
１．まず、Sheet2 のB9:B11 の文字、スーパー等の後ろに半角スペースが付いてますよ。 これを取り除けば COUNTIF の数値が正しくなるはずです。
２．SUMPRODUCT の式、
=SUMPRODUCT((B$2:D$2="スーパー"),B3:D3) になっていますが、
=SUMPRODUCT((B$2:D$2="スーパー")*(B3:D3)) じゃないのですか？
（β） 2015/07/26(日) 18:52
ご指摘の通り半角スペースが全ての元凶でした。
また、sumprodut関数ですが,でも通ると思っていましたが*でないとダメなようですね。
2つの所を直したら完全に正しい答えが出てきました。
ありがとうございました。
（たなっぺ） 2015/07/26(日) 19:36
>=SUMPRODUCT((B$2:D$2="スーパー"),B3:D3) SUMPRODUCT関数でセル参照の場合は（というよりも計算を行う関数でセル参照の場合）TRUE、FALSEなどの論理値（や空白、文字など）は無視される。
/*-+などの演算子を使った計算では「TRUE」が「1」に、「FALSE」が「0」に自動変換されて使われる。
なので >=SUMPRODUCT((B$2:D$2="スーパー")*1,B3:D3) と論理値を数値に変換すると計算される。 （こちらの場合、B3:D3内に文字列があってもそこは無視してエラーにならない） （ねむねむ） 2015/07/27(月) 11:32
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.