• ベストアンサー

漢字の含む数値列の検索方法

excel関数式算定初心者です。 sum及びsumuif程度までは多少難解な数式となっても何度も繰り返し間違い修正にて数値の抽出算定が出来たのですが、漢字を含む年月の検索方法に悩んでいます。 具体的に、請求月が「平成26年7月」とC列2行にあり、B列の「平成26年5月」を含む以前の月の行のS列の数値を検索抽出し合計したいのです。 具体的過ぎる列・行の提示で分かりづらいかと思いますが、抽象的に分かりやすく提示する言語が浮かばず、このような文面となりました。 どなたか、お力添えを頂ければと思います。 宜しくお願い致します。

  • SaHaRe
  • お礼率48% (143/297)

質問者が選んだベストアンサー

  • ベストアンサー
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.3

こんばんは! No.2さんが回答されていらっしゃる方法と同じになりますが、 別Sheetでやります。 ↓の画像で左側がSheet1とし、Sheet2に表示するとします。 両Sheetとも日付セルは「文字列」だとします。 Sheet2のA列に作業用の列を設けます(Sheet1の行データと合わせます) Sheet2のA4セルに =IF(Sheet1!B4="","",DATEVALUE(Sheet1!B4&"1日")) という数式を入れフィルハンドルでこれでもか!というくらいしっかり下へコピーしておきます。 そしてSheet2のD1セルに検索したい年月を入力(その年月以前のデータを集計)します。 結果のD2セルには =IF(D1="","",SUMIF(A:A,"<="&DATEVALUE(D1&"1日"),Sheet1!S:S)) という数式を入れています。 ※ Sheet1のデータは変更があるたびにSheet1の行・列が違わないようにコピー&ペーストします。 こんな感じではどうでしょうか? >請求月が「平成26年7月」とC列2行にあり の部分は全く無視しています。 このようなSheet上の操作もしたくない!という場合は やはりVBAになりますかね!m(_ _)m

SaHaRe
質問者

お礼

頂いたアドバイスを参考に地道に調べ何とか、後一歩のところまでこぎつけることが出来ました。問題の箇所はシリアル値に変換するマクロを実行させることで解決出来そうです。今は指定の列に0値がある場合行を削除するマクロをどう組むかで悩んでいます。質問内容とかけ離れてしまったので、一度〆させて頂き、自力でどうしても困難な場合改めて質問を致したいと思います。皆様お忙しい中、顔も知らない私にアドバイスを下さり、本当に感謝しております。有難う御座いました。

SaHaRe
質問者

補足

有難う御座います。色々とVBA関連及びExcel関数関連の初心者向け書物を読みつたない知識の中で、ひねり出したのですが、年月をシリアル値に変換し、検索をかけ合計金額を算出する、これをマクロで実行するようにプログラムを組むのが最もシンプルではと思いました。上記の方法も組み込むプログラムにおいて大変アドバイスになりました。その中でつまづいている箇所が、年月を過去6か月ほど作業列を作成するとして、1もしくは2か月前の月遅れの数値はありますが、3か月以降の検索数値がなかったとして、その枠を算定表では出さないようにする為にはどうしたらいいものか悩んでいます。質問の意図が不明確でしたら、お伝え下さい。本当に初心者がややこしいことをしているのですが、もうしばらくお助け頂ければと思います。宜しくお願い致します。

その他の回答 (3)

  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.4

ANo.2です > 私が作成excelシートを使用するのではなく、事業所へ配布して使用して頂き運用して頂く予定の為、使用者はexcelに詳しくない者になります。excelシート上での細かい操作はさせず、別シートに張り付けるのみで表が自動作成出来るようにしたいのです。 > 出力元のソフト会社に年月のデータ形式を変更して頂けるよう開発依頼を掛けていますが、開発まで時間が必要ではと思います。 > もし、解決策をお持ちでしたら、お時間のある時にアドバイス頂ければ助かります。 作業者による操作なんかいりません。 前の回答で添付した図ではC列に式を入れましたが、同じシートの別な場所でも、ANo.3の方がやっているように別シートでも良いわけです。 回答はあくまでも考え方を提示しているのですから、位置を変えたり空白の時の処理を入れたりして、作業者が既定の場所にデータを貼り付けるだけで目的のデータが取得できるようにチャレンジしてください。 アドバイスになっていなくてすみません。

SaHaRe
質問者

補足

