出産前後の痔にはご注意!

A列に 0.5、50.5、120、130.3、247・・・といった数値があり、0〜100までの数値をa、130.5〜200.8をb、250〜300をc・・・のようにa,b間が連続していない複数の条件の中から該当するものだけをB列に抽出したいのですが、どのような方法で求めたら良いでしょうか。

また、A列にある247のように一の位を四捨五入すれば250になる値もcとして扱いたいのですが可能でしょうか。

A 回答 (2件)

添付図の例で2行目に次の式を入力して、必要なだけフィルコピーしてみてくだい。


B2の式 =IF(AND(A2>=0.5,A2<=100),A2,"")
C2の式 =IF(AND(A2>=130.5,A2<=200.8),A2,"")
D2の式 =IF(AND(A2>=250,A2<=300),A2,"")
E2の式 =IF(ROUNDUP(A2,-1)=250,250,"")
「複数ある範囲に該当するものがあれば文字を」の回答画像2
    • good
    • 0
この回答へのお礼

ありがとうございます。参考にさせていただきます。

お礼日時:2018/11/26 23:11

こんにちは



B1セルに
=IFERROR(INDEX(A:A,SMALL(IF((A$1:A$100<>"")*((0<=A$1:A$100)*(A$1:A$100<=100))+((130.5<=A$1:A$100)*(A$1:A$100<=200))+((245<=A$1:A$100)*(A$1:A$100<=300))>0,ROW(A$1:A$100)),ROW(A1))),"")
の数式を入力し、Shift+Ctrl+Enterで確定します。(←必須です)
確定後、必要なところまで下方にフィルコピー
で、いかがでしょうか?


※ 各範囲の境界値を「含む/含まない」かに関して、ご質問文からは読み取れなかったので、ひとまず全ての境界値を「含む」式にしてあります。

※ A列の対象範囲をA1:A100としてありますが、それ以上ある場合は調整が必要です。
    • good
    • 0
この回答へのお礼

ありがとうございます。参考にさせていただきます。

お礼日時:2018/11/26 23:11

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

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

Qエクセルでの名前の定義について

エクセルで シート1の患者の単に名前が下にずらっと並んでいて、シート2にそれぞれの患者の情報が
横に列ごとの項目種類でならんでいた場合シート1の患者に名前の定義をし(例 S患者名)、シート2でも患者に名前を定義し(例 B患者名) それぞれお互いにクリックして見れるようにハイパーリンクをしたが、シート2でソートなどをした関係か、シート2からシート1はいいが、シート1からシート2はずれてリンクささってしまうが、どうしてか? ご教授ください。

Aベストアンサー

No.1です。

このやり方は名前の定義は不要です。手順に沿って行うだけでよいです。
A列には普通に名前を羅列します。(例 A2:Aさん、A3:Bさん、・・・)
B列には下記の手順にしたがって数式を入力します。
C列にはリンクしたい先の名前を記載します。(例 C2:Bさん、C3:Cさん、・・・)
こちらで試したときのデータの配置を添付しました。(そちらのデータの配置は把握しておりません)

No.2の回答で、うまくいかなかった理由は、数式のシート名の部分が『sheet』になっているためと思われます。そちらのファイルではシート名は『シート○○』ですよね?

ついでに、もう一点微修正しましたので、以下の手順と差し替えてください。
(数式を変えただけで手順は同じです)
最下部の※も参照ください。

◆手順
1.シート1のB2セルに『=HYPERLINK(CELL("address",INDEX(シート2!A:A,MATCH(C2,シート2!A:A,))),C2)』と入力して、下方向にコピペします。
2.シート1のC列にリンク先患者名(例:S患者名)を記入します。
3.シート2のB2セルに『=HYPERLINK(CELL("address",INDEX('シート1'!A:A,MATCH(C2,'シート1'!A:A,))),C2)』と入力して、下方向にコピペします。
4.シート2のC列にリンク先患者名(例:B患者名)を記入します。


