エクセル関数式の応用：3万行までのデータの取得方法
- エクセル関数式を使って、3万行までのデータを取得する方法について教えてください。
- エクセル2010を使用しています。画像の様なデータがあり、特定の条件に基づいてデータを取得する式を教えてもらいました。しかし、データが7行目までしかない場合は問題ありませんが、3万行までのデータを取得しようとしてもうまくいきません。
- 具体的な式は「=IFERROR(INDEX($B$1:$B$30000,SMALL(IF($A$1:$A$30000=C$1,ROW($A$1:$A$30000)),ROW(A1))),"")」です。詳しい方に教えていただきたいです。
- ベストアンサー
エクセル 関数式の応用
こんにちは エクセル2010を使っています。 画像の様なデータがあって、次のような式を教えてもらいました。 =IFERROR(INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=C$1,ROW($A$1:$A$7)),ROW(A1))),"") 画像では A列 B列の データが7行目までなのですが、これを3万行まで広げようと次のような式に変更してみましたが上手く行きません。（空白が返される） =IFERROR(INDEX($B$1:$B$30000,SMALL(IF($A$1:$A$30000=C$1,ROW($A$1:$A$30000)),ROW(A1))),"") 詳しい方、教えていただけませんか？ よろしくお願いします。
- gekikaraou
- お礼率41% (282/678)
- Excel（エクセル）
- 回答数2
- ありがとう数2
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
>面白い？事に 15000行なら抜き出してくれて、20000行はダメなんです。 実際にセル番地を間違えていないなら、「INDEX($B$1:$B$30000,SMALL(IF($A$1:$A$30000=C$1,ROW($A$1:$A$30000)),ROW(A1)))」の部分がエラーを返しているパターンになります。 すなわち、A15000:A20000の行にエラー値（#N/Aや#VALUEエラーなど）が含まれているものと思われます。 A15000セルまでなら正常な値を返すなら、A15001セル以下を選択してDeleteしてみてください。 ＃ちなみにこのようなデータ範囲の大きい配列数式を多数のセルに入力すると、シートの再計算に時間がかかり、シートの動きが重くなるなど、実用上の問題がでますので、オートフィルタやフィルタオプションの設定を利用されたほうが良いと思います（この操作をマクロの記録をしてコードを一部編集すれば、簡単にマクロ化することもできます）。
その他の回答 (1)
- bunjii
- ベストアンサー率43% (3589/8248)
提示の数式はSMALL関数の範囲（配列数）をIF関数で前処理していますので数式の確定時にCtrlとShiftを押しながらEnterを打鍵してください。 確定結果を数式バーで確認すると数式全体を｛=数式｝のように大かっこで括られていますので違いが分かります。
お礼
回答ありがとうございます。 配列数式ですよね、ですから確かにCtrlとShiftを押しながらEnterを打鍵しています。 （複数回やってみましたが、結果は変わりません） そして、7行に戻すとちゃんと抜き出してくれるんです。 A列 B列のデータは今のところ、21000行あって今後増えていく予定ですが、面白い？事に 15000行なら抜き出してくれて、20000行はダメなんです。
関連するQ&A
- エクセル関数について
Ａ列とＢ列に氏名を入れてＡ列にあってＢ列にないものを取り出す為に=IFERROR(INDEX($A$2:$A$8,SMALL(IF(ISNA(MATCH($A$2:$A$8,$B$2:$B$8,0)),ROW($A$2:$A$8)-1),ROW()-1),1),"")の関数をⅮ列に入れ下にオオートフィルしたのですが、思うように表示されません。 関数が間違っているのか判りません。 何か良い方法はあれば教えてください。
- ベストアンサー
- Excel（エクセル）
- Excelの関数式について
ExcelのB列（B2:B50）にフラグ「０」または「１」が入力されています。 フラグ「０」が入力されている行番号を関数を使用して取得したいと思っており、 下記関数でできるのですが、フラグ「0」が複数連続で続いた場合は、１つ目だけの 行番号を取得したいのですが、どのように変更したらよいのでしょうか。 D列に下記関数式を入力して、以下オートフィルしています。 =IF(COUNTIF(B$2:B$50,0)<ROW(A1),"",SMALL(INDEX((B$2:B$50<>0)*10^3+ROW(B$2:B$50),),ROW(A1))) 例えば、下記のようば場合は、行番号「2、5、8」を取得したいです。 A B 1 -1 2 0 3 -1 4 -1 5 0 6 0 7 -1 8 0 9 -1 ・ ・
- ベストアンサー
- その他(業務ソフトウェア)
- ExcelのSMALL関数について
ExcelのB列（B2:B50）にフラグ「０」または「１」が入力されています。 フラグ「０」が入力されている行番号を関数を使用して取得したいと思っています。 B列のフラグは順次更新されており、フラグ「０」の個数は複数あります。 下記の関数式（以下多めにオートフィル）で上記のことができるようになったのですが、 =IF(COUNTIF(B$2:B$50,0)<ROW(A1),"",SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1))) SMALL関数式の部分↓↓の意味がよくわかりません。 SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1)) どなたかご教授いただけないでしょうか。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセルでお聞きします。
こちらのサイトの回答を参考に、セルに下記のような式が入れました。 他のシートや他のセルにも同様の式が入っているのですが割愛致しました。 通常Sheet1のＢ列は空白なんですが、もし何か文字が入っていたら その行に関してはこの式を反映させない方法を教えて下さい。 （Sheet2） B列 =IF(ROW(E1)>COUNT('Sheet1'!$E$4:$E$505),"",INDEX('Sheet1'!E$1:E$505, SMALL(INDEX(SUBSTITUTE(('Sheet1'!$E$4:$E$505<>"")*1,0,10^5)*ROW('Sheet1' !$E$4:$E$505),),ROW(E1)))) D列 =IF(ROW(C1)>COUNT('Sheet1'!$E$4:$E$506),"",INDEX('Sheet1'!C$1:C$505, SMALL(INDEX(SUBSTITUTE(('Sheet1'!$E$4:$E$505<>"")*1,0,10^5)*ROW('Sheet1' !$E$4:$E$505),),ROW(C1))))
- 締切済み
- オフィス系ソフト
- INDEX関数を使用した関数の意味を教えてください。
INDEX(sheet2!C:C,SMALL(INDEX((sheet2!$B$1:$B$100<>$B$18)*10^4+ROW($1:$100),),ROW(A1)))&"" sheet2にある表のB列に検索時に使用する文字列があり、sheet1のB18にその検索したい文字列を入力すると、一致したB列と同じ行のC列のデータを抽出する式です。 表のデータは今後も増加しますが、どの程度増加するは不明です。 INDEX関数やSMALL関数など一つ一つの関数の意味はわかっているつもりですが、なぜ上記のセルが選択されているのかわかりません。 特にSMALLの後の式がわかりません。 エクセル関数にはあまり詳しくないので、この式の意味をできれば詳しく教えていただけないでしょうか？ （式の意味は人から聞いたものですが、違っていたらすいません）
- ベストアンサー
- オフィス系ソフト
- エクセル、毎回2づつ増えいくデーターの式？
よろしくお願いいたします。エクセルで別表からA2、B2に ともに145行までデーターが入っていて、C1に各当するものをA列から引っ張ってくる式が下の数式です。 【=IF(ROWS($2:2)>COUNTIF($A:$A,$C$1),"",INDEX($B:$B,SUMPRODUCT(ROW($A$2:$A$145)*($A$2:$A$145=$C$1)*(COUNTIF(OFFSET($A$2,,,ROW($A$2:$A$145)-ROW($A$2)+1),$C$1)=ROWS($2:2))))) 】 行数が一定で、中だけ数値が変化する分にはいいんですが、毎回2行分データーが増えていきますので、毎回A145の式を147に変えて、手数がかかって、なんかいい式がないもんかとのお伺いなんです。 よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- INDEX関数SMALL関数を使って空白行を詰める
シート1にINDEX関数SMALL関数を使って空白行を詰める表を作成したのですが詰めた表をシート2に表示することはできないのでしょうか。 =IF(COUNTIF($A4:$A4,0)=0,MAX(D$3:$D3)+1,"") =IFERROR(INDEX(A:A,MATCH(ROW()-3,$D:$D, )),"")この表をシート2に表示する方法
- 締切済み
- Excel（エクセル）
- エクセルの空白を詰めて別シートに表示
sheet１のデータを参照して、空白を詰めてsheet２へ表示させたいんです！ sheet１（元データになるもの） A列 ｜B列｜C列・・・ １行目A｜100｜100 ２行目B｜ ｜200 ３行目C｜100｜ ４行目D｜ ｜200 ５行目E｜100｜ ６行目F｜ ｜100 ・ ・ sheet２（sheet１でB列に入力があるものを抽出） A列 ｜B列 １行目A｜100 ２行目C｜100 ３行目E｜100 sheet３（sheet１でC列に入力があるものを抽出） A列 ｜B列 １行目A｜100 ２行目B｜200 ３行目D｜200 ４行目F｜100 sheet2のA1に下記の式を入力してA列とB列に数式をコピー =IF(COUNT(Sheet1!$B$1:$B$6)<ROW(A1),"",INDEX(Sheet1!A$1:A$6,SMALL(IF(Sheet1!$B$1:$B$6<>"",ROW($A$1:$A$6)),ROW(A1)))) すると下記のように表示されます。 A1＝A B1＝１００ A2＝＃ＮＵＭ！ B2＝＃ＮＵＭ！ A3＝＃ＮＵＭ！ B3＝＃ＮＵＭ！ ４行目から空白 sheet２のA２のところにエラーが出ていますが、「関数の引数」のところで「数式の結果」には「１００」と 正解が表示されています。（Ｂ２、Ａ３、Ｂ３も同様に）数式の結果のところには正解が表示されています。 答えの「＃ＮＵＭ！」のところに正解を表示させるには、どうしたらいいですか？ 見よう見まねでつくったのもで。。。関数にあまり詳しくありません。 よろしくお願い致します。
- ベストアンサー
- その他MS Office製品
- エクセルの関数について。
エクセルの関数。 Sheet1の作業列1のA2セルに =IF(COUNTA(Sheet2!2:2)=0,"",IF(Sheet2!A2="",A1,Sheet2!A2)) →数式1とする 作業列2のB2セルに =IF(A2=$D$2,ROW(),"") →数式2とする という数式を入れA2・B2セルを範囲指定 → B2セルのフィルハンドルで下へコピーしておきます。 （Sheet2の行数以上コピーしておく） そしてE2セルに =IF(COUNT($B:$B)<ROW(A1),"",INDEX(Sheet2!B:B,SMALL($B:$B,ROW(A1)))&"") →数式3とする という数式を入れオートフィルで列方向・行方向にコピーする。 教えて頂きたいことは、数式1、数式2、数式3が何を意味しているのか。 と、この数式を使って、表を作成したのですが、ある特定の数字（コード、Ｄ2セルに入力）を入力しても反応してくれない。 （Ｅ2～Ｈ2まで、空白セルの状態になります。） （sheet2は約1200行あります。） 教えて頂けないでしょうか？
- ベストアンサー
- オフィス系ソフト
- エクセル
表示するコードを教えてもらい画像のように作成できることが できたのですが A２ =IFERROR(INDEX(($A$1&$B$1&"1日")*1+ROW($A$1:$A$31)-1, SMALL(INDEX(MOD(WEEKDAY(($A$1&$B$1&"1日")*1+ROW ($A$1:$A$31)-1,1),2)*50+ROW($A$1:$A$31),0),ROW(A1))),"") B２＝A2で作成しています 2月の時だけ次の月の最初の部分が出ることがあります どうにかならないでしょうか？
- ベストアンサー
- Excel（エクセル）
お礼
回答ありがとうございます。 まさにそれでした、エラーが含まれていました。 エラー行を削除したら正常に動いています、助かりました、ありがとうございます。