度々、申し訳ありません。加えて度々お付き合い頂き有難う御座います。ようやくおっしゃっている意味が薄~くですが、理解出来るようになってきました。下記に追加質問を致しました。もう一歩のところまで来ている感覚があるのですが、そこから動けず、どこから調べればいいのか検討もつかず…お時間のある時で構いませんので、お力をお貸し頂ければと思います。宜しくお願い致します。

  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.2

「平成26年7月」等の文字列を日付のシリアル値に変換してそのシリアル値を使用すれば良いだけでは? たとえば、C2セルに「平成26年7月」とあるのでしたら=DATEVALUE(C2&"1日")で、2014/7/1のシリアル値になります。 同様にB列の文字列も作業列に=DATEVALUE(B5&"1日")の様な式を入れて後はSUMIFかSUMIFS辺りを使って対象の値を合計します。

SaHaRe
質問者

補足

ご返答有難う御座います。 説明が不足しており、補足致します。 データはソフトから抽出したものになります。 私が作成excelシートを使用するのではなく、事業所へ配布して使用して頂き運用して頂く予定の為、使用者はexcelに詳しくない者になります。excelシート上での細かい操作はさせず、別シートに張り付けるのみで表が自動作成出来るようにしたいのです。 出力元のソフト会社に年月のデータ形式を変更して頂けるよう開発依頼を掛けていますが、開発まで時間が必要ではと思います。 もし、解決策をお持ちでしたら、お時間のある時にアドバイス頂ければ助かります。 宜しくお願い致します。

  • aokii
  • ベストアンサー率23% (5210/22062)
回答No.1

年月が年月日(2014/7/22)式で記載されていれば簡単なのですが、 ご記載の通りに、年月が文字列になっているのでしたら、 はじめに、T2セルに、以下の式を入れて下にドラッグコピーする。 =IF(OR(AND(MID($B2,FIND("成",$B2)+1,FIND("年",$B2)-FIND("成",$B2)-1)*1<=26,MID($B2,FIND("年",$B2)+1,FIND("月",$B2)-FIND("年",$B2)-1)*1<=5),MID($B2,FIND("成",$B2)+1,FIND("年",$B2)-FIND("成",$B2)-1)*1<=25),1,0) 次に、U2セルに、以下の式を入れるのはいかがでしょう。 =SUMPRODUCT(S2:S11,T2:T11)

SaHaRe
質問者

補足

ご返答有難う御座います。 出力データのセル内のデータがテキスト混在の形式になっており、且つ検索し月の数値以下を出力してくる必要があるので、悩んでおります。 初歩的な質問で申し訳ないのですが、T2セルとU2セルとは具体的にどのセルになりますか? マクロを使用するということでしょうか? 知識不足・勉強不足で申し訳ないのですが、宜しくお願い致します。

