「忠犬もちしば」のAIボットを作ろう!

おはようございます!

画像の表①(Sheet1)から条件に合うデータを抽出し、
表②(Sheet2)にデータを上から並べる方法を知りたいです。
できればマクロではなく関数でお願いします。

具体的には、
「『収入1』が0ではない」を条件に表①から条件に合うデータを抽出し、
表②に、「名前」と「収入1」のデータを上から並べたいです。
(「収入1」の数の大小関係なく、表①のデータを上から抽出した順に並べたいです。)

最初は配列数式でやってみたのですが、
表①が参照されたデータからできている点と、
抽出先の表(②)に結合されたセルがある場合があるため、できませんでした。
参照と結合に関しては書式で決まっていること等もあり変えられません…

そこで、VLOOKUP関数が使えるかなと思ったのですが、、、

「0ではない」の条件は検索値にはなれないので、
表①の左端に新しい列を挿入し、
【「『収入1』が0ではない」が正なら「○」、負なら空白】のIF関数を入力しました。
その「○」をVLOOKUP関数の検索値としてやってみたのですが、これも上手くいきませんでした。
(なぜか名前「えええ」が一番上に抽出されました。収入の値が最小だから?)

また、VLOOKUP関数で仮に名前「あああ」が一番上に抽出できたとしても、2番目以降のセルに入れる式がわかりません。



私がやろうとしていること、関数でできるのでしょうか。
どなたかご教示ください。

なお、VLOOKUP関数にこだわりはありません。他の関数でできるのであればそちらでやります。
また、ここでは「収入1」を条件に「収入1」と「名前」を抽出していますが、
場面によっては条件も変わり、また、他の項目の抽出もしたいので、応用できるものだとありがたいです。
(例えば、「収入2」を条件に「名前」「住所」の抽出など。)


大変長々と失礼いたしました。
よろしくお願いいたします。

「【配列数式を使わずに】表から複数のデータ」の質問画像

質問者からの補足コメント

A 回答 (2件)

こんにちは



方法はいろいろあると思いますので、以下に一例を示します。

>【配列数式を使わずに】
とのことですので、作業列を用います。
添付図では仮にI列としていますが、離れたところでも非表示の列でもかまいません。
上段が元となるデータ、下段が抽出結果の表を示しています。

まず、作業列のI2セルに
 =IF(D2>0,COUNT(I$1:I1)+1,"")
を入力して、下方にフィルコピーしています。
これによって、条件式(今回はD2>0)に該当する行だけ、上から順に連番が振られます。

おわかりのことと思いますが、抽出結果の表では、この作業列の値を1から順に検索してゆけば良いことになります。
作業列が表全体の左側にはないので、VLookupではなく、MatchとIndexの組み合わせで参照することになります。

抽出する表のA2セルには
 =IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!I:I,0)),"")
B2セルには、
 =IFERROR(INDEX(Sheet1!D:D,MATCH(ROW(A1),Sheet1!I:I,0)),"")
の式を入力して、下方にフィルコピーしています。

式中の「Sheet1!A:A」、「Sheet1!D:D」の部分が参照したい対象の列を示していていますが、あとの部分はまったく同じ式になっています。
「【配列数式を使わずに】表から複数のデータ」の回答画像2
    • good
    • 1
この回答へのお礼

できました!
元の表の情報量が多く構造も複雑だったため、4ヶ月くらいずっと苦戦して、もうできないのかと思っていました。
私の「やりたいこと」を形にしていただき本当にありがとうございました!

お礼日時:2019/01/18 21:04

》 配列数式でやってみたのですが、


》 …点と、…ため、できませんでした
と仰ってますが、参考までにその出来なかった「…点」と「…ため」を含む具体例を教えてください、配列数式で試してみたいので。

参考までに、お示しの表①と表②を利用して、「配列数式でやってみた」結果を示しておきます。(添付図 by Excel 2013)

1.範囲 A1:E6 選択 ⇒ Alt+MC ⇒ “左端列”だけにチェック入れ ⇒ [OK]
2.セル A1 をセル H5 にコピー&ペースト
3.セル I5 選択 ⇒ Alt+AVV で、下記の[データの入力規則]を設定後、
 ̄ ̄“収入1”を表示させておく
 ̄ ̄ ̄ ̄入力値の種類: “リスト”
 ̄ ̄ ̄ ̄元の値: =$A$4:$A$6