シート名やセル参照はそちらのデータにあわせる必要があります。
リンク(C列)に指定した名前がもう一方のシートの名簿(A列)に存在しない場合、リンクがエラーになります。
あっているはずなのにリンクがエラーになるときは、F2キーを押してからEnterを試してください。

No.1です。

このやり方は名前の定義は不要です。手順に沿って行うだけでよいです。
A列には普通に名前を羅列します。(例 A2:Aさん、A3:Bさん、・・・)
B列には下記の手順にしたがって数式を入力します。
C列にはリンクしたい先の名前を記載します。(例 C2:Bさん、C3:Cさん、・・・)
こちらで試したときのデータの配置を添付しました。(そちらのデータの配置は把握しておりません)

No.2の回答で、うまくいかなかった理由は、数式のシート名の部分が『sheet』になっているためと思われます。そちらのファイ...続きを読む

Qエクセルについて。

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

Aベストアンサー

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

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

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

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

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

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

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

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

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

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

QExcelで数値が入っているセルにおいてその数値を表示せず、代わりに任意の文字列を表示したいのですが

Excelで数値が入っているセルにおいてその数値を表示せず、代わりに任意の文字列を表示したいのですが何か方法はありますか?

あるセルに元の数値があってそれをベースに別のセルで計算を行っています。但しその元の数値を印刷時に見えなくし、代わりに任意の文字列を表示したいのです。

例えば社内でのみ閲覧可能な定価相当の数値があり、これにある掛け率で売価を計算しているとします。当然この売価は印刷時において見える必要があります。一方元の数値の入ったセル部分には実際には社内定価の数値が入っているのですがこれを表示するのではなく「オープン価格」として表示したいケースです。
いい方法はありますか?

Aベストアンサー

「セルの書式設定」-「ユーザ定義」で、”オープン価格” と定義してみては。

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エクセルの計算式を教えてください。

◆エクセルで下記データを抽出したいです。

商品が4種類あり下記表のように纏めております。
商品Aについては、条件Aと条件Aを満たすので、枚数を抽出したいです。
商品Bについては、条件Aと条件Bを満たすので、個数を抽出したいです。
商品Cについては、条件Bと条件Bを満たすので、個数を抽出したいです。
商品Dについては、条件Aと条件Aを満たすので、枚数を抽出したいです。
※更に出荷日毎に分けたいです。
下記に詳細を纏めました。
教えて下さい…

Aベストアンサー

こんばんは!

1行ずつ品名ごと、別々に数式を入れた方が簡単かもしれませんが、
一つの数式で対応できるようにしてみました。

↓の画像のような配置で元データはSheet1にあり、Sheet2に表示するとします。

Sheet2のB2セルに
=SUMIFS(IF(($A2="品名A")+(A2="品名D"),Sheet1!$F:$F,Sheet1!$E:$E),Sheet1!$A:$A,B$1,Sheet1!$B:$B,$A2,Sheet1!$C:$C,IF($A2="品名C","条件B","条件A"),Sheet1!$D:$D,IF(($A2="品名A")+($A2="品名D"),"条件A","条件B"))

という数式を入れフィルハンドルで列・行方向にコピーしています。

※ 一つ一つ数式を入れた方が判りやすい場合は
例えば、B2セルの数式は
=SUMIFS(Sheet1!$F:$F,Sheet1!$A:$A,B1,Sheet1!$B:$B,$A2,Sheet1!$C:$C,"条件A",Sheet1!$D:$D,"条件A")

として右へフィル&コピー!

上記数式を各「商品」の条件によって、
SUMIFS関数の「合計範囲」と各範囲の条件を一つ一つ入れ替えてやる必要があります。

※ 気になる点があります。
質問の文章は「商品○」となっていますが、
表の方は「品名○」になっていますよね?
数式は「品名○」の方でやっています。m(_ _)m

こんばんは!

1行ずつ品名ごと、別々に数式を入れた方が簡単かもしれませんが、
一つの数式で対応できるようにしてみました。

↓の画像のような配置で元データはSheet1にあり、Sheet2に表示するとします。