関連するQ&A

  • エクセルで行から数値を検索し、検索した数値のある列

    エクセルで行から数値を検索し、検索した数値のある列から左側の全ての合計を求めたいです。   A B C D E F 1  3 4 8 9 11 22 ←数字は隙間なくあるが、値がバラバラ(ただし昇順) 2 3  2~6行には色々な数字がまばらにある 4 5 6 1行目から検索したい数→10(別のセルで数式結果として出た数) ズレてるかもしれませんが・・・ E1セルに書かれている「11」の数字より前のD列、 A~D列の2行目~6行目の合計を各行ごとに別々に出していきたいです、どうしたらいいでしょうか また、表は行も列もデータを追加していく予定です

  • 左から2列目を検査値とし1列目の数値を返す方法は

    VLOOKUP関数では範囲の1列目で特定の値を検索し、指定した列と同じ行にある数値を返しますが、これを2列目で特定の値を検索し、左端の1列目の値を返す方法を探しています。可能でしょうか? 1列目にNo.、2列目に氏名があります。これを名前を検索して、1列目の番号を返したいわけです。列を入れ替えれば可能ですが、入れ替えずにやる方法あれば教えてください。よろしくお願いします。

  • エクセルで行数の異なる2つの列の数値を新しい列にコピーする方法

    はじめまして。 エクセルで質問があります。 A列に数値データ、B列に0 or 1のデータがあります。 Sum(B:B)=A列の行数です。 このデータを、C列に1のときはA列のデータを1行下にコピー、0のときは""にしたいと考えております。 結果C列はB列を同じ行数 if文でC1セルに =if(B1=1,A1,"") と打つと、B列に0が入った途端、A列の数値が進みすぎてしまいます。 B列=0のときA列を進まなくする方法はあるのでしょうか? たとえばB1=1, B2=0, B3=1のとき、C3にはA2の数値を入れたいのですが、A3になってしまいます。 よろしくお願いいたします。

  • 複数の行で近似値の列検索がしたい

    初心者です。よろしくお願いいたします。 質問の内容ですが、A.B.Cのそれぞれの行で数値が入力されていまして、例えばA行が60、B行が65、C行が70という数値に一番近い数値を示す列を検索したいと思っています。 例えば100列目にA行61、B行65、C行71という数値があり、その列が一番条件に合う列とするとその列を検索したいのです。 現状はデータのフォームを使ってある程度絞って一つ一つ確かめています。よろしくお願いいたします。

  • 表全体から複数条件を満たす行を検索する方法は?

    数十列、数千行から成る表の各セルに数値データがランダムに入っています。(1~99の範囲) 例えば、50.55.60.65の4個の数値を含む行を検索したいとします。 今は、各列ごとにフィルタをかけて、この4個のいずれかが入っている行をメモり、その後、該当行だけを抽出し、この4個の数値がすべて含まれる行を探し当てる。というような具合です。(これをやるのに1日かかります) TVドラマ等で、数項目の条件を入れてやると該当者○人とか出ますが、それに近いことができないものかと思っているのですが・・・? 今の方法だとフィルタをかける列の順序によっては正確に抽出ができず モレてしまうこともあります。 同じ手間をかけるならこうしてみたら・・の案があればアドバイスをいただければ幸いです。

  • 図の作り方(n行n列に数値があり、数値の大小を色の濃淡で表わす)

    図の作り方(n行n列に数値があり、数値の大小を色の濃淡で表わす) n×n行列があります。 i行j列目の数値をRijと置きます。 Rijには0~100程度の数値が入ります。 数字が小さければ小さいほど、そのマスの色を濃くしたいと思います。 例えば、 1 0 0 0 0 1 1 0 0 というような3行3列の数値なら、 ■□□ □□■ ■□□ といったように表したいです。実際には150×150程度の数値があり、 濃淡で表わしたい数字0~100程度で、整数ではありません。 とりあえずgraceでできるかなと思いやってみましたが、うまくいきませんでした。 適当なソフトなどを紹介していただけるとありがたいです。 抽象的で、説明不足ですが、よろしくお願いします。

  • エクセルマクロで重複数値と以外の数値を抽出する

    数値の表を作成しておりますが、 重複数値で困っております。マクロで教えてください。 A列11~20まで1.2.2.4.5.6.6.8.9.10とあるデータに 2と6が重複しています 重複数値2.6をB列2行目以降に、C列2行目以降にはそれ以外の1.4.5.8.9.10 を抽出したいのですが、マクロで教えてください。 (offsetを使ってできますか?) また、重複数値を抽出する自作関数ってできるでしょうか? よろしくお願いいたします。

  • エクセルの表計算で、検索値が含まれる列を検索後、同じ列の違う行にある数

    エクセルの表計算で、検索値が含まれる列を検索後、同じ列の違う行にある数値を返す場合はどのようにすればいいのでしょうか? ※下表を例としますと、検索値 150 が含まれる1行目のC列を検出後、同じ列の2行目に入力されている 4300 を返す場合の関数式を教えてください。     A      B         C        D 1   1~50  51~100   101~250   251~500 2   5000    4500      4300     4000

  • エクセルで数値の取り出し方

    こんにちは。教えて下さい。 データを入力するテーブル内に数値入力欄(O列)があります。 vlookupで各行を取り出すことがあるのですが、数値欄のみ、その取出行を含めて最上位にある行の数値を合計したものを取り出したいのです。 (vlookupでの抽出はA列に1から始まる自然数を使用しています。) 1~10行までのデータが入力があった時、5番目のデータを取り出した場合は5番目のデータの数値と1~4行目までの数値の合計の抽出です。 よろしくお願い致します。

  • エクセルで文字検索を簡単に。

    エクセルで文字検索を簡単にする方法はありますか? B列2行目以降に年月日(曜日)・・・同じ年月日(曜日)の行がたくさんある場合もあります。 C列以降はB列に関する文字が入力されています。 検索対象は年月ですが、検索するのに標準コマンドで検索すればよいことかと思いますが、年月日(曜日)がかなり増え、簡単に検索する方法がないかと、ネットなどで調べましたがなかなかヒットしません。 H19年4月、5月・・・H20年1月、2月・・・・○○年月の文字を1行目に作成し、その文字をクリックすると該当する年月の行の始まりまたは、該当する年月の行のいずれかを表示させる方法はありますでしょうか? エクセルは2000です。 参考サイト等ご存じでしたらよろしくお願いします。

専門家に質問してみよう