- ベストアンサー
エクセルでこんな関数は?
A2:B30に表1があります。 使用者がこのシートに入力するさまざまな条件により、A列がTRUEかFALSEになります。 表1ではA列がTRUEのとき、B列に文字列が表示されるようにB列に式が入れてあります。 A列がすべてTRUEになることはありません。したがってB列の文字列の表示は飛び飛びというか歯抜けのような表になっています。 このB列に表示された文字列を、表2(別シート)に上から歯抜けが無いように2行目以降に順番で表示させたいのです。 考えたのはどこかのセルに =B2&B3&B4&~略~&B30 として文字列をまとめ、(文字列の区切りがわかるように、それぞれの文字列の頭に※印をつけておく)、さらにこれを※印を目標に分割する関数はないか?ということなのですがわかりません。 あるいは他の方法でもかまいません。ただし、表1をおもてに出したくないので表1をソートするやりかたは使えません。 どうかお教えください。お願いします。
- AQUALINE
- お礼率78% (144/184)
- オフィス系ソフト
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
こんにちは。maruru01です。 仮にシート2のA2から順に表示させるとします。 A2をクリックして数式バーに、 =IF(ROW(A1)>SUMPRODUCT((Sheet1!$B$2:$B$30<>"")*1),"",INDEX(Sheet1!$B$2:$B$30,SMALL(IF(Sheet1!$B$2:$B$30<>"",ROW(Sheet1!$B$2:$B$30)-1,65537),ROW(A1)))) と入力し、数式バーにまだカーソルがある状態で、[Ctrl]と[Shift]を押しながら[Enter]を押します。 うまく行くと、数式の両端に「{}」が付きます。 (このような式を配列数式といいます。) うまくいったら、下の行(最大30行までで十分)へオートフィルコピーします。 なお、[Sheet1]は表1があるシート名です。
その他の回答 (2)
- maruru01
- ベストアンサー率51% (1179/2272)
配列数式については、以下のページを参考にして下さい。 SUMPRODUCT関数についても少し載っています。 http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm 式については端折って説明します。 まず、 IF(Sheet1!$B$2:$B$30<>"",ROW(Sheet1!$B$2:$B$30)-1,65537) では、Sheet1のB列に値があれば(行番号-1)を返し、なければ(""ならば)65537を返します。 それが配列になっているので、 {65537,2,3,65537,65537,6・・・} のような配列が返ります。 なお、65537という数値は、Excelの最大行数+1です。 (つまり、返される行番号より"必ず大きい"ということです。) -1しているのは、B列のデータが2行目から始まっているためです。 で、この配列でROW(A1)番目に小さい値を、SMALL関数で返しています。 ROW関数の引数は相対参照なので、行方向へコピーすると、順にずれていき、先頭行から順に、 1番目(ROW(A1))に小さい値 2番目(ROW(A2))に小さい値 ・・・ となるわけです。 ここで、B列に何も値がない場合(65537)は飛ばして数えられます。 このSMALL関数で返される値は、B列範囲の行位置を表しています。 したがって、INDEX関数を使用してB列の値を参照出来るわけです。 あとは、下のほうの行がエラーにならないように、IF文の条件式で、 ROW(A1)>SUMPRODUCT((Sheet1!$B$2:$B$30<>"")*1) としています。 ここで、SUMPRODUCT関数は、B列範囲で値がある個数を返していますので、それ以上の行はいらないということです。
お礼
ご丁寧にありがとうございました。 じっくり勉強します。 たすかりました。感謝感激です。
- MSZ006
- ベストアンサー率38% (390/1011)
表1にも少し加工が必要ですが、次の方法でもできると思います。 表1(Sheet1にあるとします)のA列の前に1列追加します。(もともとの表1の範囲はB2:C30になります) A2に、 =IF(C2<>"",COUNTIF(INDIRECT("B2:B"&ROW()),TRUE),"") と入力し、オートフィルでA30までコピーします。 (A列は非表示にしてしまってもよいと思います。) 表2(別シートのA2から始まるとします)のA2に、 =IF(ISNA(VLOOKUP(ROW()-1,Sheet1!$A$2:$C$30,3,FALSE)),"",VLOOKUP(ROW()-1,Sheet1!$A$2:$C$30,3,FALSE)) と入力し、オートフィルでA30までコピーします。
お礼
ありがとうございました。 これはわかりやすいようですね。 今回はNo1さんの方法でやりましたが勉強してみます。
関連するQ&A
- エクセルの関数について
選択範囲内にA文字列が2つある時またはAが1つ、Bが1つある時はtrue,ないときはfalse これを表示するにはどのようにすればよいですか?
- ベストアンサー
- オフィス系ソフト
- excelで文字列を検索する関数を教えてください。
いつもお世話になります。 エクセルの関数で、シートAのセルA1に入っている文字列をシートBのC列から探し出し、同じ文字列があればTrueをなければFalseを返す関数を教えてください。 わからないながらも色々調べてみたのですが、検索にはVLOOKUPがよく使われているようですが、シートBのC列は、データの並べ替えができないので、VLOOKUPは、使えないのです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル 関数によるデータの比較について
エクセルで、文字列の比較の行い方を教えてください。 (1)A列のA1~A100まで文字列を貼り付けます。 (2)C列のA1~A100までにも文字列を貼り付けます。 ここで、A1の文字列がC1~C100までの どこかにあれば B1がTRUE どこにもなければ B1はFALSE となるような比較を行いたいのです。 B1~B100は、どのような関数を使えば良いのでしょうか? ちなみにEXCELは2000です。
- ベストアンサー
- オフィス系ソフト
- エクセルでHLOOKUP関数の選択範囲について
エクセルでHLOOKUP関数を使って、検索したいのですが、 シートは、一覧表のシートと データが入っているA101、B203、C305、...シートは300シートくらいあります。 一覧表のシートには、下のような表になっていて、 A列 B列 C列 D列 ... 1003、1004、1005、1006、... 2行目 A101 3行目 B203 4行目 C305 . . . データのはいっているシート、A101は下の表になっています。 B列 C列 D列、・・・、Z列 2行目 1004、1005、1006、... 3行目 100、 200、 150、... 一覧表のB列の2行目には HLOOKUP(B2、シートA2のB2:Z3、2行目、FALSE) という感じで、シート名をセルA2のものを参照にして 探して表示させ、B列、C列、D列の2行目から下の行も 表示させたいのですが、うめくできませんでした。 INDIRECT関数を使ってみましたが、セル範囲が無効という エラーがでてしまいます。↓こんな感じで入力してみたのですが... SUMPRODUCT((INDIRECT($A2&"!$B$2:$Z$3"))=$B$1,(INDIRECT($A2&"!$B$2:$Z$3"))) 1つづつデータを見て手打ちはデータが多く、 どんどんデータが増えていくので できれば関数を使って表示させたいと思っています。 詳しい方いらっしゃいましたら、どうか教えてください よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルの検索関数について
エクセルの検索関数について、以下のことで教えてください。 以下のような甲~丁製品データが並んでいます。B列にはチェックボックスを配置し、例えば「丙社製品」を選ぶ場合にB3にあるチェックボックスにチェックしてA3セルが「TRUE」となるようにセットされています(チェックしたチェックボックスは■で表現しています)。チェックされる(TRUEとなる)製品は一つだけです。 <シート名:製品リスト> A B C 1 FALSE □ 甲社製品 2 FALSE □ 乙社製品 3 TRUE ■ 丙社製品 4 FALSE □ 丁社製品 そこで、『本シート内A1~A4中の「TRUE」に該当する製品名(C列の何れか)を拾って』別シートのあるセルに「○社製品」と掲載したいのです。 IF関数でも可能なのですが、製品が7つ以上の場合には使えません。VLOOKUP関数をやってみましたが、A列の「TRUE」「FALSE」がランダムになる為、「昇順」とならず、エラーが出てしまいます。 単純なことなのかもしれませんが、壁に当たっています。 どなたか分かる方がいましたら教えてください。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数について
検索に使用する関数についてお伺いいたします。 表を二つ作成しました。 シート1には特に項目の指定がなく、番号の若いものから順に左から右に並べた24×8の表があります(表1とします)。 シート2にはいろいろな項目を指定した表があり、B列にランダムに数値が入っています(表2とします)。 表1の範囲に表2のB列の値が入っている場合、表2のA列に◎や*などのなんらかのしるしをしたいのですが、どのような関数を使用すればよいでしょうか? わかりにくい文章で申し訳ありませんが、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数でワイルドカードを使いたいのですが・・・
A列3行目から100行目までのセルで文字列○を含むものの合計は =COUNTIF(A3:A100,"*○*")で求めることが出来ますね。 同様にB列にも文字列○を含むセルをカウントする場合、 =COUNTIF(B3:B100,"*○*")で求められます。 では同じ行で、A列B列共に○を含むものを集計しようとして、C列に =IF(AND(A3="*○*",B3="*○*"),TRUE)とし、TRUEを合計しようとしたところすべてFALSEになってしまいました。 なぜワイルドカード(*)が使える場合と使えない場合があるのでしょうか? またどうやれば同じ行でA列B列共に○を含むものを集計できるのでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセルについて(関数)
A列に日付を入力することによって、B列に1から3の数字を表示するようにしています。 B列の関数は「=IF(B3>=2008/4/1,(MOD(B3-"2008/4/1",3)+1))」です。 A列未入力の時に「FALSE」の文字が表示されてしまいます。 今まで皆様に教えていただいたものを応用して、やってみましたが、うまくいきません。 A列未入力時に、何も表示しなくするにはどうしたら良いのでしょうか。 よろしくお願いします。 いつも、ありがとうございます。
- ベストアンサー
- オフィス系ソフト
- Excel 関数
初心者です条件にあったものを参照する数式を教えてください よろしくお願いいたします! ~sheet1~ A列 B列 回数 金額 3,000以下 29,800 3,000以上 39,800 4,000以上 49,800 上のようなsheet1に表があり sheet2にA列に下のように1の表示がある場合のみ sheet1の条件にあった金額を sheet2のC列に表示させる数式を教えてください。 A列 B列 C列 回数 金額(円) 1 5O,712 49,800 1 133 29,800 1 3,014 39,800 3,014 (計算しない) わかりにくい説明かもしれませんが… すみませんがよろしくお願いいたします。
- ベストアンサー
- Windows XP
お礼
ありがとうございます! 出来ました。驚きです。 しかし、式の意味がさっぱりわかりませんのでわたしには他に応用がきかないと思います。 よろしければ式の意味を解説していただけないでしょうか? もちろんIF、ROW、INDEX、SMALL関数の意味はわかりますが、配列数式は使った事がなくおぼろげな知識です。SUMPRODUCTもよくわかってはいません。したがって全体の意味がほとんど理解できないんです。 勝手を言ってすみません。