痔になりやすい生活習慣とは?

初めて投稿します。よろしくお願いいたします。

EXCEL2000を使用しています。
オートフィルタを使わずに
関数でデータを抽出する方法で悩んでいます。

下記<データ>が存在しています。
2行目がゼロ以外のものを、
別シート<抽出>に上から詰めて抽出したいのです。

<データ>
  A列 B列 C列 D列 E列
1行 1  2  3  4  5  ←日付
2行 5  3  0  2  0  ←数字

<抽出>別シート
  A列 B列 
1行 1  5
2行 2  3
3行 4  2

  ↑  ↑
  日付 ゼロ以外

関数については中級レベルだと思います。
いろいろ試したのですが、関数を組み合わせてもなかなかうまくいきません。
どなたかご教授いただけないものでしょうか?

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

このQ&Aに関連する最新のQ&A

A 回答 (2件)

 <データ> のあるシートを「Sheet1」、<抽出>別シート を「Sheet2」とします。



A)一発で抽出する方法(配列数式)
1)Sheet2 A1 に
=IF(ROW(A1)<=COUNTIF(Sheet1!$2:$2,">0"),INDEX(Sheet1!$1:$1,SMALL(IF(Sheet1!$A$2:$CV$2=0,256,COLUMN(Sheet1!$A$2:$CV$2)),ROW(A1))),"")
をコピペし、[Shift] + [Ctrl] + [Enter] で確定します。
2)Sheet2 B1 に
=IF(ROW(A1)<=COUNTIF(Sheet1!$2:$2,">0"),INDEX(Sheet1!$2:$2,SMALL(IF(Sheet1!$A$2:$CV$2=0,256,COLUMN(Sheet1!$A$2:$CV$2)),ROW(A1))),"")
をコピペし、[Shift] + [Ctrl] + [Enter] で確定します。
3)Sheet2 A1:B1 を選択、下方向にコピーします。

B)二発で抽出する方法
1)Sheet1 A3 に
=IF(A2>0,COLUMN(),"")
と入力し、これを右方向に必要分コピーします。
2)Sheet2 A1 に
=IF(ROW(A1)<=COUNTIF(Sheet1!$3:$3,">0"),INDEX(Sheet1!$1:$1,SMALL(Sheet1!$3:$3,ROW(A1))),"")
Sheet2 B1 に
=IF(ROW(B1)<=COUNTIF(Sheet1!$3:$3,">0"),INDEX(Sheet1!$2:$2,SMALL(Sheet1!$3:$3,ROW(B1))),"")
をコピペします。
3)Sheet2 A1:B1 を選択、下方向にコピーします。
    • good
    • 1

<データ>のあるシートで


 セルA3 : =IF(A2<>0,1,"") (B列以降もコピー。以下同じ)
 セルA4 : =IF(A2<>0,COUNT(A3:$A3),"")
 セルA5 : =A1
 セルA6 : =A2

つぎに、
 A列の10行目から下に、1から順に数字を入れる
 9行B列に「2」、9行C列に「3」を入れる

 セルB10に、 =HLOOKUP($A10,$A$4:$F$6,B$9,FALSE)
 といれてC列もコピー、さらに下にコピーすれば<抽出>の
 ようになります。
    • good
    • 1
この回答へのお礼

shiritaiさん、ありがとうございます。
とても速い回答で驚きました。

難しく考えていたので、これなら単純明快でわかりやすいです。
そうですよね~ 初心に戻った心境です。助かりました。

3行目、4行目に 
行を追加できるかどうか、確認してから試してみようと思います。

完成したらご報告いたします。まずはお礼までです。

お礼日時:2009/04/12 14:10

このQ&Aに関連する人気のQ&A

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

このQ&Aを見た人はこんな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表から0以外の数値を取り出し羅列したい

何から考えていいのか分かりません・・。
Excel200で下記のような表があります。

ID  顧客名 サービス1 サービス2 サービス3・・・・
─────────────────────────
**** AAA     0     0     0
**** BBB     0     0    11
**** CCC     1    16     0
**** DDD     0     0     3
 ・  ・      ・     ・     ・
 ・  ・      ・     ・     ・
 ・  ・      ・     ・     ・

この表からデータが0でないものをピックアップし、
次のような一覧を作成したいのです


顧客名 サービス名 件数
─────────────
BBB サービス3 11
CCC サービス1  1
CCC サービス2 16
DDD サービス3  3

こういったことは可能なのでしょうか?
過去のものも見てみたのですが、うまく合致するものを探せず、
Excelの関数のヘルプを見ても、よくわからず、困ってしまいました。

関数は多少使ったことがありますが、
マクロは使ったことがありません。
ご指導いただける方がいらっしゃいましたらどうぞ宜しくお願い致します。

何から考えていいのか分かりません・・。
Excel200で下記のような表があります。