Sheet2のB2セルに
=SUMIFS(IF(($A2="品名A")+(A2="品名D"),Sheet1!$F:$F,Sheet1!$E:$E),Sheet1!$A:$A,B$1,Sheet1!$B:$B,$A2,Sheet1!$C:$C,IF($A2="品名C","条件B","条件A"),Sheet1!$D:$D,IF(($A2="品名A")+($A2="品名D"),"条件A","条件B"))

という数式を入れフィルハンドルで列・行方向にコピーしています...続きを読む

QExcel で特定のセルを固定表示したままシートをスクロールする

Excel で特定の行や列を固定表示したままシートをスクロールすることは[ウィンドウ枠の固定]でできるのですが、任意の位置にある特定のセルを固定表示することはできませんか?
[コメント]とか使ってやってみたのですがうまくいきません。
どうぞ宜しくお願いいたします。

Aベストアンサー

どういう目的で固定表示したいのですか?

Q【関数】複数条件に応じてポイントをつけたい

お世話になっております。

条件によってポイント付与する数式を組みたいのですが
条件が複雑で、どのような関数を組めば良いか、関数の知識も乏しく頭を悩ませております。
詳しい方教えて頂けないでしょうか?

K列 担当者ID
L列 計画
M列 実績
N列 達成率
O列 達成額/未達額
P列 ポイント付与欄

上記のようなデータがあります。

下記条件で、O列「達成額/未達額」の降順にポイントを付与したいです。
その際、下記の条件でポイントを付与したいのです。

ポイント集計先
①黄色セル K5~K18:担当者ID
➁赤色セル K4:部門コード
※画像添付いたします。

ポイント付与条件別表
黄色セル E列:担当者ID ※上記①と紐づく
ピンクセル F列:担当者に紐づく主担当部門コード ※上記➁と紐づく
※補足へ画像添付いたします。

★条件★
・基本
O列
①「達成額/未達成額」が0以上なら降順に10Pからポイント付与
➁「達成額/未達成額」が0orマイナスならポイント付与せず
※ただし、マイナスでも実績があればポイント付与

上記、基本条件に加えて、下記条件も組み込みたいです。
①担当者ID+主担当部門コードが紐づけはO列に10PからポイントMAX付与
➁担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与
かつ、「達成額/未達成額」が0orマイナスなら1/2のポイントの半分付与

何卒、よろしくお願いいたします。

お世話になっております。

条件によってポイント付与する数式を組みたいのですが
条件が複雑で、どのような関数を組めば良いか、関数の知識も乏しく頭を悩ませております。
詳しい方教えて頂けないでしょうか?

K列 担当者ID
L列 計画
M列 実績
N列 達成率
O列 達成額/未達額
P列 ポイント付与欄

上記のようなデータがあります。

下記条件で、O列「達成額/未達額」の降順にポイントを付与したいです。
その際、下記の条件でポイントを付与したいのです。

ポイント集計先
①黄色セル...続きを読む

Aベストアンサー

まず基本条件の方からいきます。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*RANK.AVG関数で”達成額/未達成額”の順にランキング(1~)をつけ、11から差し引く、という方法をとります
*”実績”がマイナスのものはランキング付けそのものから除外するので、IF関数で検索対象の配列を絞り込みます
(IF関数に配列を入れたら答えも配列で返してくれるので、その絞り込んだ配列に対してRANK.AVR関数を使います。)
*マイナスのポイントは排除したいので、MAX関数でゼロと比較して正の値のみを採択します

追加条件込みの方は、以下です。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0)*IF(VLOOKUP($K5,$E$3:$F$1000,2,FALSE)=$K$4,1,IF($O5>0,0.5,0.25)),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*担当IDから担当部門コードの検索にはVLOOK関数を使います。VLOOK関数が正常に動作するには、担当IDに漏れがない事と番号順に並んでいる事が必要ですので、これ前提で考えて下さい。
*追加条件に準じ、ポイントを1倍したり、0.5倍したり、0.25倍したり、しました。

