ジメジメする梅雨のお悩み、一挙解決! >>

Excelでシート1のアンケートデータを
シート2で個別に抽出できるようにしたいのですがどのようにすればいいでしょうか。

「【Excel】該当データを抽出」の質問画像

A 回答 (5件)

こんばんは!



必ずQ1~Q3のどこかの列に「1」が入るのであれば・・・

Sheet2のB4セルに
=INDEX({5,4,3,2,1},SUMPRODUCT((OFFSET(Sheet1!A$1,MATCH(B$1,Sheet1!$A:$A,0)-1,MATCH(A4,Sheet1!$1:$1,0)-1,,5)=1)*COLUMN(A1:E1)))

としてフィルハンドルで下へコピー!

何も入らない場合もあるときは、長くなりますが
=IF(COUNT(OFFSET(Sheet1!A$1,MATCH(B$1,Sheet1!$A:$A,0)-1,MATCH(A4,Sheet1!$1:$1,0)-1,,5)),INDEX({5,4,3,2,1},SUMPRODUCT((OFFSET(Sheet1!A$1,MATCH(B$1,Sheet1!$A:$A,0)-1,MATCH(A4,Sheet1!$1:$1,0)-1,,5)=1)*COLUMN(A1:E1))),"")

としてみてください。

※ 当然のコトですが、一つの質問に対して
重複しての入力はない!という前提です。m(_ _)m
    • good
    • 0
この回答へのお礼

COLUMNという関数は初めて使いました。
無事に抽出されました、ありがとうございます。

お礼日時:2017/06/14 12:57

Sheet2!B4: =6-MATCH(1,OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!A$

1:A$6,0),MATCH(A4,Sheet1!$1:$1,0),,,"Sheet1")),,,,5))
    • good
    • 0
この回答へのお礼

ADDRESSという関数は初めて使いました!
無事に抽出することができました。
ありがとうございます。

お礼日時:2017/06/14 12:56

ANo2です。



連投で訂正です。申し訳ない。

式を記入するのは、B6セルではなくB4セルですね。(汗)
    • good
    • 0

こんにちは



データ表を作るときには、後でどのような加工や分析をするかを考えたうえで作成することをお勧めします。
難しい集計にしておいて、「なんとかしてくれ~!」というのではなく・・・

シート2のB6に以下を入力して、下方にフィルコピー
=6-MATCH(1,OFFSET(OFFSET(Sheet1!$B$2:$F$2,MATCH($B$1,Sheet1!A$3:A$6,0),0),0,INT(MATCH(A4,Sheet1!$B$1:$P$1,0)/5)*5),0)

※ 参照が煩雑になってきたので、最後は『6-index』で数値で求めるというズルに走りました。(本来は、これをインデックスにして、さらに回答の項目名を参照すべきなんだろうけれど…)
    • good
    • 0
この回答へのお礼

ありがとうございます!
複雑な関数ですが、きちんと表示されました。

お礼日時:2017/06/14 12:55

できないことはないが添付図の様な形ではダメなんでしょうか?


また、同姓同名はいないのでしょうか?
「【Excel】該当データを抽出」の回答画像1
    • good
    • 0
この回答へのお礼

もともと、あるデータがシート1のタイプになります。
次回はそのように入力したいと思います。

お礼日時:2017/06/14 12:54

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Q「年月」ごとにカウント

お世話になっております。

C列には、『初回入会年月日』として入力しております。
E列から右方向へ、「2017年1月」を始めとし「年月」を表記しております。
この「年月」ごとの欄に、C列に列記している「初回入会年月日」を検索して
該当する「年月」の件数を表示させる仕組みです。

例えば、図表から
C列の「初回入会年月日」には、

「2017年1月17日」
「2017年1月19日」
「2017年1月17日」
「2017年1月31日」

1月には4件あり、それをE2に、「4」と表示させるといった感じです。

C列の「初回入会年月日」を検索し、該当する「年月」の件数を表示させる関数式を教えてください。
m(__)m

※補足説明
・C列の表示形式は、「2017/○/○」と打込み、ユーザー定義「yyyy"年"m"月"d"日"」で設定しております。
・「年月」の項目の表示形式は、年頭の「2017/1/1」と入力してユーザー定義で
「yyyy"年"m"月"」と設定し、
 2月以降は、「2017/2/1」と打ち込み、それをユーザー定義「m"月"」で設定
