- 締切済み
Excel2003での複数条件抽出について
いつもお世話になっております Sheet1のA列にNo. B列には地名(例として東京 大阪 名古屋 福岡とします)C列に氏名 D列には数量が入ります Sheet2以降には東京 大阪 名古屋 福岡のシートがあります 今まではB列の地名と同じシート名に関数を使い各項目がそのまま一覧表示されるようにしていましたが 同一人物が複数回入力される場合 その数量を合計して重複データは一度だけという形で一覧表示されるようにするにはどんな関数を使えばいいでしょうか? オートフィルタを使えばいいとは思いますが 依頼された先からそのまま表示されるように関数を使うよう指示がありました わかりにくいと思いますが ご教示宜しくお願い致します
- rian_2005
- お礼率71% (59/82)
- その他MS Office製品
- 回答数8
- ありがとう数7
- みんなの回答 (8)
- 専門家の回答
みんなの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答No.3です。 >質問なのですが 数式の■には何を入力すれば良いのでしょうか? ちょっと勘違いをされてしまったようですが、■は「サイトが使用しているフォントの中にはない文字」や、「何かの不明な文字」とか、「文字化けの類」等ではなく、そのまま、黒い四角形の(文字としての)記号です。 「しかく」(sikaku)と入力してから、変換キーを押しますと、四角、◆、□、◇、死角、4画、等々の変換候補とともに現れる記号の1つです。 Excelの適当なセルに =CHAR(8739) と入力した際に表示される文字です。 何故その様な記号を使うのかと言いますと、Sheet2のA列に、氏名と地名を合わせたデータを表示させた際に、例えば、「そのまんま東」という氏名の方が、(宮崎県ではなく)京都に住んでいた場合、単純に氏名と住所を繋げただけでは、Sheet2のA列に表示される内容が「そのまんま東京都」となってしまい、東京のデータなのか、京都のデータなのか判らなくなってしまいます。 又、例えば地名のシートに東京シートと京都シートがあった場合、「京都」の文字列が含まれているだけで、京都のデータであると判断する様な関数としたのでは、東京のデータも京都のデータの一部に含まれてしまう事になります。 その様な弊害が生じる恐れを回避するために、Sheet2のA列に表示されるデータには、氏名と住所の区切りをつける目印として、通常の文章や名称では使用される事がまず無い■を間に入れる様にしておき、例えば京都のデータを集計する際にも、「京都」の文字列が含まれているか否かで判断するのではなく、、「■京都」の文字列が含まれているか否かで判断する関数としている訳です。 ですから、必ずしも■でなければならないという訳ではなく、通常の文章や名称では使用される事がまず無い文字であれば、◇や▲、★、等々でも構いません。 尚、今回は地名や人名ですが、これが何かの商品や機械等の名称の場合は、#、-、・、&、等々は、名称の中の文字として使用される可能性があるため、私は、区切りを表す記号として使用する事を避けており、その癖があったため、今回も■を使用した次第です。
- tom04
- ベストアンサー率49% (2537/5117)
No.6です! たびたびごめんなさい。 あまり複雑でない関数でやってみました。 No.5さんとほとんど似たような感じになりますが、各Sheetに作業用の列を設けます。 ↓の画像で左側がSheet1で右側が各Sheetの配置だとします。 (とりあえず「東京」Sheetで説明します) 各SheetのA1セルにはそのSheetのSheet名を入力しておきます。 画面左下のSheet見出し上の「東京」Sheetを選択Shiftキーを押しながら最後の「福岡」Sheetを選択 これで「東京」Sheet以降が作業グループ化されましたので、 A2セルに =IF(AND(Sheet1!B2=$A$1,COUNTIF(Sheet1!$C$2:Sheet1!C2,Sheet1!C2)=1),ROW(),"") B2セルに =Sheet1!B2&Sheet1!C2 という数式を入れ、A2・B2セルを範囲指定 → B2セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。 (Sheet1のデータ以上コピーしておきます。) そして、C2セルに =IF(COUNT(A:A)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(A:A,ROW(A1)))) D2セルに =IF(C2="","",SUMIF(B:B,INDEX(B:B,SMALL(A:A,ROW(A1))),Sheet1!D:D)) という数式を入れ、C2・D2セルを範囲指定 → D2セルのフィルハンドルで下へコピー! これで画像のような感じになります。 最後にSheet見出し上で右クリック → 作業グループ解除 これで完了です。 ※ 各Sheetの作業列(A・B列)が目障りであれば非表示にしておきます。 ※ 極力短い数式にしてみました。これであれば実状のレイアウトに合わせて ご自身で何とかアレンジできないでしょうか? 何度も失礼しました。m(_ _)m
お礼
何度も回答頂きありがとうございます またお礼が遅くなって申し訳ありません 短い数式にしていただけて助かります 何とかできそうです ありがとうございました
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは! お望みの関数ではありませんが・・・ VBAでの一例です。 Sheet1はSheet見出しの一番左側に配置してあり、Sheet2以降に「東京」「大阪」・・・ というSheet名のSheetがあるとします。 Alt+F11キー → メニュー → 挿入 → VBE画面に ↓のコードをコピー&ペーストしてマクロを実行してみてください。 (Alt+F8キー → マクロ → マクロ実行です) Sub 各Sheetに振り分け() 'この行から Dim i As Long, k As Long, n As Long, str As String Application.ScreenUpdating = False For k = 2 To Worksheets.Count str = Worksheets(k).Name With Worksheets(1) .Cells(1, 1).CurrentRegion.AutoFilter field:=2, Criteria1:=str .Columns("C:D").Cells.Copy Worksheets(str).Cells(1, 1) End With With Worksheets(k) For i = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) > 1 Then n = WorksheetFunction.Match(.Cells(i, 1), .Columns(1), False) .Cells(n, 2) = .Cells(n, 2) + .Cells(i, 2) .Rows(i).Delete End If Next i End With Next k Worksheets(1).AutoFilterMode = False Application.ScreenUpdating = True MsgBox "処理完了" End Sub 'この行まで ※ 関数でないのでSheet1のデータ変更があるたびにマクロを実行する必要があります。 ※ 仮に各Sheetに関数が入っている場合は、一旦マクロを実行すると消えてしまいますので、 別Bookでマクロを試してみてください。 >依頼された先からそのまま表示されるように関数を使うよう指示がありました とありますがメンテナンス面からみると、関数で処理してもご希望通りに表示されない場合 関数の訂正などをご自身で出来ない場合はVBAでも同じだと思いますので、 敢えて別案を提示してみました。 参考にならなかったら無視してください。m(_ _)m
お礼
お礼が遅くなって申し訳ありません VBAは全く知識がありませんがこれからじっくり試してみます ありがとうございました
- KURUMITO
- ベストアンサー率42% (1835/4283)
次のようンしてはどうでしょう。 シート1に元の表が有ってA1セルにNo,B1セルに地名、C1セルに氏名、D1セルに数量と項目名がそれぞれあって各データは下方にそれぞれ入力されているとします。 そこで作業列をEおよびF列に設けます。 E2セルには次の式を入力して下方にドラッグコピーします。 =B2&C2 F2セルには次の式を入力して下方にドラッグコピーします。B列で入力されている行よりもかなり下の行までドラッグコピーします。 =IF(B2<>"",IF(COUNTIF(B$2:B2,B2)=1,ROUNDDOWN(MAX(F$1:F1),-4)+10000+ROW(),IF(COUNTIF(E$2:E2,E2)=1,ROUNDDOWN(INDEX(F$1:F1,MATCH(B2,B$1:B1,0)),-4)+ROW(),"")),IF(ROW(A1)<=COUNTA(B$2:B10000)+INT(MAX(F$1:F1)/10000),(ROW(A1)-COUNTA(B$2:B10000))*10000,"")) 東京、大阪、名古屋、福岡などのシートがそれぞれに有るとしてシート見出しでCtrlキーを押しながらそれらの見出しをクリックします。これでそれらのシートは同じ作業グループとなります。 そこで表示されているシート、例えば東京のシートとします。A1セルには地名と入力してB1セルには東京と入力します。 A2セルからD2セルまでには、No,地名、氏名、数値と文字をそれぞれ入力します。 その後にA3セルには次の式を入力してC3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(OR(INDEX(Sheet1!$A:$D,MATCH(SMALL(Sheet1!$F:$F,RANK(ROUNDDOWN(INDEX(Sheet1!$F:$F,MATCH($B$1,Sheet1!$B:$B,0)),-4),Sheet1!$F:$F,1)+ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1))=0,A2=""),"",INDEX(Sheet1!$A:$D,MATCH(SMALL(Sheet1!$F:$F,RANK(ROUNDDOWN(INDEX(Sheet1!$F:$F,MATCH($B$1,Sheet1!$B:$B,0)),-4),Sheet1!$F:$F,1)+ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1))) また、D3セルには次の式を入力して下方にドラッグコピーします。 =IF(B3="","",SUMIF(Sheet1!E:E,B3&C3,Sheet1!D:D)) 最後にシート1を選択することで作業グループを解除します。 その後は各地名のシートについて、それぞれのシートのB1セルに該当の地名を入力することでお求めの表が表示されます。
お礼
回答ありがとうございます 早速試してみます どうもありがとうございました
- MackyNo1
- ベストアンサー率53% (1521/2850)
>今まではB列の地名と同じシート名に関数を使い各項目がそのまま一覧表示されるようにしていましたが 同一人物が複数回入力される場合 その数量を合計して重複データは一度だけという形で一覧表示されるようにするにはどんな関数を使えばいいでしょうか? すでに地名ごとのデータが1つのシートに表示できているなら、同じシートに(例えばG2セルに)以下の関数を入力して下方向にオートフィルして重複のない氏名を表示させてみてはいかがでしょう。 =INDEX(C:C,SMALL(INDEX(((MATCH($C$2:$C$100&"",$C$2:$C$100&"",0)<>ROW($C$2:$C$100)-1)*1000+ROW($C$2:$C$100)),),ROW(A1)))&"" 数量の合計(H2セル)はSUMIF関数で合計します。 =IF(G2="","",SUMIF(C:C,G2,D:D)) レイアウトが気になるなら、最後に元データの部分のA~D列は適宜非表示にします。
お礼
回答ありがとうございます 非表示にする程度なら大丈夫そうです 早速試してみたいと思います
- kagakusuki
- ベストアンサー率51% (2610/5101)
Sheet1のA列のNo.が、顧客ナンバーの様な、個人を区別するためのナンバーであれば話は楽だったのですが、そうと限っている訳ではないため、取り敢えず、B列が同じ地域にとなっている中には、同姓同名の氏名が現れる事は無い(例えば「佐藤一郎」という同姓同名の氏名がある場合には、「佐藤一郎 #1」、「佐藤一郎 #2」の様にして区別している)ものとして、話を進めさせて頂きます。 今仮に、各地名のシートのB1セルに、各々の地名を表示し、A3セルには「No. 」、B3セルには「氏名」、D3セルには「数量」とそれぞれ入力されていて、実際の抽出したデータは4行目以下に表示するものとします。 又、Sheet2のA列とB列を作業列とし使用するものとします。 まず、Sheet2のA1セルに次の関数を入力して下さい。 =IF(OR(INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$C:$C,ROW())=""),"",INDEX(Sheet1!$C:$C,ROW())&"■"&INDEX(Sheet1!$B:$B,ROW())) 次に、Sheet2のB1セルに次の関数を入力して下さい。 =IF(AND($A1<>"",COUNTIF($A$1:$A1,$A1)=1),INDEX(Sheet1!$B:$B,ROW())&"■"&COUNTIF($A$1:$A1,"*?■"&INDEX(Sheet1!$B:$B,ROW())),"") 次に、Sheet2のA1~B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。 次に、 東京 というシート名のシートを作成して下さい。 次に、東京シートのB1セルに次の関数を入力して下さい。 =REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1),FIND(".xls",CELL("filename",B1))),) 次に、東京シートのA4セルに次の関数を入力して下さい。 =IF(ROWS($4:4)>COUNTIF(Sheet2!$B:$B,$B$1&"■*?"),IF(ROWS($4:4)=COUNTIF(Sheet2!$B:$B,$B$1&"■*?")+2,"合計",""),ROWS($4:4)) 次に、東京シートのB4セルに次の関数を入力して下さい。 =IF(OR($A4="",$A4="合計"),"",INDEX(Sheet1!$C:$C,MATCH($B$1&"■"&ROWS($4:4),Sheet2!$B:$B,0))) 次に、東京シートのC4セルに次の関数を入力して下さい。 =IF($A4="","",SUMIF(Sheet2!$A:$A,IF($A4="合計","*?",$B4)&"■"&$B$1,Sheet1!$D:$D)) 次に、東京シートのA4~C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。 次に、東京シートのコピーシートを複数複製して、それぞれのシート名を大阪、名古屋、福岡、等の各地名に変更して下さい。 これで、各地名のシートに抽出結果が自動的に表示されます。
補足
回答ありがとうございます 返信が遅れて申し訳ありません 質問なのですが 数式の■には何を入力すれば良いのでしょうか?
》 わかりにくいと思いますが… そのとおりなので、適切なアドバイスができません。ただ… 》 依頼された先から・・・関数を使うよう指示がありました との由、察するに「依頼先」の担当者は Excelの関数の達人のようです。 此処は、貴方が恥を忍んで、素直に教えを乞うことをお奨めします。
お礼
お礼が遅れ またわかりにくい質問で申し訳ありません 依頼者は達人という訳ではなく 下手に操作してデータがおかしくならないように はじめから関数で出してくれという人なんです 回答ありがとうございました
- Cupper-2
- ベストアンサー率29% (1342/4565)
関数ではなくピボットテーブルかな。 ヘルプを参考に試してみてください。 ピボットテーブルは簡単な集計から、ちょっと複雑な集計まで 少ない手順で簡単に行うことができる機能です。 覚えておいて損はないと思いますよ。
お礼
お礼が遅れて申し訳ありません ピボットテーブルだと簡単ですよね 回答ありがとうございました
関連するQ&A
- エクセル 複数条件での抽出
自社名簿(sheet1)と、系列会社の名簿(sheet2) A列:ID番号 B列:氏名 C列:所属グループ D列:支店 共に、同じ表です。 sheet1とsheet2を照らし合わせ、A~Dまでの情報が全て一致する(sheet2に重複してる)該当者のみに sheet2のE列に★印が反映されるようにしたいのです。 条件が一つですと、沢山の簡易的な関数でわかりやすのですが、 複数の条件での方法が、他の質問者様の回答の説明をみてもしっくりきませんでした。 ご教授宜しくお願いします。
- ベストアンサー
- その他(ビジネス・キャリア)
- <エクセル>複数条件下での個数の数え方
A列に都道府県、B列に地域、C列に売上がある表があります。 東京と大阪を除いた地域のうち、C列で0以上のセルの個数を表示したいと思います。(ここだと2コ) countif、dcountなど考えましたが、うまく表示できません。 列を増やさず、個数を表示できる関数を教えてくださいますか? 宜しくお願いします。 A B C 1 東京 新宿 500 2 大阪 心斎橋 0 3 名古屋 栄 300 4 東京 渋谷 0 5 北海道 札幌 100 6 宮城 仙台 0 7 大阪 梅田 100
- 締切済み
- オフィス系ソフト
- 二つの検索値から別のシートに抽出する
WinXP,Excel2000 下記のような表から例えば氏名コード(A列)103番と 日付(B列)2006/5/3からE列のデータ3.50を導きたい のですが、この場合検索値がA列・B列と2つあるので 同じシートに答えを出す場合は F3=SUMPRODUCT((A1:A9=F1)*(B1:B9=F2)*E1:E9)で できるのですが、別のシートに抽出するにはどうしたら よいでしょうか。ご教授ください。<m(__)m> A列 B列 C列 D列 E列 F列 1 100 2006/5/3 東京 8:10 8.00 103 2 101 2006/5/3 名古屋 9:10 7.25 2006/5/3 3 102 2006/5/3 大阪 7:45 5.00 3.50 4 103 2006/5/3 京都 10:30 3.50 5 104 2006/5/3 札幌 8:40 4.75 6 100 2006/5/4 福岡 8:45 5.00 7 101 2006/5/4 東京 9:05 6.25 8 103 2006/5/4 大阪 8:15 1.50 9 105 2006/5/4 横浜 10:15 3.00
- ベストアンサー
- オフィス系ソフト
- 複数の検索値から任意のデータを抽出する方法
Excelの質問です。 Sheet1には、1行1件のコード、地域、金額の表があります。 A列のコードは、同じコードはひとかたまりになっていますが、 同じコードが2行ある場合や、1行またはそれ以上ある場合と決まっていません(最大6行)。 また、コードの順も昇順・降順になっていません。 Sheet2には、A列にコードだけが入力してあり、コードをキーにして、 Sheet1から地域と金額を自動表示したいのですが、 MATCHやINDEX関数を組み合わせたりしたのですが、うまくいきません。 関数式をお教えください。 <Sheet1> A B C →列番号です。 コード 地域 金額 56789 東京 100 56789 大阪 50 12345 大阪 400 12345 神奈川 200 12345 東京 1000 22222 大阪 300 22222 神奈川 400 22222 東京 100 ・・・・・つづく <Sheet2> A B C 12345 大阪 400 →B列とC列を数式で自動表示したい 神奈川 200 東京 1000 56789 東京 100 大阪 50 22222 ・・・・・つづく
- 締切済み
- オフィス系ソフト
- エクセルのVLOOKUP関数で…(複数条件?の抽出)
●シート2、A列に部品正式名称、B列に部品略称の一覧表(部品の種類は約500点) ●シート3、A列に略称、B,C,D,E,F,G列と続けて寸法などの詳細を記した一覧表 があります。 ●シート1に検索一覧表として、B列(B3~B8)は項目、セルC3~C8にVLOOKUPでシート3の情報が抽出されるようにしてあります。 C1で略称を入力し抽出するのはOKなのですが、正式名称で入力しても同じようにシート3の情報を抽出するようにしたいです。 (IFを使って思いつく関数を組合わせてみたりしたのですが、うまくいきませんでした(TT) できたら関数で何か良い方法ありますでしょうか? 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- [Excel2003]複数条件の関数について
関数について教えて下さい。どうやっても分かりません。 Sheet1に、ユーザーID(列A)、適用1(列B)、適用2(列C)の詳細データがあります。 Sheet2に、ユーザーIDごとの統計をとる為、A列にユーザーIDの一覧を作りました。 Sheet2のA2にあるユーザーで、適用1、適用2が「1」のものの件数を出すにはどうしたらいいのでしょうか。 COUNTIFとIF文を組合わせれば出来るかと思ったんですが、どうにもうまくいきません。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル関数で時間を別の表に分ける
いつもお世話になっております。 下記のことはエクセル関数で、どうしたらできるのか教えてください。 Sheet1 A B C D 1 あああ 大阪 月・火・水 10:00-11:00 2 いいい 大阪 月 10:00-12:00 3 ううう 東京 火・水 11:00-12:00 4 えええ 福岡 水 11:00-12:00 5 おおお 福岡 木 15:00-16:00 シート1には【A】列に名前 【B】列には地名 【C】列には曜日、【D】列には時間が入っています。 シート1の表を参考にシート2は下記のような表をどう入力すればいいですか? A B C D E F G 1 月 月 火 火 水 水 木 210:00-11:00 10:00-11:00 10:00-11:00 10:00-11:00 10:00-11:00 10:00-11:00 5:00-16:00 3 大阪 大阪 東京 大阪 東京 福岡 4 あああ あああ ううう あああ ううう おおお 5 いいい 6 7 811:00-12:00 11:00-12:00 11:00-12:00 11:00-12:00 11:00-12:00 11:00-12:00 9 大阪 東京 福岡 東京 10いいい ううう えええ ううう とっても見にくい参考で申し訳ございません。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel 複数条件該当データの別シート表示
Excelで、2つの条件に該当するデータのみ別シートに表示させる方法を教えてください。 Sheet1には、A列:地区 B列に所属 C列には氏名の表があります。 Sheet2に、該当する条件のものだけを表示させるようにしたいのです。 (たとえば、地区=東京かつ所属=総務部) この条件は、Sheet2のD列以降で指定する欄を設けたいと考えています。 Sheet1のデータ A列 B列 C列 地区 所属 氏名 東京 経理部 山田 大阪 経理部 松本 東京 総務部 加藤 よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- EXCEL2000:データの抽出
EXCEL2000で、同ファイル内のデータ抽出に関して質問があります。 あるシート(シート1)に契約一覧を作成しています。 各契約の担当者を入力した際(A列)に、その担当者コードを自動的にとなりのセル(B列)に入力できるようにしたいと考えています。 対応する担当者コードは、別シート(シート2)に担当者一覧テーブルを作成しました。 おそらく関数をB列に設定して、A列の数値をデータに反映させて返すという形になると思うのですが、どの関数を使えばよいのでしょうか? ヘルプで確認した限りだと、DGET関数というのが適切なような気がするのですが、各引数に何を入力すべきなのかがいまいちつかめません。 ご返答よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- [EXCEL2000]重複データを除去する方法
(1)A列には文字データがあり、B列にはフリガナがあります。 (2)重複しているデータを1つとみなして抽出したいのですが、どうすればよいでしょうか?(データがいくつ重複しているかは決まっていません。) どなたか教えてください! (A列) (B列) 東京都 トウキョウト 東京都 トウキョウト 大阪府 オオサカフ 大阪府 オオサカフ 大阪府 オオサカフ 福岡県 フクオカケン ↓ 東京都 トウキョウト 大阪府 オオサカフ 福岡県 フクオカケン
- ベストアンサー
- オフィス系ソフト
お礼
お礼が遅くなって申し訳ありません 詳しく説明して頂きよくわかりました ありがとうございました