まず基本条件の方からいきます。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*RANK.AVG関数で”達成額/未達成額”の順にランキング(1~)をつけ、11から差し引く、という方法をとります
*”実績”がマイナスのものはランキング付けそのものから除外するので、IF関数で検索対象の配列を絞り込みます
(IF関数に配列を入れたら答えも配列で返してくれるので、その絞り込んだ配列に対してRAN...続きを読む

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此れ、例によって、MSのミスですか?

添付図上段は、Excel 2013 のヘルプ[CELL 関数]からの抜粋です。
見づらければ、次の URL を参照してください。
http://support.office.com/ja-jp/article/cell-%E9%96%A2%E6%95%B0-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

添付図下段は私が検証した結果を示しています。
標準書式のA列の全セルには 2018/5/3 を入力しています。
A列の各セルに対して同行のB列に示す書式(format)を設定すると、A列は添付図に示す表示になります。
式 =CELL("format",A2) を入力して、此れを下方にズズーッとオートフィルした結果(戻り値)をC列に示しています。

赤地に白抜きした部分がヘルプと齟齬していますよね。
特に戻り値 D5 は何処にも登場しません。(どのようにすれば D5 が得られるのだろう?)

此れらの食い違いは、一体全体どういうことなのか教えてください。

Aベストアンサー

こんばんは。
>「ローカルバージョン」とは日本語で書けば“日本語版”という僅か4文字で済むことでしょ?
>>日本独特とは言わないまでも、アメリカのものとは違うということです。
と書きましたとおり、昔ならともかく、本当に「日本語」版というのが存在するならばです。

いずれにしても、ローカル・バージョンは、オリジナル・バージョンに対する意味です。私たちの使っているものが、厳密に日本語版とは言えないからなのです。それは、内部的に、特に韓国語バージョンは共有する部分が多いので、メニュー表示だけの違いだったという記憶があります。東アジアバージョンは、そう変わらないのではないでしょうか。

それに反して、「フランス語」や「ドイツ語」バージョンしか知りませんが、ヨーロッパの言語に関しては、その国の言語のバージョンは、私たちが使っているものとは大きく違うのではないかと思うのです。関数名自体が違います。そして、このアジア向けでは、Microsoft Office で、一度、問題が発生したことがあるのです。アジア向け(日本だけではない)に、一部の機能を抜いた製品を出荷したことがあるのです。そうしたら、ユーザー側で大変なクレームがついたのです。

>ご承知とは思いますが、私がたまに利用する
>http://answers.microsoft.com/ja-jp
>に投稿しても「そんなことは、Microsoft に問い合わせたら」と言われたことがあります。

これが、英語版でも同じような対応なのです。そもそも、回答者に肩書などいらないと思うのです。それで、最近は、Stack OverFlow サイトを読むことが多いのですが、ここでも解決はしませんね。今は、エクセル・ユーザーでも、一部はかなり上のステージに移ってしまいました。
今度、Excel 2019 では、Python が加わるとも聞きますが、どこまでやれるか分かりません。

問い合わせ先なのですが、間違っていたら申し訳ないのですが、ここのHelp画面の下にメールの送信マークがあります。ヘルプからだと、そこしか思いつきませんでした。

https://support.office.com/ja-jp/article/cell-%E9%96%A2%E6%95%B0-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

No.3さんの
>セルに独自の表示形式が設定されている場合は、セルの表示形式を表すコードが返されます。
#2のマクロ関数の GET.CELL(7, セル)だったら出せるけれども、CELL関数では無理なのではないでしょうか。

こんばんは。
>「ローカルバージョン」とは日本語で書けば“日本語版”という僅か4文字で済むことでしょ?
>>日本独特とは言わないまでも、アメリカのものとは違うということです。
と書きましたとおり、昔ならともかく、本当に「日本語」版というのが存在するならばです。

いずれにしても、ローカル・バージョンは、オリジナル・バージョンに対する意味です。私たちの使っているものが、厳密に日本語版とは言えないからなのです。それは、内部的に、特に韓国語バージョンは共有する部分が多いので、メニュー表示...続きを読む

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&Aランキング