Excel。PHONETIC関数を使って郵便番号入力を省略する方法

Excel。
PHONETIC関数を使って郵便番号入力を省略する方法

PHONETIC関数とIME言語バー　＋αで、ASC+PHONETIC関数

フリガナを表示する関数。PHONETIC関数。
これの関数とIME言語バーを使って、郵便番号から、住所を入力支援する技をご紹介。

ちょっと、分かりにくいですが、
まぁ、PHONETIC関数がフリガナ以外で使い道がないと思われると、
可愛そうなので、早速やってみましょう。

まず、IME言語バーの変換モードを【人名/地名】に変えておきます。
入力モードは【ひらがな】です。

この状態で、郵便番号をハイフン付きで、入力して漢字変換と同じように変換すると、住所に変換されるのです。
２１０－０００１　→　神奈川県川崎市川崎区本町
と変換されるのです。で、これを使う訳です。
下記のような表があったとして、

まずは、C3をクリックして、郵便番号を入力します。例として、２１０－０００１とします。

そして、変換すると、神奈川県川崎市川崎区本町と変換されます。

次にB3をクリックして、PHONETIC関数を作ってみましょう。
PHONETIC関数のダイアログボックスを表示して、参照はC3で、OKをクリックします。

そうすると、郵便番号が表示されます。

C3に郵便番号を入力して、変換すると、自動的に郵便番号がB3に表示される仕組みの完成です。

あとは、郵便番号が全角なので、半角にしてみましょう。
全角を半角に変換する関数は、ASC関数ですので、ASC+PHONETIC関数で出来ますね。
では、作ってみましょう。B3をいったん削除しておきましょう。
B3にASC関数のダイアログボックスを表示します。

文字列の中には、名前ボックス▼から、PHONETIC関数を挿入します。

これで、完成です。

Excel。大文字・小文字・頭文字は大文字に変換するには UPPER関数とLOWER関数とPROPER関数

Excel。大文字・小文字・頭文字は大文字に変換するには

UPPER関数とLOWER関数とPROPER関数

先日、講義で、文字操作でASC関数やJIS関数を紹介したところ、
食いつきがよかったので、＋αで、
大文字・小文字・頭文字を大文字に統一する方法をご紹介しました。

下記のようなデータがあったとして、

まずは、大文字に変換する、UPPER関数からご紹介します。
C3をクリックして、UPPER関数のダイアログボックスを表示しましょう。

あとは、B3をクリックして完成。簡単ですね。
=UPPER(B3)

つぎは、小文字に変換する、LOWER関数をご紹介。
C4をクリックして、LOWER関数のダイアログボックスを表示しましょう。

あとは、B4をクリックして完成。簡単ですね。
=LOWER(B4)

つぎは、頭文字だけを大文字に変換する、PROPER関数をご紹介。
C5をクリックして、PROPER関数のダイアログボックスを表示しましょう。

あとは、B5をクリックして完成。簡単ですね。
=PROPER(B5)

で、これで終わったら、面白くないので、
ちょっと、アレンジ技をご紹介しましょう。
入力データに不備がある場合の一つのケースに、
アルファベットや数字など、半角全角が混ざってしまっている。

大文字小文字もバラバラ

なんてことがあります。
そこで、
下記のような表があったとして、

パターン1は、変更前がacＣ入力されております。Cが全角なんですね。
変更後は、半角の大文字で統一したい場合は、

ASC+UPPER関数

のネストを使ってみると…
ということで、やってみましょう。

まず、C6をクリックして、半角にするASC関数のダイアログボックスを表示しましょう。

文字列に、UPPER関数を挿入していきます。
名前ボックスの▼をクリックして、ネストを作成していきましょう。

これで、完成。半角で大文字になりました。
=ASC(UPPER(B6))　という数式が作成されております。

今度は、全角の大文字で統一したい場合は、

JIS+UPPER関数

のネストを使ってみると…
ということで、やってみましょう。
まず、C7をクリックして、全角にするJIS関数のダイアログボックスを表示しましょう。

文字列に、UPPER関数を挿入していきます。
名前ボックスの▼をクリックして、ネストを作成していきましょう。

これで、完成。半角で大文字になりました。
=JIS(UPPER(B7))　という数式が作成されております。

なかなか、文字操作の関数は単発で使うことは少なくて、ネストで使うことが多いように感じされますので、色々試してみるといいかもしれませんね。

完成は、こんなです。

Excel。関数を知っていればいいという訳ではない。文字列結合は&が楽 CONCATENATE関数

Excel。関数を知っていればいいという訳ではない。
文字列結合は&が楽

CONCATENATE関数

下記のように、都道府県と市区町村を合わせた、
住所１という列を作成する場合、文字列結合を行いたいわけです。
そこで、登場する関数が、

CONCATENATE関数。

しかしまぁ…なんだけど、まずは、関数を紹介しましょう。

