- 締切済み
excel 条件検索後、空白を除いて整列
お忙しいところありがとうございます。エクセルで以下のような表があるとします。 列 名前 生年月日 年 500歳以上 浦島太郎 1700/8/17 datedif関数 =if(c1>500,c1,"") かぐや姫 1400/9/6 datedif関数 =if(c2>500,c2,"") 一寸法師 1500/1/1 datedif関数 =if(c3>500,c3,"") : : : : と、いうような感じで、名前がずらっと200以上(数は毎回可変)は並んでいます。 そして、年齢をdatedif関数で割り出し、さらにそれがある基準を超えたらその年を表示、超えなければ空白を表示する、という風にしています。(この例では「500歳以上」の列) 問題はここからで、「500歳以上」の列に表示されているデータだけの、「名前」の列を別のシートに表示したいのです。その際、空白セルがあるデータはとばして、年齢だけが入っているデータの列を上に詰めて表示させたいのです(この例では、かぐや姫と一寸法師が該当) わかりにくい質問で申し訳ありません。関数でもVBAでもかまいませんのでどうかよろしくお願い致します。
- ta5549
- お礼率100% (5/5)
- オフィス系ソフト
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
みんなの回答
- zap35
- ベストアンサー率44% (1383/3079)
Sheet1のA列が名前、C列が年齢だとして、別シートのA1に以下の式を貼り付けて下にコピーすれば表示できます。 =IF(ROW()-0<=COUNTIF(Sheet1!C:C,">500"),INDEX(Sheet1!$A$1:$A$10,SUMPRODUCT(LARGE((Sheet1!$C$1:$C$10>500)*ROW(Sheet1!$C$1:$C$10),COUNTIF(Sheet1!C:C,">500")-ROW()+1+0))),"") 表示の開始行が2行目のときは式中の「-0、+0」の箇所を「-1、+1」に変更して下さい。(3行目以降は1ずつアップ)
- imogasi
- ベストアンサー率27% (4737/17068)
作業列を使い、空白でない列に連番を振ります。 そして別シートの行番号と対応付けて値をとってきます。 するとデータのある行が詰まります。 imogasi方式です。WEBでimogasi方式と入れて照会してください。沢山私の回答が出ます。 作業列を使う欠点もあるが、どういう理屈で処理しているかわかりやすい方法と思います。 作業列を使わないやり方は、慣れない方には、式が相当理解が難しくなります。
お礼
御礼が遅くなり申し訳ありません。 検索させてもらい、少し学ばせていただきます。 ありがとうございました、まずはお礼を言わせて下さい。
- p-applepie
- ベストアンサー率53% (156/290)
こんにちは。 単純な方法ですが、一度だけ作業をすればいいのであれば・・ 1.データ→フィルタ→オートフィルタ で オートフィルタの▼を出す 2.If関数の部分の▼をクリックし (空白以外のセル) を選択して データが入っているセルを選択 3.表示されているデータの名前部分を別シートにコピーペースト ではではダメでしょうか? 上記作業をマクロ記録して、列全体をコピーするように 設定すれば、何度も使用出来ます。
お礼
御礼が遅くなり申し訳ありません。 ご指摘の方法で出来ました。オートフィルタを分からない人でもマクロ記録すれば応用が出来そうですね。 ありがとうございました
関連するQ&A
- エクセルの関数についての質問です。
DATEDIF関数を使って生年月日から年齢を求める式を作り、それにIF関数を組み合わせたいと思って試したのですが、うまくいきませんでした。何か良い方法があったら教えて下さい。 A1に生年月日を入力し、B1に、 =DATEDIF(A1,TODAY(),"Y") で年齢を求め、オートフィルで複写すると、A列に生年月日を入力していない場合、B列にある数値が 出てしまうので、A列が空白の時は、B列も空白にしたいのです。そこで =IF(A1="",B1="",DATEDIF(A1,TODAY(),"Y")) としてみたところ 循環参照 している。と言うエラーメッセージがでてしまいます。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル2007「ISNUMBER関数」の組合せについて
エクセル2007「ISNUMBER関数」の組合せについて うまく説明できないので具体的に関数を掲載します。 「B列またはC列が空白の場合は空白セルとする」という関数を以下のようにつくりました。 =IF(OR(B2="",C2=""),"",DATEDIF(C2,B1,"Y")) (B1にはtoday関数が入力されています) これにC列が数値ではない場合も空白にするという条件を追加したいのです。 ISNUMBER関数を使うのだと思うのですが組合せ方がわかりません。 ご教示よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- Excelで複数の条件を満たす計算式を教えてください。
Excel関数の計算式について教えてください。 書類の提出の処理と、更新を一覧表にしたいのです。 A列に氏名 B列に書類を提出したかどうか(提出したら○、未提出は空白) C列に書類を処理したかどうか(処理したら×、未処理なら空白) D列に更新できたかどうか(更新したら空白、未更新なら1) このような条件で入力を実施したいのです。 それで、 B列が「○」か空白で、C列が「×」のときはD列は空白表示 B列が「○」か空白で、C列が空白の時にはD列に「1」と表示したい。 この場合、D列に挿入すべき計算式を教えてください。 IF関数でネストすれば多分なんとなかる…と思うのですが、 いろいろ試しましたが、式がエラーを起こしてしまいます。 わかる方には簡単なことだろうと思いますが、どうぞお助けください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで年月の合計を求める関数を教えてください。
A列 B列 C列 2000/4/1 2002/3/31 2年 2002/4/1 2004/9/30 2年6ヶ月 2005/4/1 2005/6/30 3ヶ月 として,C列の関数は(=TEXT(DATEDIF(A1,BI,"Y"),"0年;;")&TEXT(DATEDIF(A1,B1,"YM"),"0ヶ月;;")としています。 そこで,C列のみで(空白期間があるので)年月を合計する関数を教えてください。 (上記の例の場合,4年9ヶ月となるように) よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- SUMIFSの検索条件で文字後ろの空白を無視
=SUM(SUMIFS(A2:A800,B2:B8000,"東京",C2:C8000,{"*-200","*-200-*"},D2:D8000{"AB1","AC1","AD1"})) 以上の関数のC列のOR条件"*-200"によって、XXXXXX-200の行も集計したいのですが、200の後ろに空白があり、集計できませんでした。 (空白を手作業で削除したら集計できました。) データ表中には複数このような空白を含んだものがありますので、上記集計関数式で、空白を無視するような方法をご教授願います。
- ベストアンサー
- Excel(エクセル)
- エクセル 関数のあわせ技
お世話になります。 最終目的は年齢表示です。 生年月日の元データは他システムからCSVカンマ形式で出力されたものをエクセルで表示しているため、問題箇所があります。 ・年月日のいずれかが一桁の場合、「空白」+「半角数字」+「年月日」となっています。 例えば、昭和20年5月1日 の場合は 昭和20年 5月 1日 となっています。(数字は半角です) ・元データのほとんどはテキスト形式なのですが、一部日付データ形式があります。 セル表示は「元号+年月日形式」ですが、数式バーは西暦表示です 例えば、昭和24年12月11日 とセル上は表示されますが、 1949/12/11 と数式バーでは表示されます。 まず、生年月日データに混じっている空白を消すため、SUBSTITUTE関数を使って別の列に書き出しました。 =IF($J3="","空白行",SUBSTITUTE(AM3," ","")) これで、昭和20年 5月 1日は昭和20年5月1日になりました。 その後、DATEDIF関数で年齢を表示させました。 =IF(ISERROR(DATEDIF(AJ3,today(),"Y"))=TRUE,"",DATEDIF(AJ3,today(),"Y")) ただし、日付データの 1949/12/11 のセルは、18243 になってしまいました。 これでも年齢計算はできるのですが、生年月日確認用に使いますし、後で印刷時に利用するデータですので、「テキスト形式」として、昭和24年12月11日 と表示させたいのです。 上記の関数式に別の関数を組み合わせればいいのだろうと思いましたが、うまくいきません。 どうか、よろしくご教示ください。
- ベストアンサー
- オフィス系ソフト
- Excel 関数で上から順に数字を整列させる
Excelで、下記表(例)に入力された数字を上から順に関数を用いて隣のB列に整列させたいです。 ・例 A1:A60の表があります。 ※以下A列に入力された数字とします 1 空白 2 20 3 空白 4 15 5 25 ・(省略、この間空白と考えて下さい) ・ ・ 59 10 60 空白 上記表(例)は、A2に20,A4に15,A5に25,A59に10それ以外は空白を表します。 上記表のセル内数字は全て手入力とします。 全てのセルが空白の場合もあります。 B列はB1:B5の5行です。 この入力された数字を関数を用いて、B列に上から順にB1に20,B2に15,B3に25,B4に10,B5は空白となるように整列させたいです。 B列セル結果は他シートに参照されますのでセルはロックを掛けています。 上記のことからデータの並べ替えも使えませんし昇降順でもありません。 上から順に整列させた数字を、B1:B5の5行内で上位5まで表示させます。 A列の行数が多いのでif関数も使えません。 なお、上記質問内に不明な点がありましたら補足させて頂きます。 以上、宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel 空白を含むグラフの可変域を設定したい
名前定義を使用してグラフの可変域を自動的に参照させたいのですが、空白の扱いに困っています。 1.データの更新を日付を横軸にした折れ線グラフに 自動的に反映させたい 2.データの空白はグラフ上でも空白として扱いたい 3.できれば関数で処理したいがそれが効率的でなければマクロでも B列にデータの始まり以降は空白を含まない日付、 C列から決まった系列名があり値がそれぞれ列方向に伸びていきますが、 ここに不規則に空白が入ります。 A B C D … → 1 2 日付 項目名 3 2010/1/1 (数値) 4 2010/1/8 (数値) 5 2010/1/15 6 2000/1/22 7 2000/1/29 (数値) ↓ ↓ 今使っている名前定義: ='Sheet'!$C$3:INDEX('Sheet'!$C:$C,COUNTA('Sheet'!$C:$C)+1) では空白の有無に影響されてしまいB3からの日付列にしか使えません。 どのような方法に変えるのが良いのでしょうか。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- 関数によって空白したのを数値「0」に置き換える
A.............B...............C 1 ZZ............6..............× 2 AA........... 1 3 BB 4 CC............3 B列にはそれぞれ関数が入っています。 セルB1には、B2、B3、B4の数値の合計です。 C1のセルには、C1数値とB2、B3、B4の数値の合計が一致しているか否かの判定する関数が 入っており、一致していなければ、"×"が表示させるようにしています。 しかし、ここで問題が起きました。 「VALUE」というエラーが表示されました。 上記の表では、セルB3は、空白になっております。 本来は、セルが空白になっている場合は、「0」が格納されていると思うのですが、 ここには、関数によって、文字列?の「空白」に置き換えられています。 例えば = IF((Z5) ,5, "") ようにです。 多分、関数によって文字列として認識される空白("")を置いたためと思います。 関数によって置き換えた空白("")を数値「0」として認識できる関数を教えてください。 いろいろな方法があると思いますが、「関数」のみで教えてください。 C1には、以下の関数が入っています。 =IF((B1) = (B2+B3+B4) ,"○","×")
- ベストアンサー
- オフィス系ソフト
- エクセルにて2つの条件を満たす人数のカウント方法
いつもお世話になっています。 エクセルの関数にて質問があります。 ご教授ください。 A1~A20に名前、 B1~B20列に○もしくは空白 C1~C20列に年齢を入れた表があります。 B列に○がついていて、尚且つ年齢が20才未満の 方の人数をカウントしたい場合どのような関数を入力すればいいのでしょうか? ご教授よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
御回答ありがとうございます。 式の内容を理解するには及びませんが、ご指摘の方法で出来たみたいです。 じっくり時間をかけて式の部分部分の意味を理解してみようと思います。 ありがとうございました