3.セル H5 に次式を入力
 ̄ ̄=IFERROR(INDEX(INDIRECT(H$5),,SMALL(IF(INDIRECT($I$5)>0,COLUMN(INDIRECT($H$5))),ROW($A1))-1),"")
 ̄ ̄【お断り】上式は必ず配列数式として入力のこと
4.セル H5 を右隣および下3行にオートフィル
「【配列数式を使わずに】表から複数のデータ」の回答画像1
    • good
    • 0
この回答へのお礼

丁寧なご回答ありがとうございます!

「具体例」ですが、
まず、「表①が参照されたデータからできている点」ですが、
「表①」は、もともと別の表(表⓪とします)のデータを参照した(リンクさせた)ものです。
参照している理由は、表⓪の作りが複雑なためです。表⓪は、田んぼの「田」の字のような形の所謂「テーブル・表」ではなく、画像(補足参照)のような「入力フォーム」風のもので、しかもこれが複数人分あります。そのため、同じ「氏名」のデータでも一人一人のデータが離れた場所にあったり、一行にひとつのデータがある行もあれば、一行に複数のデータが含まれる行もあったりで、配列数式での範囲指定などが上手くできませんでした。
そこで入力データを作業用のシンプルなテーブルにリンクさせて、それを配列数式に利用しようと思いましたが、結果が「0」になり、できませんでした。(配列数式を解説しているサイトに、参照されたデータは配列数式が使えない旨が記載されていました。ですが、どこのものか思い出せず、見つけることもできませんでした…。)

次に、「抽出先の表(②)に結合されたセルがある場合がある」ですが、
一度実験的に、リンクを用いていない実験用のテーブルで配列数式を使ってみたときに、Ctrl+Shift+Enterで確定させたところ、「配列数式は結合されたセルに使えません」というような注意書きが出ました。配列数式を使いたかったセルは書式で決まっている様式であったため、どうすることもできず、諦めました。

お礼日時:2019/01/18 21:50

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

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

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

Q何故このようなプログラムだとエクセルに書き込めないのか教えてください。

何故このようなプログラムだとエクセルに書き込めないのか教えてください。

Aベストアンサー

私もPythonは研究を始めたばかりですが、まさかopenpyxlとかのExcel操作ライブラリがインストールされてないなんてオチはないですよね?