ID  顧客名 サービス1 サービス2 サービス3・・・・
─────────────────────────
**** AAA     0     0     0
**** BBB     0     0    11
**** CCC     1    16     0
**** DDD     0     0     3
 ・  ・      ・     ・     ・
 ・  ・      ・     ・     ・
 ・  ・ ...続きを読む

Aベストアンサー

関数でもできそうですが、マクロのほうがはるかに簡単にできます。


Sheet1が下のようになっており、

    A   B    C     D     E   ・・・
1  ID  顧客名 サービス1 サービス2 サービス3 ・・・
2 **** AAA   0     0     0
3 **** BBB   0     0    11
4 **** CCC   1    16     0
5 **** DDD   0     0     3   ・・・

これをSheet2に次のように集計してみます。(2行目から集計)

    A   B    C
1 顧客名 サービス名 件数
2 BBB サービス3 11
3 CCC サービス1  1
4 CCC サービス2 16
5 DDD サービス3  3


ツール→マクロ→Visual Basic Editor でVBE画面に移り、 表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示します。
プロジェクトエクスプローラのSheet1をダブルクリック。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

シートに戻り、ツール→マクロ→マクロでこのマクロ(ichiran)を指定して実行します。

ここからコピーします

Sub ichiran()
  Dim rw As Long '調べる行
  Dim col As Integer '調べる列
  Dim outRw As Long '書き出す行

  outRw = 2
  With Worksheets("Sheet2")
    .Cells.Clear
    .Range("A1:C1") = Array("ID", "サービス名", "件数")
    For rw = 2 To Range("B65536").End(xlUp).Row
      For col = 3 To Range("IV" & rw).End(xlToLeft).Column
        If Cells(rw, col).Value > 0 Then
          .Cells(outRw, 1) = Cells(rw, 2)
          .Cells(outRw, 2) = Cells(1, col)
          .Cells(outRw, 3) = Cells(rw, col).Value
          outRw = outRw + 1
        End If
      Next
    Next
  End With
End Sub

関数でもできそうですが、マクロのほうがはるかに簡単にできます。


Sheet1が下のようになっており、

    A   B    C     D     E   ・・・
1  ID  顧客名 サービス1 サービス2 サービス3 ・・・
2 **** AAA   0     0     0
3 **** BBB   0     0    11
4 **** CCC   1    16     0
5 **** DDD   0     0     3   ・・・

これをSheet2...続きを読む

Q初めて0以外の数値が出てきたときの値を表示(EXCEL)

化学系の仕事をしていて実験の値をエクセルでまとめています。
こんなことは出来るのでしょうか?

1分ごとに測定を行いますが、はじめの数分は0の値が続きます。
簡単に例を挙げるとこんな感じです。
【例】
分→測定値
00→0
01→0
02→0
03→0
04→2.36
05→4.83
06→3.52

このとき初めて数値が0以外の値が出たときの2.36の値を適当なセルに表示させるにはどのような方法がありますか?測定値の数値には規則性がないのでMAX関数やMIN関数が使えません。何かいい方法教えてください。

Aベストアンサー

A列にデータがあるとしたら、
=INDEX(A1:A99,MATCH(TRUE,A1:A99<>0,0))
を配列数式として入力(Ctrl+Shift+Enter)
でいかが?

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセル空白以外の文字を抽出

よくある質問なのですが過去の質問をみてもどうも解決できません。
A列の10行から20行まで『名称』として数種類の文字があるとします。その間には空白もあります。
これを100行からある集計表には空白なしで表示したいのです。

A10  あ
A11  い
A12  
A13  う
A14  
A15  え
A16
A17
A18  
A19
A20  お



A100  あ
A101  い
A102  う
A103  え
A104  お

という感じなのですがどうすればできますか。
マクロはできなので関数でお願いします。

Aベストアンサー

A1とすべきところをA!のようにしてしまいました。下記が正解です。

=IF(ROW(A1)>MAX(B$9:B$99),"",INDEX(A$10:A$99,MATCH(ROW(A1),B$10:B$99,0)))

Qエクセルの参照結果を空欄を詰めて表示させたい

エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。
このJの列にある科目で100点を取った生徒のみを別シートのB28から縦に順番に表示させたいです。

基本的な関数を使って、100点を取った人のみ抽出して表示させることはできるのですが、空欄を詰めて順に表示させることができず困っております。
よろしくお願いします。

Aベストアンサー

 もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。

 今仮に、

>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。

の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。

 まず、Sheet2のA27セルに次の関数を入力して下さい。

=Sheet1!$A$2

 次に、Sheet2のA28セルに次の関数を入力して下さい。

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$A:$A,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$A:$A)),0)))

 次に、Sheet2のB28セルに次の関数を入力して下さい。