・C列のデータ件数 500件 $C$2:$C$501

お世話になっております。

C列には、『初回入会年月日』として入力しております。
E列から右方向へ、「2017年1月」を始めとし「年月」を表記しております。
この「年月」ごとの欄に、C列に列記している「初回入会年月日」を検索して
該当する「年月」の件数を表示させる仕組みです。

例えば、図表から
C列の「初回入会年月日」には、

「2017年1月17日」
「2017年1月19日」
「2017年1月17日」
「2017年1月31日」

1月には4件あり、それをE2に、「4」と表示させるといった感じです。

C列の「初...続きを読む

Aベストアンサー

No.2・3です。

まずNo.2の方に関して・・・
SUMPRODUCT関数の範囲内に「文字列」が含まれているというコトはないですか?
文字列が含まれている場合はエラーになります。

No.3に関して・・・
C列と1行目の日付はどちらもシリアル値になっているのですよね?
そして1行目の日付はすべて各月の1日のシリアル値だというコトなのであのような数式にしました。

お示しの画像通りの表を手元のExcelで確認し、投稿した数式です。

こちらではE2セルにちゃんと「4」が表示されています。

※ 502行目以降に数値データがあるととんでもない結果が返るコトがあります。
No.3の数式の範囲を501行目までに限定し
=COUNTIFS($C2:$C501,">="&E1,$C2:$C501,"<"&EDATE(E1,1))

としたらどうなりますか?m(_ _)m

Qエクセル SUMPRODUCT関数について

SUMPRODUCT関数を使い、以下の合計を件数を出したいのですが、いろいろ調べてもよくわかりませんのでご教示をお願いいたします。(エクセル2010使用)

【やりたいこと】
以下の表を使用し、「みかん」と「ばなな」を購入した件数を月ごとに合計して表示する関数を作りたい。4月の合計件数はB8セル、5月はB9セル、6月はB10セルに表示できるようにします。

     (A列)  (B列)
(行) 種類  購入日
 1  みかん 4月3日
 2  ばなな 4月15日
 3  りんご 5月6日
 4  りんご 5月30日
 5  ばなな 6月7日
 6  みかん 6月9日
 7
 8 【結果】 4月 2
 9      5月 0
10      6月 2

よろしくお願いします。

Aベストアンサー

こんにちは!

今回の質問の場合は「OR」条件になりますので、足し算にする必要があります。

↓の画像ではA8~A10セルの表示形式をユーザー定義から
0月
とし、単に4とか5という数値のみを入力しています。

B8セルに
=SUMPRODUCT((MONTH(B$1:B$6)=A8)*(A$1:A$6="みかん"))+SUMPRODUCT((MONTH(B$1:B$6)=A8)*(A$1:A$6="ばなな"))

という数式を入れフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

Qエクセル 関数 教えてください!

添付画像のようにしたいのですが、エクセルでそんなことが出来るのか分かりません。
賢者の皆様、知恵をお貸しください。

詳細はこんな感じです。

①②③は条件付書式ということはなんとなく分かります!
僕なりに一生懸命悩んでしましたが、浅知恵のためお手上げ状態です。。

①=予想と結果が1番で一致したときに、予想列の該当セルを黄色に塗りつぶす
②=予想と結果が2番で一致したときに、予想列の該当セルを赤色に塗りつぶす
③=予想と結果が3番で一致したときに、予想列の該当セルを緑色に塗りつぶす
④=予想A、予想B合計の統計点数を出す(1番予想が3点、2番予想は2点、3番予想は1点)
⑤=④を数字の大きいもの順に抽出する(同じ値は表示順で抽出)
⑥=1番予想が予想通りになった確率を求める
⑦=2番予想が予想通りになった確率を求める
⑧=3番予想が予想通りになった確率を求める

よろしくお願いいたします!!!!!!

Aベストアンサー

こんばんは!

①~③は大丈夫というコトなので・・・

④~⑤についてだけ
まず④は
↓の画像のように作業用の表を作成するのが簡単だと思います。
「作業表1」は1番~3番の「得点?」の表を作成しておきます。
K2セルに
=IF(B9="",0,VLOOKUP(B9,$O$2:$P$4,2,0))+IF(F9="",0,VLOOKUP(F9,$O$2:$P$4,2,0))

