これからの季節に親子でハイキング! >>

以前にどなたかが、質問の回答としての関数式をそのまま使っていますが一つだけ問題があります。
詳しい方よろしくお願いします。
Sheet1に年間データがあります。そこからSheet2へE1セルで月を指定して月単位でデータを抽出しているのですが
1月だけ抽出データの最後に、1900/1/0をひろってしまいます。書式を変えても1月0日をひろいます。他の月は空白を返してくれるのですが、1月だけは、書式コピー分すべて1900/1/0を返してきます。文字列や数値は大丈夫です。日付だけです。対処法お願いします。
なんとなくしか理解出来ていない、わたしですいません( ノД`)シクシク…

Sheet1 抽出作業列 F列  
=IF(OR(Sheet2!E$1="",MONTH(A2)<>Sheet2!E$1),"",ROW())

Sheet2 抽出列 A~E列 A列日付
=IF(COUNT(Sheet1!$F:$F)<ROW(E1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROW(E1))))
よろしくお願いします。

A 回答 (3件)

こんにちは



>1900/1/0をひろってしまいます
実際にそのようなデータがあるのなら、関数の機能としては間違いではないと思いますが、多分、「空白」セルが抽出されていて、結果的にそのような表示になっているのではないかと推測します。

セルの位置関係や内容(意味)がご質問文だけではよくわからないので、半分は想像ですが・・・
>Sheet1 抽出作業列 F列  
>=IF(OR(Sheet2!E$1="",MONTH(A2)<>Sheet2!E$1),"",ROW())
A列に対象とする日付が並んでいて、その範囲内に空白セルが混在しているものと推測します。
(1月を抽出する際に、空白セルの行のF列に抽出用の行番号が表示されているのではないでしょうか?)

対策として、A列が空白の場合はピックアップしないように、式内のORに条件を追加すればよいのではないかと思います。
具体的には
 =IF(OR(Sheet2!E$1="",A2="",MONTH(A2)<>Sheet2!E$1),"",ROW())
といった感じ。

(半分は想像ですので、当たるも八卦です。悪しからず)
    • good
    • 0
この回答へのお礼

お早い回答ありがとうございます。ご想像のとおりです。
空白セルの行のF列に抽出用の行番号が表示されています。
A2="" 足すだけなんて・・・m(__)m
わたしレベルじゃ簡単に想像できますね 
ありがとうございました

お礼日時:2018/12/07 17:33

こんにちは!



具体的な配置がよく判りませんが・・・
作業列の数式を
=IF((A2="")+(Sheet2!E$1="")+(MONTH(Sheet1!A2)<>Sheet2!E$1),"",ROW())

にしたらどうなりますか?

ちなみに数式の意味は「OR」条件で
A2が空白、またはA2の「月」がSheet2のE1以外、またはSheet2のE1が空白の場合は空白に!
そうでない場合は行番号を!

という数式です。

※ 1900/1/0をひろってしまいます。
Excel的に、空白セル=0 とみなしますので
セルの表示形式が「日付」になっている場合、0=1900/1/0 と判断します。
シリアル値は
1900/1/1を「1」として、1日増えるごとに「1」ずつプラスされます。

すなわち、「0」は1900/1/1 の1日前という判断して
「1900/1/0」という表示になると思います。m(_ _)m
    • good
    • 0
この回答へのお礼

わかりやすい解説ありがとうございます。
勉強になりました。1日前か・・・
またよろしくお願いします

お礼日時:2018/12/07 17:40

関数の複数の組み合わせとなっているので、


個々の関数が正しいか確認してみましょう。

「1900/1/0」は、「空白」「0]「1未満」を日付表示するとそうなります。
そんなところが選択対象になっているのでしょう。
    • good
    • 0
この回答へのお礼

作業列で空白もカウントしていました。
ありがとうございます。
未熟者ですいません m(__)m

お礼日時:2018/12/07 17:44

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

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Q参照先セルに値が入っていない時に、「0」や「1900/01/00」などが入らないようにしたいのですが

Excelでの質問です。
参照先のセルに何も値(空白セル?)が入っていない時に
「0」などが表示されてしまうのを参照先と同じように、何も表示されていない状態に
したいのですが、どのようにすればよろしいでしょうか。

例えば、A1に「=B1」と入力してある場合、A1に「0」と表示されます。
B1には何も入力していない状態であります。
A1をB1と同じように何も表示されていないように「0」とかを出ないように
したいのです。

何かいい方法がありましたら、教えてください。
よろしくお願いします。

Aベストアンサー

IF関数で空白なら空白を返すようにします。

=IF(B1="","",B1)

IF関数 完璧マスター!
http://kokoro.kir.jp/excel/if.html

Qエクセルで1月0日と表示される!!

エクセルで2つのシートを使い、1つのシートには、一ヶ月の日付と点数を入力し、もう1つのシートは、先のシートからデータが飛ぶ形で、曜日ごと集計される形で、月ごとにまとめています。

しかし、集計のシートで、第5週がない場合、入力シートのセル内はブランクで、集計シートに表示されるのは1月0日となってしまいます。

説明がわかりずらいかと思いますが、取りあえず、シートを越えてデータを飛ばしたときに、1月0日と表示させない方法を教えていただければと思います。

ネットで検索しても、解決方法がヒットしません。
エクセルのスペシャリストの方、助けてください。

Aベストアンサー

こんばんは。

>先のシートからデータが飛ぶ形

‥というのは、=Sheet1!A2 のような式を入れているということでしょうか?

そういうことでしたら Sheet1 の A2セルが空白の場合、0と認識されますので、
日付シリアル値が 0 → 1900年1月0日 になるからです。

これを回避させるには次のような方法があります。
-----------------------------------------------
(1) Sheet2 のすべてのセルが 0 を表示させなくてもかまわないのなら
 メニュー <ツール>-<オプション>-<表示>タブの
 「ゼロ値」 のチェックをはずす。

(2) Sheet2 の日付のセルをすべて選択し、
 メニュー <書式>-<条件付き書式>で
 「セルの値が」-「次の値に等しい」を選び、
  0 のときは文字の色を白にする。
 
※条件付き書式については参考URLをご覧ください。

(3) Sheet2 の数式を↓のように変更する。 
=IF(Sheet1!A2="","",Sheet1!A2)
----------------------------------------------
以上のうちからどれか1つをお選びください。

参考URL:http://www.eurus.dti.ne.jp/~yoneyama/Excel/jyo-sho/jyo-syna.htm

こんばんは。

>先のシートからデータが飛ぶ形

‥というのは、=Sheet1!A2 のような式を入れているということでしょうか?

そういうことでしたら Sheet1 の A2セルが空白の場合、0と認識されますので、
日付シリアル値が 0 → 1900年1月0日 になるからです。

これを回避させるには次のような方法があります。
-----------------------------------------------
(1) Sheet2 のすべてのセルが 0 を表示させなくてもかまわないのなら
 メニュー <ツール>-<オプション>-<表示>タブの
 「ゼロ...続きを読む

Q和暦から西暦に変換したい

EXCEL2013を使用しています。
A1セルに入力した和暦(年月の形式)を
A2セルに西暦(月初の年月日形式)で出力する方法を教えて下さい。
よろしくお願い致します。

【例1】
A1セル H1.2
A2セル 1989/2/1

【例2】
A1セル S63.8
A2セル 1988/8/1

Aベストアンサー

A1セルに入力したものは、文字列として認識してしまうので、下の
A2セルに表示させるものも文字列として表示してもよいのならば、
以下のような数式で可能です。

 =TEXT(A1&".1","yyyy/m/d")

シリアル値として、他でも利用したいのなら以下のようにします。

 =(A1&".1")*1
 セルの表示形式を yyyy/m/d にする。

どちらで設定するかは、利用する側で判断してください。

Q月末の自動計算

月末火曜日の日付をスタートとして稼働日数を求めたいと思っています。

10月なら9/25~10/30まで
11月なら10/30~11/27まで
12月なら11/27~12/25までの日数を計算したいです。

月末水曜日になると当月の日数に自動で計算が切り替わるよう計算することは可能でしょうか。

わかる方がいれば教えていただきたいです。
※土日は日数に含んでOKです。

Aベストアンサー

No1です

>先程試したところ8と表示されるのですが11/28~今日までが8日という計算でしょうか?
はい。下に示しますように『今日』を対象に計算する式として作成してしまいましたので、12/5(=本日)は11/27(=最終火曜日)から数えて8日目という意味になります。
(シートが自動計算の設定になっていれば、明日になると9と表示されるはずです)

私の勝手な勘違いだったかもしれませんが、「今日が月末火曜日から何日目かを計算する」関数式として考えていましたが、対象が「今日」とは限らなかったのでしょうか?
その場合は、式中の「TODAY()」の部分を対象の日(=シリアル値)に置き換えればそのまま同じ計算が可能なはずです。
例えば、対象の日がA1セルに入力されているとするなら、TODAY()を全てA1に置き換えればよいといった感じです。

>ただ、水曜日になったら前日の火曜日分も含めて計算してほしいです。
翌日の水曜日を2日目と計算したいという意味でしょうか?
(No1の説明を読んでいただければわかりますが)現在の式は、水曜日は1(日目)と表示される式になっています。
修正したい場合は、No1の式より常に1日多い値になれば良いだけなので、式の最後に「+1」を加えれば、お求めの結果になると思います。

>とても複雑な回答で解読できなかったので教えていただけると幸いです
式は少々長いですが、全体としての構成は、単純にIFで場合分けした式になっています。
ご参考までに、大雑把な構成を言葉で示すならば・・・
『対象日(今日)がその月の最終火曜日より前なら、前月の最終火曜日からの日数を数え、後ならその月の最終火曜日からの日数を数える』
という式になっています。

No1です

>先程試したところ8と表示されるのですが11/28~今日までが8日という計算でしょうか?
はい。下に示しますように『今日』を対象に計算する式として作成してしまいましたので、12/5(=本日)は11/27(=最終火曜日)から数えて8日目という意味になります。
(シートが自動計算の設定になっていれば、明日になると9と表示されるはずです)

私の勝手な勘違いだったかもしれませんが、「今日が月末火曜日から何日目かを計算する」関数式として考えていましたが、対象が「今日」とは限らなかったのでしょう...続きを読む

QエクセルのIF関数について教えてください

エクセル2007を使用しているのですが、IF関数について分からない事があるので分かる方アドバイスをお願いします。

A2には「お急ぎ便」「通常便」「日時指定便」のいづれかの文字が入力されています。
A3には「出荷予定日」  2018/12/1 など入力されています。
A4には「=A3+1」と入力して出荷予定日の翌日の日付が入力されるようにしています。 
     例えばA3が2018/12/1の場合には2018/12/2と表示されます。

私がやりたいことですが、A5のセルに下記の情報を入力したいです。

A2のセルに「お急ぎ便」という特定の文字が含まれている場合には、出荷予定日の翌日に到着させなければいけない為、A4の日付をA5セルに表示させるようにしたいです。

反対に「通常便」「日時指定便」の文字が含まれている場合には、A5セルに何も入力せずに「空白」にしたいです。

私も色々と試行錯誤しながら下記の関数がつかえそうだと考えてみましたが、この文字列はお急ぎ便という文字がはいっている場合は○を表示させて、入っていない場合は×と表示させるものですよね。

=IF(COUNTIF(A2,"*お急ぎ便*")=1,"○,"×")

上記の○の部分に、A4のセルで表示されている文字を表示させるようにしたいのですが、可能でしょうか?×の部分は単純に削除して空白にすればいいと思うので問題がないのですが、○の部分に=A3+1とかA4とか色々と入力しましたが、入力した内容がそのまま表示されてしまって、A4の日付をA5のセルに表示させるのはできないようです。

分かる方がいらっしゃったらアドバイス頂ければ助かります。どうぞよろしくお願いします。

エクセル2007を使用しているのですが、IF関数について分からない事があるので分かる方アドバイスをお願いします。

A2には「お急ぎ便」「通常便」「日時指定便」のいづれかの文字が入力されています。
A3には「出荷予定日」  2018/12/1 など入力されています。
A4には「=A3+1」と入力して出荷予定日の翌日の日付が入力されるようにしています。 
     例えばA3が2018/12/1の場合には2018/12/2と表示されます。

私がやりたいことですが、A5のセルに下記の情報を入力したいです。

...続きを読む

Aベストアンサー

>○の部分に=A3+1とかA4とか色々と入力しましたが、入力した内容がそのまま表示されてしまって

まずは、A5のセルの表示形式を日付にしましょう。

それから、A2には
>「お急ぎ便」「通常便」「日時指定便」のいづれかの文字が入力されています。
なので、部分一致にしなくても

A5=IF(A2="お急ぎ便",A4,"")

でよくないですか?それとも他に文字がプラスされることがあるのですか?

Q下の画像の数値を93+124+126と計算できる関数を教えて下さい。

下の画像の数値を93+124+126と計算できる関数を教えて下さい。

Aベストアンサー

式でいいんじゃ無い?
=A113*100+B113*10+C113
をD113へ入れる

それを、D114,D115へコピペ

Qvlookupにて、返した値をさらに、他の文字列に変換するには

タイトルの件、質問します。

下記画像をご覧ください。
B2には、次の式が挿入されています。 =VLOOKUP(A2,$D$2:$E$6,2,FALSE)
B3には、              =VLOOKUP(A3,$D$2:$E$6,2,FALSE)

B2の501をさらに、【五】に変換
VLOOKにて値【101や201】を返した後に、さらに、101を一、201を二
501を五 というように、任意の文字列に変換をするにはどすれば良いでしょうか?

宜しくお願いします。

Aベストアンサー

ちなみに、表示形式でやる場合C2に
=INT(B2/100)
→表示形式をユーザー定義で「[DBNum1]」にする。

TEXT関数なら
=TEXT(INT(B2/100),"[DBNum1]")

Q万年カレンダーについて。

次のexcelのデータで、おかしなところがありましたら、教えていただけると幸いです。
https://fire.st/9Il0GsR

Aベストアンサー

>確かに35行は、緑になりました。どうしてそうなるのでしょうか?教えていただけると幸いです。
添付の画像を参照ください。
2018年6月の例ですが、C35へ
=COUNTIF(祝日,A35)
と入力しています。結果は、1048470が表示されます。
これは、=COUNTIF($Y:$Y,A35)と同じことです。(祝日の範囲がY列全体の為)
A35は空白なので、Y列の空白の部分がカウントされます。その為、1048470になります。
COUNTIF(祝日,A35)の結果が0でなければ、判定は真なので、色がつく条件式が成立します。
そのため、緑になります。

>すみません。COUNTIF 関数のみで、祝日に色をつけるとするとどうすれば良いのでしょうか?教えていただけると幸いです。
祝日の範囲を、空白を除いて、正確に定義しなおします。
一旦、祝日の名前を削除し、実際に祝日の日付が入力されているセル範囲Y3:Y107を、祝日の範囲にします。
(つまり、Y列全体をやめて、$Y$3:$Y$107にします)
そうすると、COUNTIF 関数のみで祝日に色をつけることができます。
祝日の名前を削除する方法は、下記を参照ください。(削除せず、変更することも可能です)
https://www.wanichan.com/pc/excel/2016/8/47.html

COUNTIF 関数のみで、祝日に色をつけるようにした場合は、祝日の追加が発生すると(2023年以降分の祝日の追加など)、Y列の範囲が変わるので毎回、それにあわせて、祝日の範囲もかえる必要があります。

>確かに35行は、緑になりました。どうしてそうなるのでしょうか?教えていただけると幸いです。
添付の画像を参照ください。
2018年6月の例ですが、C35へ
=COUNTIF(祝日,A35)
と入力しています。結果は、1048470が表示されます。
これは、=COUNTIF($Y:$Y,A35)と同じことです。(祝日の範囲がY列全体の為)
A35は空白なので、Y列の空白の部分がカウントされます。その為、1048470になります。
COUNTIF(祝日,A35)の結果が0でなければ、判定は真なので、色がつく条件式が成立します。
そのため、緑になります。...続きを読む

Qエクセルについて。

次の数式を求める関数(下の方のプラン表)は、どんな数式を打てば良いのでしょうか?教えていただけると幸いです。

Aベストアンサー

単純な足し算ですよね。
添付画像のように作ったとして

[C8]=SUM($B$2,C$7,$B8)

これを[C8:F12]の範囲にコピーすればOK。

通常、数式のセルは、コピー貼り付けすると、移動先に応じてセルアドレスが変わってしまいますが、
「$」をつけることで、固定することができます。

上の式だと、どこに移動しても

$B$2 は B2 を参照するように固定される。

C$7 は 7行目 を参照するように固定される。
(式を下にコピーしても、7行目に固定される)

$B8 は B列 を参照するように固定される。
(式を右にコピーしても、B列に固定される)

ということになります。
$をつけて参照先を固定するのを「絶対参照」、
つけていない普通の場合は「相対参照」、
と言います。
より詳しいことは、このキーワードで検索して調べてみてください。

金額表示については、セルの書式から、
分類:通貨
記号:\
にしてあります。
実際のセルに「\」は入力されていません。

Q単価別売上合計の求め方

初心者です。わかる方入れば教えてください。

単価別の売上合計を出したいのですが関数で可能でしょうか?

シート1:O列→それぞれの商品の売上
シート1:K列→それぞれの売価

シート:集計(価格別)
A列、B列
1000 2000 →(1000円以上:2000円未満)
2000 3000 →(2000円以上:3000円未満)
3000 4000 →(3000円以上:4000円未満)の意味で記入されています。

この時、「シート:集計(価格別)」のC列にAB列の条件に当てはまるシート1の商品の売上合計を求めたいのですが可能でしょうか。

Aベストアンサー

ピボットテーブルを使ったやり方を紹介します。

データは1行目から記載されているとして説明します。
また、P列を作業列として使用します。

◆手順
1.見出し行を作成します。1行目からデータがある場合は行を挿入する。O1セルに『売上』、P1セルに『売価』と入力する。
2.P列に売価の代表値を書く。今回の場合は、1000~2000→1000、2000~3000→2000、・・・とする。数式はP2セルに『=ROUNDDOWN(F2/1000,0)*1000』を入力して下方向にコピペする。
3.O~P列を選択して、メニュー → 挿入 → ピボットテーブル とする。
4.別ウィンドウがでるが、作成先を新規ウィンドウにして『OK』をクリックする。
5.添付図の⑤のようにドラッグ&ドロップする。
6.添付図の⑥のように、『売上』を左クリック → 値フィールドの設定 → 別ウィンドウが開く → 選択したフィールドのデータ『合計』を選択 → OK
7.完成


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

人気Q&Aランキング

価格.com 格安SIM 料金比較