下記のようなデータがあるとします。

E2をクリックして、CONCATENATE関数のダイアログボックスを表示します。
文字列1に都道府県のC2をクリック
文字列2に市区町村のD2をクリック

これで、完成ですね。

ところが、この関数、実は…。ほとんど使わない。下記の方法が楽なので、
ということで、もう一つのやり方をご紹介します。

E2をクリックして、数式を作っていきます。

=C2&D2

これで、OK。完成です。要するに、文字列同士を&で接着したワケです。
何でもかんでも、関数を知っていればいいという訳ではないケースですね。
関数にとらわれず幅広くスキルを知っておきたいものですね。

Excel。全角を半角に、半角を全角に変換するには ASC関数とJIS関数

Excel。全角を半角に、半角を全角に変換するには

ASC関数とJIS関数

作業がうまくいかないと、聞く中で、
全角と半角のデータの混在というものが結構あることに気づかされます。

確かに、入力業務だけをまかせていると、
全角・半角のユレは気にしないかもしれませんが、
データを使う側としては、違う訳ですね。

そこで、今回は、

全角を半角に、半角を全角にする方法

をご紹介しましょう。

まずは、全角を半角にする方法から。
このケースは、住所の番地がそれにあたりますね。
下記のようなデータがあるとします。

今回は、F列の番地が全角なので、G列に半角の番地を作成してみましょう。
全角を半角にする関数は、

ASC関数ですね。

まずは、G2をクリックして、ASC関数のダイアログボックスを挿入します。
そして、F2をクリックして、OKボタンをクリックして完成。

簡単ですね。
次は、半角を全角にしてみましょう。
マンション名などを半角カタカナで入力してあったのを
全角にしたいなどの時に使いますね。下記のようなデータがあったとします。

半角を全角にする関数は、

JIS関数ですね。

まずは、D2をクリックして、JIS関数のダイアログボックスを挿入します。
そして、C2をクリックして、OKボタンをクリックして完成。

Excel。３分割に判定する時はCHOOSE+SIGN関数でも出来るけど…

Excel。３分割に判定する時は
CHOOSE+SIGN関数でも出来るけど…

CHOOSE+SIGN関数

↑・→・↓という3分割に判定しようとする。
第2弾。
前回はTEXT関数でのやり方をご紹介しましたが、
今回紹介するCHOOSE+SIGN関数の組み合わせでも出来ますので、ご紹介しましょう。

まず、CHOOSE関数の特長を見てみましょう。
この関数は、インデックスの値によって、1ならば、どうする。2ならばどうする。というように指定できる関数です。
では、このCHOOSEという関数を使って、3分割しようとした場合、1ならば、2ならば、3ならば、というように、1・2・3という数字を算出できる計算式を作る必要があるということに気が付きます。
では、どうやって、求めたらいいでしょうか？
そこで、登場するのが、SIGN関数です。
このSIGN関数は、正の数ならば1を、零であれば0を、負の数ならば、-1を判定し算出してくれるのです。つまり、今回から前回を引いて、今回の成績がよければ、正の数の1を算出します。逆に前回の成績がよければ、負の数の-1を算出します。
ならば、算出した数に2を加算してあげれば、1・2・3となるわけです。
わかりやすく、表にしてみます。

この判定を使って、早速、CHOOSE+SIGN関数を作っていきましょう。

まず、E4をクリックして、CHOOSE関数のダイアログボックスを表示しましょう。

インデックスに、SIGN関数をネストしていきます。
名前ボックスの▼をクリックして、挿入します。
SIGN関数のダイアログボックスを表示したら、数値に式を作成していきます。
D4-C4

と入力して、CHOOSE関数に戻りましょう。

数式バーの関数名をクリックすると、ダイアログボックスがそのクリックした関数に変わります。
そして、
インデックスには、SIGN(D4-C4)の後ろに+2を加筆します。
インデックスは、SIGN(D4-C4)+2
値1は、負の場合なので、↓を、値2は、零なので、→を、値3は、正の場合なので、↑を入力しましょう。

これで、完成です。

なお、D4-C4を逆にC4-D4にした場合は、値1が↑・値2が→・値3が↓にすればOKです。

Excel。３分割に判定する時はIF+IFじゃなくても、出来るんです。TEXT関数

Excel。３分割(↑・→・↓)に判定する時はIF+IFじゃなくても、
出来るんです。

TEXT関数

↑・→・↓という3分割に判定しようとすると、
IF+IF関数ということで、判定をすることが多いと思いますが、
実のところ、IF+IF関数でなくても、簡単に作れちゃうのです。それを今回はご紹介。

さて、下記のようなリストがあります。
1回目と2回目で得点がアップしたのか、ダウンしたのか、
そのままだったのか。というのを判定したいわけです。