という数式を入れ列・行方向にフィルハンドルでコピー!

⑤について
「作業表2」のO11セルに
=COUNTIF(K$2:K$8,">"&K2)+COUNTIF(K$2:K2,K2)

という数式を入れフィルハンドルで右へ3列分・下へ7行コピーしておきます。
そしてK11セルに
=INDEX($J$2:$J$8,MATCH(ROW(A1),O$11:O$17,0))

という数式を入れ列・行方向にフィル&コピー!
これで画像のような感じになります。

※ 作業用の表が目障りであれば遠く離れた列にするか
非表示にしておいてください。

⑥~⑧については
0%か100%のどちらかになってしまいますよね?
それでも良いのであれば・・・
B17セル(%表示にしておく)に
=(VLOOKUP($A17,$A$3:$D$5,COLUMN(),0)=INDEX($A$9:$A$15,MATCH($A17,B$9:B$15,0)))*1

という数式を入れフィルハンドルで列・行方向にコピー!

B17セルを選択 → B17セルの四辺にマウスポインタを移動させ上下左右の小さな矢印になったところで
Ctrlキーを押しながらF17セルまでドラッグ&ドロップ
数式内の「COLUMN関数」だけに手を加え
=(VLOOKUP($A17,$A$3:$D$5,COLUMN(B1),0)=INDEX($A$9:$A$15,MATCH($A17,F$9:F$15,0)))*1

という数式にして、これも列・行方向にフィル&コピー!

こんな感じではどうでしょうか?m(_ _)m

こんばんは!

①~③は大丈夫というコトなので・・・

④~⑤についてだけ
まず④は
↓の画像のように作業用の表を作成するのが簡単だと思います。
「作業表1」は1番~3番の「得点?」の表を作成しておきます。
K2セルに
=IF(B9="",0,VLOOKUP(B9,$O$2:$P$4,2,0))+IF(F9="",0,VLOOKUP(F9,$O$2:$P$4,2,0))

という数式を入れ列・行方向にフィルハンドルでコピー!

⑤について
「作業表2」のO11セルに
=COUNTIF(K$2:K$8,">"&K2)+COUNTIF(K$2:K2,K2)

という数式を入れフィルハンドルで右へ3列分・下へ7行コピーしておきま...続きを読む

Q1つのSUMIFSをSUMで括った数式の意味について

エクセル2010を使っている者です。

会社で使っているファイルで
=SUM(SUMIFS(合計!J:J,合計!G:G,{"1000","9000"},合計!H:H,"<=160",合計!F:F,5000))
という数式を使っていました。

SUMIFS関数はSUM(合計)の意味を含んでおり、SUMで括る意味がわかりませんでした。
(SUMの中に複数のSUMIFSを入れているのであれば、SUMIFS(・・・)+SUMIFS(・・・)という
意味になり理解できるのですが)

また、{}の意味もわかりません。

どなたか、ご教示願います。

Aベストアンサー

SUMIFS(合計!J:J,合計!G:G,{"1000","9000"},合計!H:H,"<=160",合計!F:F,5000)
を数式バー内で選択して[F9] 配列の結果が返ります。1行2列
それを合計するSUMです。
G列が1000 または 9000の場合で、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計しなさい

G列が1000、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計したものと
G列が9000、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計したものを足しなさい
でもよいです

Qexcelで条件に合うよう、複数のセルの合計を求めたい

例えば、次のように並んでいるセルの数値があるとします。

1515
2748
540
5509
2195
680
7142
305
5042
530
667
325
9950
4800

その合計が30000以上で、かつ、最小の数字となるよう、複数のセルを選択したいと思いますが、これを実現できる関数はありますか?

Aベストアンサー

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積和

で、全16384通りの計算が行われるので、A4:末尾を選択して並べ替えすると、
合計が30002となる、
1515
2748
2195
680
7142
305
667
9950
4800
が確認できるとか。

--
もっと数値の数が増えると、この方法では厳しいので、

ナップザック問題をExcelで解く
http://www.geocities.co.jp/SiliconValley-Oakland/8139/

みたいなプログラムで解くような事になります。


条件が違うのでプログラムはそのまま使えませんが、似た質問。

エクセルで、「袋詰め問題」を解きたい - Excel(エクセル) 解決済 | 教えて!goo
https://oshiete.goo.ne.jp/qa/1255891.html

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積...続きを読む