あとsheet1.write(str… ってなってますが、ここはセル番地の指定だからstrでは文字列になっちゃうのでまずいと思います。変数countは数値なんだから、そのまま sheet1.write(count, 0 ,str(inputvale)) でいけるのでは?

Q「(アクサングラーブ)キー」とは何ぞや?

添付図の記述は、Excel 2013 のヘルプからの抜粋です。
赤枠内にある「(アクサングラーブ)キー」とはキーボード上のどのキーのことか教えてください。
ついでに、もしご存知なら英語のスペルもお願いします。

Aベストアンサー

ショートカット キーで数式の表示/非表示を切り替えるには
Excel 2003、2007、2010 共通です。
[Ctrl] キー + [Shift] キー + [@] キー
Excel 2003 の [ワークシート分析モード] のコマンドには、[Ctrl] + [`] と表示されており、
Excel 2007/2010 の [数式の表示] ボタンにマウス ポインタを合わせるとショートカット キーとして [Ctrl + [Shift] +[`] と表示されます。


なのになぜ [Ctrl] キー + [Shift] キー + [@] キー と書いたかというと、、、
そのほうが覚えやすいかな、と思ってのことです。どれも間違ってはいないのですよ。
なぜなら、106 日本語 キーボードで [`] (アクサン グラーブ) を入力するには
[Shift] キーを押しながら [@] のキーを押す必要があるのです。

↓に よると、
だそうです。

http://cblog.crie.jp/excel/129/

Qエクセル

紙の書類をスキャンしてパソコンに取り込んだ後
そのスキャンデータを計算できるようにするのは可能なんでしょうか??
教えて頂けると幸いです。

Aベストアンサー

一般論ですが
スキャンしたデータは画像になります。
画像をテキストに変換したのちに(フォーマットにもよりますが)、計算できるようにすることは、可能です。
但し、画像からテキストに変換するとき、100%正しく変換される保証はありません。
(誤変換された箇所は人間が検証して直接修正する必要があります)
下記URLは、画像をテキストに変換する1例です。
https://support.google.com/drive/answer/176692?co=GENIE.Platform%3DDesktop&hl=ja

「画像をテキストに変換」で検索を行えば、いくつかヒットするのでそれらを参考にしてください。

Qエクセルの使い方初心者です 数値が一定の数に満たないものを切り捨てて、残った数値の合計を出したいので

エクセルの使い方初心者です



数値が一定の数に満たないものを切り捨てて、残った数値の合計を出したいのですが最適な関数を教えてください

Aベストアンサー

こう言う事ですよね?

ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3BbssbMqmfD5tQ8h

尚、
ファイルは 必ず、
1度 エクセルで、
開き、
ローカル保存してください、

ローカル保存で なければ、
意味が 無いです。


そうすれば、
閲覧も、編集も、
叶うものと 思います。

Q万年カレンダーについて。

次のexcelのデータで、おかしなところがありましたら、教えていただけると幸いです。
https://fire.st/9Il0GsR

Aベストアンサー

>確かに35行は、緑になりました。どうしてそうなるのでしょうか?教えていただけると幸いです。
添付の画像を参照ください。
2018年6月の例ですが、C35へ
=COUNTIF(祝日,A35)
と入力しています。結果は、1048470が表示されます。
これは、=COUNTIF($Y:$Y,A35)と同じことです。(祝日の範囲がY列全体の為)
A35は空白なので、Y列の空白の部分がカウントされます。その為、1048470になります。
COUNTIF(祝日,A35)の結果が0でなければ、判定は真なので、色がつく条件式が成立します。
そのため、緑になります。

>すみません。COUNTIF 関数のみで、祝日に色をつけるとするとどうすれば良いのでしょうか?教えていただけると幸いです。
祝日の範囲を、空白を除いて、正確に定義しなおします。
一旦、祝日の名前を削除し、実際に祝日の日付が入力されているセル範囲Y3:Y107を、祝日の範囲にします。
(つまり、Y列全体をやめて、$Y$3:$Y$107にします)
そうすると、COUNTIF 関数のみで祝日に色をつけることができます。
祝日の名前を削除する方法は、下記を参照ください。(削除せず、変更することも可能です)
https://www.wanichan.com/pc/excel/2016/8/47.html

COUNTIF 関数のみで、祝日に色をつけるようにした場合は、祝日の追加が発生すると(2023年以降分の祝日の追加など)、Y列の範囲が変わるので毎回、それにあわせて、祝日の範囲もかえる必要があります。

>確かに35行は、緑になりました。どうしてそうなるのでしょうか?教えていただけると幸いです。
添付の画像を参照ください。
2018年6月の例ですが、C35へ
=COUNTIF(祝日,A35)
と入力しています。結果は、1048470が表示されます。
これは、=COUNTIF($Y:$Y,A35)と同じことです。(祝日の範囲がY列全体の為)
A35は空白なので、Y列の空白の部分がカウントされます。その為、1048470になります。
COUNTIF(祝日,A35)の結果が0でなければ、判定は真なので、色がつく条件式が成立します。
そのため、緑になります。...続きを読む

Qエクセルについて。

次の数式を求める関数(下の方のプラン表)は、どんな数式を打てば良いのでしょうか?教えていただけると幸いです。

Aベストアンサー

単純な足し算ですよね。
添付画像のように作ったとして

[C8]=SUM($B$2,C$7,$B8)

これを[C8:F12]の範囲にコピーすればOK。

通常、数式のセルは、コピー貼り付けすると、移動先に応じてセルアドレスが変わってしまいますが、
「$」をつけることで、固定することができます。

上の式だと、どこに移動しても

$B$2 は B2 を参照するように固定される。

C$7 は 7行目 を参照するように固定される。
(式を下にコピーしても、7行目に固定される)

$B8 は B列 を参照するように固定される。
(式を右にコピーしても、B列に固定される)

ということになります。
$をつけて参照先を固定するのを「絶対参照」、
つけていない普通の場合は「相対参照」、
と言います。
より詳しいことは、このキーワードで検索して調べてみてください。

金額表示については、セルの書式から、
分類:通貨
記号:\
にしてあります。
実際のセルに「\」は入力されていません。

Q飛行機の形について。

飛行機の写真の形を、下を白い雲の形にするには、どうすれば良いのでしょうか?教えていただけると幸いです。
https://oshiete.goo.ne.jp/qa/10845425.html

Aベストアンサー

あなたは、回答者の名前などいちいち覚えていないのかな?
あちこちで同じ質問を繰り返しているから、混乱するのですよ。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12199512557
回答の内容を試して、理解できない部分だけを補足すれば、的確な
回答も得られるでしょうに。(まぁ、無理だと思うけど)
https://oshiete.goo.ne.jp/qa/10845425.html
他の掲示板の回答内容を、お礼欄にそのまま書き、その内容を問う
のもどうかと思う。(私の回答内容を無視していると思えるから)
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12199512557

[透明色を指定]については、今回はありえないと思う。
飛行機自体の色にも雲の色と同じ色があるので、その部分も透明に
なってしまうから。だから無意味な回答ですね。
(他の回答者を批判するつもりはないが、混乱の元なので否定)
印刷されたものとして、白色は用紙の色を使い印刷されない部分と
なっています。ですから、雲の色は印刷されないので凸凹に見える
だけだってことを理解してください。

これ以上のやり取りは、無意味なので質問を閉じてくださいね。
私としても、そのほうが幸いです。

あなたは、回答者の名前などいちいち覚えていないのかな?
あちこちで同じ質問を繰り返しているから、混乱するのですよ。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12199512557
回答の内容を試して、理解できない部分だけを補足すれば、的確な
回答も得られるでしょうに。(まぁ、無理だと思うけど)
https://oshiete.goo.ne.jp/qa/10845425.html
他の掲示板の回答内容を、お礼欄にそのまま書き、その内容を問う
のもどうかと思う。(私の回答内容を無視していると思えるから)
https://detail...続きを読む

Qexcelで小数点以下の数字のみ表示したい

いつもどうも有難うございます。

小数点第3位の数値ばかりの表を作成しています。
0.111 の時、ゼロを表示せずに、.111と表示させたいのですが、どうしたら良いか教えていただけますでしょうか。

Aベストアンサー

》 0.111 の時、…、.111と表示させたい
セルの書式を
#.000
とすればOK!

Qエクセル 特定の文字がある複数の行に関数を入力したい

VBAについて教えてください。

A列に”WH”という文字が含まれていたら、
I列に”=VLOOKUP(A5,[材料使用予定表マクロ.xlsm]フタミ箱集計!$E$3:$O$200,6,FALSE)”
という関数を入力したいです。

[材料使用予定表マクロ.xlsm]フタミ箱集計は入力したいブックとは別のブックです。
A列に”WH”は複数あります。
全データは2000行あります。

ど素人が調べてやってみても時間が過ぎるばかりでした・・・
お力を貸してくださいm(__)m

Aベストアンサー

もしくは、IF関数でA列のセルに”WH”という文字が含まれていたらVLOOKUPの式、そうでなければ空白(か、お好みの結果)という式を全部に入れるとか。

>ど素人が調べてやってみても
どういうことをやったみたのかくらいは書いていただきたいですね。

Q平均年齢も出し方がわかりません。

町内旅行会の名簿作成中です。氏名、住所等は問題ありませんが、
年齢は生年月日から計算式で1つのセルに○○歳○○ヶ月まで出来ましたが、平均年齢がいろいろネットで調べながら計算しましたがエラー表示されて上手く出来ません。ご指導をお願いします。

Aベストアンサー

> ○○歳○○ヶ月

それは年齢とは言いません。余計なことをせず素直にdatedif(生年月日, 基準日, "Y")で年齢を算出していれば、何の問題もなく平均を計算可能です。


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

人気Q&Aランキング