通常ならば、IF+IF関数で判定する数式を作っていきますね。
まずは、こちらから、やってみましょう。
E4をクリックして、IF関数のダイアログボックスを表示します。
論理式には、D4>C4を入力します。
2回目は1回目より大きいか？ということですね。
真の場合には、”↑”を入力します。
2回目のほうが大きい時は↑という文字を表示させます。
偽の場合には、また判定をさせるために、IF関数をいれていきます。

名前ボックスの▼をクリックして、IF関数のダイアログボックスを表示します。
論理式には、
D4<C4と入力します。
=でもいいのですが、分かりにくいので、小さいか？と聞きます。
真の場合には、”↓”を入力します。
2回目のほうが小さい時は↓という文字を表示させます。
偽の場合には、残りの判定、つまり同じだったら、ということで、→と入力します。

これで、IF+IF関数の3分割判定の数式は完成しました。
あとは、オートフィルを使ってコピーします。

このようになりますね。
これでも、十分なのですが、今度はTEXT関数を使って作成してみましょう。

上記のような表があります。
E4をクリックして、TEXT関数のダイアログボックスを表示します。
値には、D4-C4と入力します。
これは、結果が正か負か零かを判定させるための式です。

表示形式には、”↑;↓;→”と入力します。

これはなんなのか？
なんで、↑・→・↓ではないのかというと、
表示形式は;（セミコロン）で区切ると、正数・負数・ゼロとそれぞれの場合、
表示形式をどうするのか？と設定することが出来るの特長を使った技です。

表示形式のユーザー定義書式の考え方とTEXT関数を知っていると、
IF+IF関数のネスト構造にしなくても3分割判定が、出来ちゃうんですね。

けど、この技。集中講義とかでは、披露しません。
なぜならば、IF+IF関数はネスト構造の基本。
練習していただきたいのと、
IF関数と、ごっちゃになる人がいるので、やらないわけですね。

まぁ、知っておいて損はないでしょう。

Excel。重複の入力を警告するテクニックは【入力規則+COUNTIF関数】で結構簡単に出来る。

Excel。重複の入力を警告するテクニックは
【入力規則+COUNTIF関数】で結構簡単に出来る。

入力規則+COUNTIF関数

現場からよく聞くものの中に、「重複」というキーワードがあります。
今回は、重複入力を防ぐためのテクニックで、入力を警告する技をご紹介します。
結構簡単なので、覚えるといいかも。

さて、下記のようなリストがあります。品番が重複しないようにしたいわけですね。

まずは、A列を全選択して、

データタブのデータの入力規則をクリックして、設定のユーザー設定を選択します。

数式に、

=countif(a:a,a1)=1

と入力して、エラーメッセージに、メッセージを入力しましょう。
数式は、ワザと小文字で入力してエラーがなく確定すると、大文字に変わりますので、入力ミスのチェックにもなりますので、小文字で入力をお勧めします。
コレで完成！

試しに、入力してみると、

きちんと、メッセージが表示されて、重複登録が出来ないようになりました。
どういう仕組みかというと、
=countif(a:a,a1)=1　は、
セルの値が列内に1個ある。という意味で、入力したものが列内に数えたら１個だけなら、イコール１で、入力OKとなり、すでに入力されていると、入力したものも含めて列内を数えたら2個あれば、イコール１でなくなるので、エラーとなり、入力出来ないという訳です。

Excel。RANK関数ほど有名じゃないけど、順位の関数も便利です。

Excel。RANK関数ほど有名じゃないけど、
順位の関数も便利です。

LARGE関数・SMALL関数

研修やらなんやらで、結構ご紹介することがある、

RANK関数。

RANK関数は、順番を付ける関数なのですが、あくまでも順番を付ける”だけ”の関数。

たとえば、データが膨大になると、
どれが、1位はどれか、2位はどれだ、と探すのが大変。
並び替えをすれば、見つかりますが、最初から、1位・2位を見つけるだけなのに、
RANK関数をつかって、並び替えてとか、
オートフィルターを使って、抽出するのも、工程数がかかるわけですね。

たしかに、最大・最小を見つけるだけなら、
MAX関数とMIN関数で用は足りますが、
2位以降～ブービーまでは対応できない。

そこで、紹介するのが、

LARGE関数・SMALL関数。

この関数を使えば、
ダイレクトに、どのデータが何位なのかを見つけることが出来ます。
下記のデータを使って、ご紹介。

まずは、

LARGE関数。

1位をMAX関数ではなく、LARGE関数で算出してみます。
F4をクリックして、LARGE関数のダイアログボックスを表示させたら、

配列は、データを選択して、順位は1位の1。これでOK。

今度は、

SMALL関数。

作り方はLARGE関数と同じですね。

順位に2をいれることによって、ブービーを求めることができます。
なお、同じ値の場合は、同じ順位で、それ以降の順位が繰り下がっていきます。