QExcel 別シートへのデータ抽出方法

Excelのシート1から条件が一致するデータをシート2に表示させるにはどのような関数を使用すれば良いのでしょうか?
初心者なのでわからないことばかりですがよろしくお願い致します。

イメージは下記のような感じです
〈シート1〉
A B C D
1 ID11 氏名 部署 内容
2 ID 22 氏名 部署 内容



〈シート2〉シート1のA列の値とシート2のA1に入力した値が一致する項目の行を表示させたい。
A B C D
1 ID 11 ←(できれば手段で入力したい)
2 シート1A列がID11の行全てを表示

説明がうまくできず、申し訳ありませんがどうか教えて頂けると助かります。
お願い致します。

Aベストアンサー

こんにちは!

> シート1A列がID11の行全てを表示
というコトですので、Sheet1のA列には重複してデータが存在するというコトですよね?

一例です
↓の画像のようにSheet2のA1セルに検索したい「ID」を入力するとします。
A4セルに
=IFERROR(INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!$A$1:$A$100=$A$1,ROW($A$1:$A$100)),ROW(A1)))&"","")

配列数式ですので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → A4セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
A4セルのフィルハンドルで列・行方向にコピーすると
画像のような感じになります。m(_ _)m

QIF、AND関数について

EXCEⅬの超初心者です。下記の表はA社の「支払い表」見本と、銀行の「振り込み料金表」です。この支払表に送料を表示させたくて、IF関数やAND関数で計算式を作りたいのですが、エラーばかり! どなたか助けていただけませんか!!

◇支払い表 ◇
支払先(口座名) 振込先   銀行番号/支店番号 口座番号 振込金額     送料
川田敦男    三菱東京UFJ/室町   0005/430 4683231 \60016  ?
小野田美奈子 三井住友/新宿西   0009/259 4030041 \27000  ?
田口たつ    みずほ/横浜駅前   0001/292 1207310 \9000  ?
株式会社テレパ みずほ/青山   0001/211 5675505 \31680   ?



◆当行同一支店内宛
3万円未満 216円
   3万円以上 432円

◆当行本支店宛
3万円未満 324円
   3万円以上 540円

◆他行宛(電信)
3万円未満 648円
  3万円以上 864円

EXCEⅬの超初心者です。下記の表はA社の「支払い表」見本と、銀行の「振り込み料金表」です。この支払表に送料を表示させたくて、IF関数やAND関数で計算式を作りたいのですが、エラーばかり! どなたか助けていただけませんか!!

◇支払い表 ◇
支払先(口座名) 振込先   銀行番号/支店番号 口座番号 振込金額     送料
川田敦男    三菱東京UFJ/室町   0005/430 4683231 \60016  ?
小野田美奈子 三井住友/新宿西   0009/259 4030041 \27000  ?
田口たつ    みず...続きを読む

Aベストアンサー

No4の続きです。
I2を順次下にコピペすると I3,I4と自動的に変わりまり、
    右にコピペすると J2,K2と自動的に変わります。
I2 は 相対セルの形式だから。
$I$2 の絶対セルの形式にすれば、どこにコピペしても$I$2は固定のまま変わりません。
詳しくは、参考書で相対セルと絶対セルの違いを確認してください。
G2に入力する式はI2,J2ではなく、$I$2,$J$2の絶対セルで回答してあります。

