ちゃんとヘルプで探してみて、それでもわからない（その確立の方が高いのですが・・・）時はお知恵を拝借しにうかがいます！！！
A B C D E ・・・・
1 60 男
2 94 男
3 82 女
4 59 男
5 77 女
6 101 女
■エクセルで上記のようにB列に数値（数値は年齢でこの年齢もA列に誕生日を入力しており自動計算させています）が入力されています。
■次にC列に?50〜70 → 1と年齢の区分を付け自動で計算されるようにし、
?71〜80 → 2
?81〜90 → 3
?91〜100 → 4
?100〜 → 5
※こんな感じに自動計算させたいです。
A B C D E ・・・・
1 60 1 男
2 94 4 男
3 82 3 女
4 59 1 男
5 77 2 女
6 101 5 女
■さらにD列の性別を 男なら1 女なら2 と区分をつけ先ほどの年齢の区分と
あわせE列に 11(セルE1) 13（セルE2) と表示をしたい
※最終的にこんな感じです。
A B C D E ・・・・
1 60 1 男 11
2 94 4 男 14
3 82 3 女 23
4 59 1 男 11
5 77 2 女 22
6 101 5 女 25
のですが、該当する関数があるのでしょうか？エクセルの関数の本を見てみたのですがわかりません。教えてください。
OSはＸＰでエクセルは2003を使っています。
1 60 男
2 94 男
3 82 女
4 59 男
5 77 女
6 101 女
■エクセルで上記のようにB列に数値（数値は年齢でこの年齢もA列に誕生日を入力しており自動計算させています）が入力されています。
■次にC列に?50〜70 → 1と年齢の区分を付け自動で計算されるようにし、
?71〜80 → 2
?81〜90 → 3
?91〜100 → 4
?100〜 → 5
※こんな感じに自動計算させたいです。
A B C D E ・・・・
1 60 1 男
2 94 4 男
3 82 3 女
4 59 1 男
5 77 2 女
6 101 5 女
■さらにD列の性別を 男なら1 女なら2 と区分をつけ先ほどの年齢の区分と
あわせE列に 11(セルE1) 13（セルE2) と表示をしたい
※最終的にこんな感じです。
A B C D E ・・・・
1 60 1 男 11
2 94 4 男 14
3 82 3 女 23
4 59 1 男 11
5 77 2 女 22
6 101 5 女 25
のですが、該当する関数があるのでしょうか？エクセルの関数の本を見てみたのですがわかりません。教えてください。
OSはＸＰでエクセルは2003を使っています。
|
|
|
|
コメント(16)
まずランキングの表を作ります。
試しに、以下のように入力してみてください
A11 = 50
A12 = 71
A13 = 81
A14 = 91
A15 = 100
この表を使ってMATXH関数でランキングわけします。
B1 = MATCH(A1,$A$11:$A$15,1)
ここでポイントは、MATCH関数内の一番最後の部分を「１」にすることです。
次に、男女の分岐ですが、IF関数でできます。
男のとき⇒ １＆MATCH関数で調べた値
男でないとき ⇒ ２＆MATCH関数で調べた値
だから、
D1 = IF(C1="男",1&MATCH(A1,$A$11:$A$15,1),2&MATCH(A1,$A$11:$A$15,1))
となります。
試しに、以下のように入力してみてください
A11 = 50
A12 = 71
A13 = 81
A14 = 91
A15 = 100
この表を使ってMATXH関数でランキングわけします。
B1 = MATCH(A1,$A$11:$A$15,1)
ここでポイントは、MATCH関数内の一番最後の部分を「１」にすることです。
次に、男女の分岐ですが、IF関数でできます。
男のとき⇒ １＆MATCH関数で調べた値
男でないとき ⇒ ２＆MATCH関数で調べた値
だから、
D1 = IF(C1="男",1&MATCH(A1,$A$11:$A$15,1),2&MATCH(A1,$A$11:$A$15,1))
となります。
丸付き数字は機種依存文字の一種で環境に依り文字化けして
読めない方もいるようですので使用は避けた方が無難です。
> ■次にC列に(1) 50〜70 → 1と年齢の区分を付け自動で計算されるようにし、
> (2) 71〜80 → 2
> (3) 81〜90 → 3
> (4) 91〜100 → 4
> (5) 100〜 → 5
> ※こんな感じに自動計算させたいです。
条件のパターンが少なければ以下のようにIF関数で場合分けすれば出来ますね。
条件のパターンがもっと多くなるようであれば、
年齢に対応してどの値を返すのかを定義した表を別セルに作成しておき、
その表からVLOOKUP関数で値を取得するような形式にした方が良いです。
以下の３つの質問に回答していただければ数式提示できます。
1. 上記の前者(パターン少、IFで解決)と後者(パターン増えるかも、VLOOKUPで解決)のどちらが良いか？
2. 50未満の場合はどうするのか？
3. 「91〜100」と「100〜」とあるが、100の場合はどっちなのか？
> ■さらにD列の性別を 男なら1 女なら2 と区分をつけ先ほどの年齢の区分と
> あわせE列に 11(セルE1) 13（セルE2) と表示をしたい
E1セルに以下の数式を入力し、あとはE2セル以降にコピペすれば良いです。
=IF(D1="男",1,IF(D1="女",2,""))&C1
読めない方もいるようですので使用は避けた方が無難です。
> ■次にC列に(1) 50〜70 → 1と年齢の区分を付け自動で計算されるようにし、
> (2) 71〜80 → 2
> (3) 81〜90 → 3
> (4) 91〜100 → 4
> (5) 100〜 → 5
> ※こんな感じに自動計算させたいです。
条件のパターンが少なければ以下のようにIF関数で場合分けすれば出来ますね。
条件のパターンがもっと多くなるようであれば、
年齢に対応してどの値を返すのかを定義した表を別セルに作成しておき、
その表からVLOOKUP関数で値を取得するような形式にした方が良いです。
以下の３つの質問に回答していただければ数式提示できます。
1. 上記の前者(パターン少、IFで解決)と後者(パターン増えるかも、VLOOKUPで解決)のどちらが良いか？
2. 50未満の場合はどうするのか？
3. 「91〜100」と「100〜」とあるが、100の場合はどっちなのか？
> ■さらにD列の性別を 男なら1 女なら2 と区分をつけ先ほどの年齢の区分と
> あわせE列に 11(セルE1) 13（セルE2) と表示をしたい
E1セルに以下の数式を入力し、あとはE2セル以降にコピペすれば良いです。
=IF(D1="男",1,IF(D1="女",2,""))&C1
＞よっちゃんさん
年齢の区分も性別と年齢の区分も出来ました
本当に助かりました
ありがとうございます
加えて、ご質問なのですが・・・・
（１）7のコメのようにif関数を（）でつなげればいくつでも条件の設定を出来るのでしょうか？
（２）上記の式をif関数を使わずvloolup関数を使った場合（やり方を4のコメで概ねコメントいただいていますが・・・）はどういった方式になるのでしょうか？
（３）今回のＥ１セルの関数 IF(D1="男",1,IF(D1="女",2,""))&C1
において ・・・”女”,2,""))＆C1 の ”” の部分はどういった意味を持つのですか？又 ＆■■ を使えば ■■ で指定したセルの値を ＆の前のセルの値とくっつけて該当のセルに値を表示できるのでしょうか？
あつかましくて申し訳ありませんが、勉強のためにも教えてください！！！
年齢の区分も性別と年齢の区分も出来ました
本当に助かりました
ありがとうございます
加えて、ご質問なのですが・・・・
（１）7のコメのようにif関数を（）でつなげればいくつでも条件の設定を出来るのでしょうか？
（２）上記の式をif関数を使わずvloolup関数を使った場合（やり方を4のコメで概ねコメントいただいていますが・・・）はどういった方式になるのでしょうか？
（３）今回のＥ１セルの関数 IF(D1="男",1,IF(D1="女",2,""))&C1
において ・・・”女”,2,""))＆C1 の ”” の部分はどういった意味を持つのですか？又 ＆■■ を使えば ■■ で指定したセルの値を ＆の前のセルの値とくっつけて該当のセルに値を表示できるのでしょうか？
あつかましくて申し訳ありませんが、勉強のためにも教えてください！！！
>>8 smileさん
おはようございます( = =)zZZ
思ったことダラダラ書いていたら長くなってしまい、
文字数制限ではじかれましたので２回にわけて投稿します^^;
------------------------------------------------
> （１）
今回はIF関数を繋げたわけではないことをまず覚えてください。
「繋げる」という表現ですと、文字数の制限に達するまで繋げられます。
例えば、 =IF(A1=1,1,2)+IF(A1=1,1,2)+・・・+IF(A1=1,1,2) のように。
今回は関数の中に関数を「入れ子」していることに注目してください。
この関数の入れ子のことを「ネスト」と言います。
ここで、Excelのヘルプにてキーワード「ネスト」で検索し、
「関数内にネストした関数について」という項目に目を通してください。
ヘルプにあるように、ネストレベルは 7 までできます。
ですので、最初の質問が「(5) 101〜 → 5」とならないで
もっと続くとなった場合、
=IF(B1<71,1,IF(B1<81,2,IF(B1<91,3,IF(B1<101,4,IF(B1<111,5,IF(B1<121,6,IF(B1<131,7,IF(B1<141,8,9))))))))
までは出来ますが、
=IF(B1<71,1,IF(B1<81,2,IF(B1<91,3,IF(B1<101,4,IF(B1<111,5,IF(B1<121,6,IF(B1<131,7,IF(B1<141,8,IF(B1<151,9,10)))))))))
は出来ないことになります。
※実際に上記の２つの数式をExcelにコピペして試してみてください。
※最初のIF関数から、ネストされているIF関数の数を数えてみてください。
コメント>>4で私が
> 条件のパターンがもっと多くなるようであれば、
と書いたのは、こういうことだったからです。
------------------------------------------------
> （２）
すごく長くなりそうですのである程度省略して書きます。
わからないところは質問してください。
まずはヘルプで「VLOOKUP」関数について調べてください。
別表の存在がわかりやすくなるようにG1セルに「年齢対応表」と入力。
G2に0、G3に71、G4に81、G5に91、G6に101、
H2に1、H3に2、H4に3、H5に4、H6に5、とそれぞれ入力。
C1セルに =VLOOKUP(B1,$G$2:$H$6,2,TRUE) と入力。
C2セル以降にC1セルをコピペ。
VLOOKUP関数の第4引数に TRUE(もしくは省略) を指定した時の
性質を利用し、検索対象範囲の左端の列の値を昇順に並べ替えた別表から
今回欲しい値を取得しています。
ヘルプに書いてあるはずですが、第4引数に FALSE を指定した際は
完全一致の値だけ検索され、指定した別表に対する列番号の値を返します。
ある表から特定の値を探す時などに重宝する関数です。
このように別表を設けておくと、例えば
70歳未満の場合に 1 ではなく A を返すようにした場合、
別表の値を書き換えすだけで済みます。
条件の場合わけが30パターンになった場合でも
別表の範囲を拡大すれば対応できます。
※C1セルの数式の参照範囲の拡大＆C2セル以降へのコピペも必要ですね。
LOOLUP関数でも、今回の場合は同様にことができます。
ヘルプでLOOKUP関数について調べて理解ができれば
コメント>>9の俄丸。/丁忍/ぺっちんさんの回答の意味が理解できると思います。
おはようございます( = =)zZZ
思ったことダラダラ書いていたら長くなってしまい、
文字数制限ではじかれましたので２回にわけて投稿します^^;
------------------------------------------------
> （１）
今回はIF関数を繋げたわけではないことをまず覚えてください。
「繋げる」という表現ですと、文字数の制限に達するまで繋げられます。
例えば、 =IF(A1=1,1,2)+IF(A1=1,1,2)+・・・+IF(A1=1,1,2) のように。
今回は関数の中に関数を「入れ子」していることに注目してください。
この関数の入れ子のことを「ネスト」と言います。
ここで、Excelのヘルプにてキーワード「ネスト」で検索し、
「関数内にネストした関数について」という項目に目を通してください。
ヘルプにあるように、ネストレベルは 7 までできます。
ですので、最初の質問が「(5) 101〜 → 5」とならないで
もっと続くとなった場合、
=IF(B1<71,1,IF(B1<81,2,IF(B1<91,3,IF(B1<101,4,IF(B1<111,5,IF(B1<121,6,IF(B1<131,7,IF(B1<141,8,9))))))))
までは出来ますが、
=IF(B1<71,1,IF(B1<81,2,IF(B1<91,3,IF(B1<101,4,IF(B1<111,5,IF(B1<121,6,IF(B1<131,7,IF(B1<141,8,IF(B1<151,9,10)))))))))
は出来ないことになります。
※実際に上記の２つの数式をExcelにコピペして試してみてください。
※最初のIF関数から、ネストされているIF関数の数を数えてみてください。
コメント>>4で私が
> 条件のパターンがもっと多くなるようであれば、
と書いたのは、こういうことだったからです。
------------------------------------------------
> （２）
すごく長くなりそうですのである程度省略して書きます。
わからないところは質問してください。
まずはヘルプで「VLOOKUP」関数について調べてください。
別表の存在がわかりやすくなるようにG1セルに「年齢対応表」と入力。
G2に0、G3に71、G4に81、G5に91、G6に101、
H2に1、H3に2、H4に3、H5に4、H6に5、とそれぞれ入力。
C1セルに =VLOOKUP(B1,$G$2:$H$6,2,TRUE) と入力。
C2セル以降にC1セルをコピペ。
VLOOKUP関数の第4引数に TRUE(もしくは省略) を指定した時の
性質を利用し、検索対象範囲の左端の列の値を昇順に並べ替えた別表から
今回欲しい値を取得しています。
ヘルプに書いてあるはずですが、第4引数に FALSE を指定した際は
完全一致の値だけ検索され、指定した別表に対する列番号の値を返します。
ある表から特定の値を探す時などに重宝する関数です。
このように別表を設けておくと、例えば
70歳未満の場合に 1 ではなく A を返すようにした場合、
別表の値を書き換えすだけで済みます。
条件の場合わけが30パターンになった場合でも
別表の範囲を拡大すれば対応できます。
※C1セルの数式の参照範囲の拡大＆C2セル以降へのコピペも必要ですね。
LOOLUP関数でも、今回の場合は同様にことができます。
ヘルプでLOOKUP関数について調べて理解ができれば
コメント>>9の俄丸。/丁忍/ぺっちんさんの回答の意味が理解できると思います。
> （３）の１つめ
引用はなるべく正確になるようにコピペで行ってください。
半角と全角のダブルクォーテーション( "" と ””)は全く別物です。
数式内で文字列を直接扱う場合、半角のダブルクォーテーションでくくります。
=A1 とすると、A1セルに対する参照となりますが、
="A1" とすると、「A1」という文字列になります。
=IF(D1="男",1,IF(D1="女",2,"")) を無理やり日本語にしてみましょう。
↓
=もし(D1セルの値が男という文字列だったら,数値の1を返す,そうでなかったらこうする))
「そうでなかったらこうする」の部分は以下とする。
=もし(D1セルの値が女という文字列だったら,数値の2を返す,そうでなかったら空っぽの文字列を返す)
上の２つを１つにまとめるとこんな感じです
↓
=もし(D1セルの値が男という文字列だったら,数値の1を返す,そうでなくてもし(D1セルの値が女という文字列だったら,数値の2を返す,そうでなかったら空っぽの文字列を返す))
ちょっと説明が冗長になりましたが、要するに "" は文字列を表すということです。
=IF(D1="男",1,IF(D1="女",2,"")) は
=IF(D1="男",1,IF(D1="女",2,"性別が未入力！")) でもいいです。
=IF(D1="男",1,IF(D1="女",2,3)) でもいいです。
何行か上で
> 数式内で文字列を直接扱う場合、半角のダブルクォーテーションでくくります。
と書きました。
もし、別セル、例えばA2セルに 男 、 A3セルに 女 と入力されていた場合は
=IF(D1=A2,1,IF(D1=A3,2,"")) となります。
数式内のA2 等を "" でくくってしまうと A2 という文字列と比較することになります。
------------------------------------------------
> （３）の２つめ
全角の ＆ ではなく、半角の & ですね。
Excelには算術演算子、文字列演算子、比較演算子、参照演算子という
４種類の演算子があり、 & は文字列演算子と言い、
文字列と文字列を繋げる役割をしてくれます。
(勉強のためにと思うのでしたらヘルプで「演算子」について調べてみてください。)
A1セルに あい 、 A2セルに うえ と入力し、
B1セルに =A1&A2&"お" と入力してみてください。
そういうことです。
> 文字列と文字列を繋げる役割をしてくれます。
と書きましたが、数値と数値や、数値と文字列もできます。
同様の機能を果たす関数もあります。
CONCATENATE関数です。
=CONCATENATE(A1,A2,"お")
こちらは引数が最大30個までという残念な制限付がありますが、
数式がごちゃごちゃして見難くなりそうな場合には
私は好んで & 演算子ではなく CONCATENATE関数を利用します。
------------------------------------------------
他にも何か質問がありましたどうぞ。
これを機にヘルプも今まで以上に活用してみてください。
引用はなるべく正確になるようにコピペで行ってください。
半角と全角のダブルクォーテーション( "" と ””)は全く別物です。
数式内で文字列を直接扱う場合、半角のダブルクォーテーションでくくります。
=A1 とすると、A1セルに対する参照となりますが、
="A1" とすると、「A1」という文字列になります。
=IF(D1="男",1,IF(D1="女",2,"")) を無理やり日本語にしてみましょう。
↓
=もし(D1セルの値が男という文字列だったら,数値の1を返す,そうでなかったらこうする))
「そうでなかったらこうする」の部分は以下とする。
=もし(D1セルの値が女という文字列だったら,数値の2を返す,そうでなかったら空っぽの文字列を返す)
上の２つを１つにまとめるとこんな感じです
↓
=もし(D1セルの値が男という文字列だったら,数値の1を返す,そうでなくてもし(D1セルの値が女という文字列だったら,数値の2を返す,そうでなかったら空っぽの文字列を返す))
ちょっと説明が冗長になりましたが、要するに "" は文字列を表すということです。
=IF(D1="男",1,IF(D1="女",2,"")) は
=IF(D1="男",1,IF(D1="女",2,"性別が未入力！")) でもいいです。
=IF(D1="男",1,IF(D1="女",2,3)) でもいいです。
何行か上で
> 数式内で文字列を直接扱う場合、半角のダブルクォーテーションでくくります。
と書きました。
もし、別セル、例えばA2セルに 男 、 A3セルに 女 と入力されていた場合は
=IF(D1=A2,1,IF(D1=A3,2,"")) となります。
数式内のA2 等を "" でくくってしまうと A2 という文字列と比較することになります。
------------------------------------------------
> （３）の２つめ
全角の ＆ ではなく、半角の & ですね。
Excelには算術演算子、文字列演算子、比較演算子、参照演算子という
４種類の演算子があり、 & は文字列演算子と言い、
文字列と文字列を繋げる役割をしてくれます。
(勉強のためにと思うのでしたらヘルプで「演算子」について調べてみてください。)
A1セルに あい 、 A2セルに うえ と入力し、
B1セルに =A1&A2&"お" と入力してみてください。
そういうことです。
> 文字列と文字列を繋げる役割をしてくれます。
と書きましたが、数値と数値や、数値と文字列もできます。
同様の機能を果たす関数もあります。
CONCATENATE関数です。
=CONCATENATE(A1,A2,"お")
こちらは引数が最大30個までという残念な制限付がありますが、
数式がごちゃごちゃして見難くなりそうな場合には
私は好んで & 演算子ではなく CONCATENATE関数を利用します。
------------------------------------------------
他にも何か質問がありましたどうぞ。
これを機にヘルプも今まで以上に活用してみてください。
惜しいなあ…
性別を「おとこ」、「おんな」とわざわざ入力してさらに「1」と「2」に直したいんだよね
実に惜しい…
なんかさっき閃いたんだ
俺だったらこうすんの↓
■性別の入力値
男なら「1」、女なら「0」と入力する（もちろん逆でもいいけど）
■性別を入力する列の書式設定
１）
「セルの書式設定」の「表示」タブから「ユーザー設定」を選ぶ
２）
「種類」ボックスに表示している値を消去し以下を入力
"男";;[赤]"女"
どうなるかというと、性別の列に 1 を入力すると「男」と表示し 0 と入力すると赤色で「女」と表示される
つまり計算式なんて要らないよ
※本当は負数、ゼロ、正数で表示を変える書式なんだ
だから実は 2 でも「男」と表示されるけどね
ま、そんな時は「入力規制」かけりゃいいワケだし
また別の列でどうしても 1 と 2 で表示したいんなら +1 すりゃいいだけかと
性別を「おとこ」、「おんな」とわざわざ入力してさらに「1」と「2」に直したいんだよね
実に惜しい…
なんかさっき閃いたんだ
俺だったらこうすんの↓
■性別の入力値
男なら「1」、女なら「0」と入力する（もちろん逆でもいいけど）
■性別を入力する列の書式設定
１）
「セルの書式設定」の「表示」タブから「ユーザー設定」を選ぶ
２）
「種類」ボックスに表示している値を消去し以下を入力
"男";;[赤]"女"
どうなるかというと、性別の列に 1 を入力すると「男」と表示し 0 と入力すると赤色で「女」と表示される
つまり計算式なんて要らないよ
※本当は負数、ゼロ、正数で表示を変える書式なんだ
だから実は 2 でも「男」と表示されるけどね
ま、そんな時は「入力規制」かけりゃいいワケだし
また別の列でどうしても 1 と 2 で表示したいんなら +1 すりゃいいだけかと
ﾃﾚｯ､ﾃｯﾃﾚｯｰさんの方法のパターンですが
セルの書式設定→ユーザー設定 により
[=1]"男";[=2]"女"
とすると、１を入力することで「男」２を入力することで「女」と表記されます。
もちろん今回の場合、十の位になりますので、[=10]"男";[=20]"女"として、10と20を入力するようにする方法もあります。
=IF(A2<=50,"",IF(A2<=70,1,IF(A2>=101,5,ROUNDUP((A2-60)/10,0))))
↑50以下空白 ↑70以下１ ↑101以上5 ↑その他の条件
というように計算式に書き換えられるものを置き換えてしまうとネストを節約できるかも知れません。
男／女の条件を加えて
=IF(A2="男",10,IF(A2="女",20,""))+IF(B2<=50,"",IF(B2<=70,1,IF(B2>=101,5,ROUNDUP((B2-60)/10,0))))
という関数も可能かも知れません。
セルの書式設定→ユーザー設定 により
[=1]"男";[=2]"女"
とすると、１を入力することで「男」２を入力することで「女」と表記されます。
もちろん今回の場合、十の位になりますので、[=10]"男";[=20]"女"として、10と20を入力するようにする方法もあります。
=IF(A2<=50,"",IF(A2<=70,1,IF(A2>=101,5,ROUNDUP((A2-60)/10,0))))
↑50以下空白 ↑70以下１ ↑101以上5 ↑その他の条件
というように計算式に書き換えられるものを置き換えてしまうとネストを節約できるかも知れません。
男／女の条件を加えて
=IF(A2="男",10,IF(A2="女",20,""))+IF(B2<=50,"",IF(B2<=70,1,IF(B2>=101,5,ROUNDUP((B2-60)/10,0))))
という関数も可能かも知れません。
- mixiユーザー
- ログインしてコメントしよう！
|
|
|
|