=IF($A28="","",VLOOKUP($A28,Sheet1!$A:$B,2,FALSE))

 次に、Sheet2のA28~B28の範囲をまとめてコピーして、Sheet2のA29~B51の範囲に貼り付けて下さい。

 以上です。


 因みに、もし、生徒の中に同姓同名の生徒が存在しない事が保障されている場合には、A列を表示させる事なしに、Sheet2のB28セルに次の様な関数を入力してから、Sheet2のB28セルをコピーして、Sheet2のB29~B51の範囲に貼り付けるだけで済みます。(Sheet2のB27セルに、Sheet1のB2セルと同じ値を入力しておく必要はあります)

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$B:$B,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$B:$B,ROWS(Sheet1!$B:$B)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$B:$B)),0)))

 もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。

 今仮に、

>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。

の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。

 まず、Sheet2のA27セルに次の関数を入力して下さい。

=Sheet1!$...続きを読む

QExcel 複数のデータを別シートに上から詰めて表示させたい

Excelで、色々なシートに複数のデータがあります。
それらを一定条件で抽出して(そこは問題ないのですが)
ある1枚のシートに「上から詰めて」1行ずつ表示させたいのですが、
その表示されるべき「複数のデータ」は、時に空欄になっている事も
あるものです。
という事は、「上から詰めて」という条件をかなえるためには、
その表示させたいシートにセルを固定して参照させる訳には
行きませんよね・・・。
かと言って&を使ってみても、関数ではセル内改行させられない・・・
ですよね?

そんな事で悩んでおります。
お知恵を貸してくださる方いらしたらお願いします。

Aベストアンサー

こんにちは。
>それらを一定条件で抽出して(そこは問題ないのですが)
>時に空欄になっている事も
↑これって、抽出方法に問題があるのでは???
現状はどうされているのですか?
>&を使ってみても、関数ではセル内改行させられない・・・
セル内改行は「CHAR(10)」です。
例えば、「="○○○"&CHAR(10)&"△△△"」なら
○○○
△△△
と、1つのセルに表示されます。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Q【エクセル】表から条件に合ったデータを別シートに順番に抽出したい

エクセルの表である条件にあてはまる行を
別シートに順番に抽出されるような関数を教えてください。


シート1のB列にあるデータが"渋谷区"のとき
シート2のE74からE80くらいまでに
シート1のC列のデータを上から順に抽出されるようにしたいです。

【シート1】
A        B         C
1 東京都 渋谷区  PC
2東京都  港区  PC
3東京都 新宿区  マウス
4東京都 渋谷区  ペン
5東京都 渋谷区  モニタ



10

【シート2】

E
74 PC
75 ペン
76 モニタ



80 


【補足】
元の表(シート1)を編集することがあるので
自動的に反映されるよう、関数で作成できたらと思います。
シート2の構成上、抽出後のソートや、フィルタオプションは避けたいです。

わかりづらい文章で申し訳ございませんが
どなたかいいアドバイスがありましたら教えてください。
お礼は明日になってしまいますが、必ずご返答いたします。
よろしくお願いいたします。

 

エクセルの表である条件にあてはまる行を
別シートに順番に抽出されるような関数を教えてください。


シート1のB列にあるデータが"渋谷区"のとき
シート2のE74からE80くらいまでに
シート1のC列のデータを上から順に抽出されるようにしたいです。

【シート1】
A        B         C
1 東京都 渋谷区  PC
2東京都  港区  PC
3東京都 新宿区  マウス
4東京都 渋谷区  ペン
5東京都 渋谷区  モニタ



10

【シート2】

E
...続きを読む

Aベストアンサー

>E74に関数を入力し、その後関数をE80までドラッグすればよいのでしょうか?
いいえ。
1.
=INDEX(シート1!C1:C11,SMALL(IF(シート1!B1:B10="渋谷区",ROW(A1:A10),11),ROW(A1:A10)))&""
の数式を掲示板上でコピーします(シート名は合わせてください)
2. E74:E80セル範囲を選択します
3. 数式バーで[Ctrl]+[V]貼り付け
4. [Ctrl]+[Shift] +[Enter] でセルへの入力を確定させます

>その際、絶対参照や相対参照は必要でしょうか?
セルのコピーはしませんので不要です。
参考まで

Qエクセルで左から、最初に0より大きい値を抽出したい

エクセルで左から始めて、最初に「0」ゼロより大きい値になる(セルの位置の)値を表示したいです
関数でお願いできればと思います。

a,b,c,d,e,f ,g,h,i, j
---------------
0,0,0,1,0,0,0,0,0,0 - この場合、D1の"1"を表示させたい
0,1,2,1,3,4,1,0,0,1 - この場合、B2の"1"を表示させたい
0,0,0,0,1,2,4,5,1,2 - この場合、E3の"1"を表示させたい

少々急いでおります。何卒、よろしくお願いいたします。

補足
エクセル2010です 。

Aベストアンサー

以下のような関数で最初の0より大きな数字を表示できます。

=INDEX(A1:J1,MATCH(1,INDEX((A1:J1>0)*1,),0))

エラー表示をしたくないならIFERROR関数などを使用して適宜数式を修正してください。


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

人気Q&Aランキング