また
>小生は、IF(C2=100,IF(D2=1,・・・つまりCとDを振り出し銀行ナンバー、支店のナンバーを” ”で囲って(文字認識化)、この双方の文字に当てはまるもの、一つに当てはまるもの、すべてに当てはまらないもの、として選択しようとしていました。

この考え方で概ねよいですが、「一つに当てはまるもの」については
もしも他行の支店番号が貴方のみずほ銀行の支店番号と同じになることがあり得るなら
他行とみずほの判別を追加する必要があると思います。

QEXCEL関数、別シートを参照して値を返したい。。

計算式をセルH3~H14に入れたいです。

セルE3にロジックパターンを1~5まで入力。

関数でセルH3~H14の順位を出したいのですが、関数がわかりません。
セルH3~H14まで同じロジックパターンで順位を出します。

H3の場合、ロジックパターンは「1」、サインNoはG3にあり「7」
ロジックパターンsheetのB列を参照すると、「7」はセルB8にあり、
横にスライドしてA列の順位をみると「5位」です。

この5位という結果をセルH3に出るようにしたいのです。
サインNOは変動します。
(前回の質問を見ていただけるとわかるのですが、G3~G15にも関数が入ってます)

そして、隣のセルに、
1位~4位は★★★
5位~8位は★★
9位~12位は★  の表示をしたいのですが、どうしたらいいでしょうか?

Aベストアンサー

H3にはコレを
=MATCH($G3,OFFSET(ロジックパターン!$A$4,,$E$3,12,),0)

I3にはコレを
=LEFT("★★★",4-ROUNDUP(H3/4,0))

J3にはコレを
=MATCH($G3,OFFSET(ロジックパターン!$A$4,,$E$4,12,),0)

MATCH関数でターゲットとなるG3の数値をロジックパターンから探し、見つかったセルの高さを表示しています。
高さが5のため5位でよいのかと思います。5位と表示したいようであれば表示形式を変えたりCONCATENATEを使ったりしてください。
検索範囲が可変となるため、OFFSETで列をずらしています。

Qエクセル関数に詳しい方お願いします! {=IFERROR(INDEX('反映用(触らない)'!$M$

エクセル関数に詳しい方お願いします!

{=IFERROR(INDEX('反映用(触らない)'!$M$1:$M$92,SMALL(IF('反映用(触らない)'$M$1:$M$92<>””,ROW('反映用(触らない)'!$M$1:$M$92)),ROW('反映用(触らない)'!M1))),””)}
の関数で、反映用(触らない)のシートM1に13-エと入力したら入力先のシートC17に13-エと反映されます。
同じ要領で反映用(触らない)のシートM93に5月10日と入力して入力先のシートB17に反映したいのですができません。反映用(触らない)M93〜M184までのせるで空白のところは詰めて上から入力された順番に反映できるようにしたいのですが皆さんのお力をお貸しください

Aベストアンサー

では基本的なところで 配列数式
https://www.forguncy.com/blog/20170110_arrayformula

一応 ROW,INDEX関数
http://excel.onushi.com/function/row.htm
https://kokodane.com/kansu_lookup_10.htm

配列定数
https://support.office.com/ja-jp/article/%E9%85%8D%E5%88%97%E5%AE%9A%E6%95%B0%E3%82%92%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%AE%E4%B8%AD%E3%81%A7%E4%BD%BF%E3%81%86-477443ea-5e71-4242-877d-fcae47454eb8

範囲を狭めた配列数式の内側から[F9]キーで計算させて、理解を深めるとよいと思います
数式-「数式の検証」でもいいかも

関数IF内の計算結果が、配列で戻ってくるのがこの数式のポイントです。

配列数式のガイドラインと例
https://support.office.com/ja-jp/article/%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%AE%E3%82%AC%E3%82%A4%E3%83%89%E3%83%A9%E3%82%A4%E3%83%B3%E3%81%A8%E4%BE%8B-7d94a64e-3ff3-4686-9372-ecfd5caa57c7?CorrelationId=d66fe414-4ccb-4716-a635-a90f85ed3a5a&ui=ja-JP&rs=ja-JP&ad=JP&fromAR=1
より一部編集、抜粋
配列数式を使用する場合の短所
●Ctrl キーを押しながら shift キーを押しながら Enter キーを押すことを忘れる。
●ブックの他のユーザーは、数式を理解できない可能性がある。
●コンピューターの処理速度とメモリによっては、大きな配列数式を使用すると、計算速度が低下することがある。

※直接的な回答が欲しければ、B17セルにどのような数式を入力したかを返信してください。
エラーの結果だけでは何をしたのかわかりません

では基本的なところで 配列数式
https://www.forguncy.com/blog/20170110_arrayformula

一応 ROW,INDEX関数
http://excel.onushi.com/function/row.htm
https://kokodane.com/kansu_lookup_10.htm

配列定数
https://support.office.com/ja-jp/article/%E9%85%8D%E5%88%97%E5%AE%9A%E6%95%B0%E3%82%92%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%AE%E4%B8%AD%E3%81%A7%E4%BD%BF%E3%81%86-477443ea-5e71-4242-877d-fcae47454eb8

範囲を狭めた配列数式の内側から[F9]キーで計算させて、理解を深めるとよいと思い...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報