二つのデータを比較して、違いを見つける方法。

同じフォーマットで主力されたデータを比べる方法を紹介します。

例えば、

会社の支払いデータと取引先への請求書データを確認したり、仕入れデータと出荷データを比べたりするのに使います。

一つや二つのデータであれば、比べるのは簡単ですが、100や200になってくると結構大変です。

時間もかかりますし、間違えるリスクも上がります。

エクセルでは、このあたりは得意分野になりますので、しっかり覚えて活用しましょう。

今回は「IF関数」を活用します。

□「IF（イフ）」関数とは？
djiro.hatenablog.com

 ↑ここで詳しく説明しているので、気になる方は読んでみてください。

□ 2つのデータを比較し、差異を見つける

「シート1」と「シート2」を比較します。新たに「比較」というシートを作成してください。

①「シート2」をコピーして「比較」を作成

↓

②「Delete」キーで削除して空のセルにする

※ワンポイント

対象セルを削除するときに、「Backspace」を使って削除する事がよくあります。

エクセルの場合、対象セルが1つ以上あって、それを選択してまとめて消す場合は、「Delete」キーを活用します。

こうする事で、選択範囲をまとめて消せます。

当たり前かもしれませんが、意外と知らない方が多く、以前「Backspace」キーで一つずつ消している人を目撃したので記載しておきます。

↓

③IF関数を活用する

「＝IF（シート1！B2＜＞シート2！B2，シート2！B2ーシート1！B2，“”）」

※「シート1」と「シート2」を比較して、一致しない場合は「差分」を表示し、一致しない場合は空白を表示します。

↓

④コピーして完成

□上達するためのワンポイント：表示された値に自動的に色をつける

セルの書式設定で、マイナス数値を赤で表示します。

完成した表を全体選択して、「セルの書式設定」で変更します。

比較したデータに対して、見やすいように差をつけることも大切です。

あらかじめ、書式設定をしておけば色を変えることは簡単ですので、覚えておきましょう。

セル範囲に名前を付けて作業効率を上げる方法。

セルやセル範囲を指定する時に、名前をつけておくと便利です。

例えば、

「A2＊B2」のような数式を見ても、何を計算しているかわかりません。

・「A列」＝単価

・「B列」＝数量

上記のようにセルに名前をつけておくと、「＝単価＊数量」というように一目で数式が何を表しているかわかります。

また、わかりやすいだけでなく範囲をまとめて計算することが可能になり、数式を短縮することもできます。

具体的に方法を説明していきます。

□セル範囲に名前をつけます。

セル範囲を選択し、画面左上の「名前ボックス」から名前をつける事ができます。

今回は、

・「A2：A13」→「単価」

・「B2：B13」→「数量」

と、名前を設定します。

①セル範囲を選択する（A2：A13）

②「名前ボックス」に「単価」と入力

※「B2：B13」も同様の手順で行います。

※注意

選択する時に列を丸ごと指定すると、数式を組んだ時にエラーが出ます。

□設定した名前の管理方法

「数式」タブから「名前の管理」をクリックすると、

現在使用している名前の一覧を確認したり、新規作成や編集を行なったりできます。

いろいろできますので、迷ったらこのボタンを押しましょう。

※作成した「数量」と「単価」を確認してみましょう。

□名前をつけたセル範囲をまとめて計算する

セル範囲に付けた名前を利用して、数式を作成します。今回は「SUM（サム）」関数を使用します。

①「SUM（単価＊数量）」を入力

②「Ctrl」＋「Shift」＋「Enter」を押して合計金額を表示

※もう少し詳しく解説

入力した数式を確認すると「{ }」で囲まれていることがわかります。

画像

このカッコに挟まれている数式を「配列数式」といいます。

配列とは情報が入った箱が連なっているイメージです。

画像

この数式に当てはめて考えると、

「SUM（単価＊数量）」という一つの数式で、「2列目の（単価＊数量）」＋「3列目の（単価＊数量）」＋「4列目の（単価＊数量）」、、、、。

というふうになり、名前をつけた数式を全て合計してくれています。

↓

これを配列数式を使わずに表現すると、

「＝C2＊D2」＋「＝C3＊D3」＋「C4＊D4」、、、、。

いくつも数式を入力しないといけなくなり大変です。

配列数式を使えば一発で終わるので、かなり時短になります。

□上達のためのワンポイント：エラーが出た時の対処方法

エラーが出る時の多くの原因は「設定した名前を変更・もしくは削除してしまった」場合です。

配列数式は、設定したセル範囲の名前と紐づけているので、名前が変わってしまうとエラーが出てしまいます。

エラー表示になってしまった時は、自分が設定した名前が変わっていないか確認してみましょう。

□まとめ

※選択したセル範囲を「名前ボックス」から変更する。

※「数式」タブから「名前の管理」で編集作業ができる。

※設定した名前を使って、配列数式を利用すると作業が効率的に進めれる。

※エラーが出た場合は設定した名前が変更していないか確認する。

複雑な表をグループ化して、見やすくする方法。

行数や列数が多い表では、目的のセルを探すだけでも結構大変です。

今回はタスクごとにグループ化して開閉する方法を解説します。

僕がエクセルを積極的に使い始めた頃、この方法を知らずによくスクロールして探してい他ので、時間がかかって大変でした。

簡単な機能ですが、意外と知らずに使っている人も多いと思います。

他の人が使っても、対象のセルがすぐに見つけられるようにすることも、身近な業務改善になりますので、ぜひ活用してみてください。

□タスクをグループごとにまとめる。

グループ化するのはとても簡単で「Shift」＋「Alt」＋「→」で可能です。

具体的な手順をまとめます

①対象の行を全て選択する。

↓

②「Shift」＋「Ait」＋「→」でグループ化する。

※ショートカットキーを使わずに、「データ」タブの「グループ化」からも可能です。

しかし、探す時間がもったいないのでショートカットキーで覚えた方が効率的です。

↓

③完成

列の左側に「ー」（マナイス）表示されているか確認してください。

・「ー」（マイナス）アイコン：閉じる

・「＋」（プラス）アイコン：開く

□グループ化を解除する方法

グループ化を解除するには、「Shift」＋「Alt」＋「←」のショートカットキーで可能です。

グループ化する時と矢印が逆なのでわかりやすいと思います。

「データ」タブをクリックして「グループ解除」をクリックしても解除できますが、

これも時間がかかるので、ショートカットキーで覚えてしまいましょう。

□上達のためのワンポイント：グループ化の中にグループを作る方法

グループ化したカテゴリーの中を更に細分化してグループ化することも可能です。

最大8階層まで作成可能です。

グループ化したカテゴリーの中で更にグループ化すると、左上に「1」「2」・・・。と、

番号が付いたボタンが表示されます。

この数字が階層を表しており、「1」「2」・・・「8」まで可能だということです。

□まとめ

※グループ化：「Shift」＋「Alt」＋「→」

　「データ」タブから「グループ化」でも可能

※グループ化解除：「Shift」＋「Alt」＋「←」

　「データ」タブから「グループ化解除」でも可能

複数シートのデータを一つにまとめる方法。ちょっと難しいよ！

複数のシートに表記しているデータを一つのシートにまとめることはよくあります。

集計用のシート、提出用のシート、各個人別に分けたシート、各支店ごとに分けたシート、日付ごとに分けたシート、、、。

とにかく例を挙げればキリがりません。

それらを一つのシートにまとめる時に、一つずつコピーしてまとめていくのは大変です。

今回はこんな時にお勧めな「INDIRECT（インダイレクト）」関数を使って効率的に作業する方法を解説していきます。

□「INDIRECT（インダイレクト）関数」基本的な考え方

A1セルに「6」という数字を入れて、A6セルに「お金」という文字を入れます。

C2とC3のセルに２つの数式を入れてみます。

・C2：「“A“ & A1」

・C3：「INDIRECT（“A“ & A1）」

この数式の回答はこうなります↓

これを図式化して解説するとこうなります↓

簡単に説明するとこういう仕組みな訳ですが、

この関数をどのように活用するかさらに具体的に解説しています。

□別のシートにあるデータを取得する

上記で説明している「INDIRECT（インダイレクト）」関数を具体的にどのように活用していくか具体的に説明してみます。

もう一度ここで復習しますが、

この関数は、セルの参照を文字列で指定して取得する関数です。

引数は1つだけで、参照先を文字列で指定します。

具体的な例を入れて数式にしてみます。こんな感じ↓

　○結構複雑ですので詳しく解説します。

①「aさん」という名前のシートの「B12」セルから値を取ってきます。これを数式にすると「aさん！B12」という文字列になります。これを引数として挿入すると、

「INDIRECT（“‘aさん‘！B12“）」

↓

②「aさん」の部分を直接書くのではなく、残業時間シートの「氏名」列のセルを参照するように変更します。ここでは「A2」を参照します。

「INDIRECT（“‘A2‘！B 12“）」

※注意※

上記のように単純に「aさん」を「A2」に変えてしまうと、「A2」がセルではなく、文字列として認識してしまうので、「A2」という名前のシートを探してしまいエラーが出ます。

そこで、

他の文字列と切り離す形にする必要があります。

「INDIRECT（文字列 & A2 & 文字列）

数式にすると↓

「INDIRECT（“ ‘ ”&A2&“ ‘！B12）」となります。

各シートのA2のセルを参照するという意味ですので、「&」で繋ぐことで文字列であるとエクセルが認識しなくなります。

↓

③列を固定する。

「＄A 2」でA2の列を固定します。

↓

④完成した数式

「INDIRECT（“‘”&＄A2&“‘！B12“）」

結構複雑です。応用的な使い方をまた別の機会に追記します！ 

□まとめ

※「INDIRECT（インダイレクト）」関数：参照先のセルを表示する

　「INDIRECT（参照文字列）」

参照文字列で指定されたセルの内容を表示する。

※参照先の文字列の書き方

→同一ブック内にある別シートを参照する場合

「“シート名！セル」

→別のブックにあるシートを参照する場合

「“‘[ブック名]シート名‘！セル“」

メモ帳を使ってカテゴリー分けを行う方法方法。慣れればめちゃくちゃ簡単です！

日付や製品名などで、記号や製造日など色々なカテゴリーが一つの文字列の中に複数含まれてセルの中に入力されていることがあります。

例えば

・「2020 12 21」　→  2020年　12月　21日

・「AQOUS 4TーC50JA 50インチ」　→  アクオス　型番　サイズ

こんな感じです。

今日解説するのは、

この一つの文字列の中からカテゴリーを分けてそれぞれのセルに入力する方法です。

こんな感じ　↓

一つのセルには最小単位を入力することが基本ですので、上記のように一つのセルに3つも情報が入っていると後で利用するのに色々と不便です。

「FIND（ファインド）」関数を使って対象文字を見つけ出し、「MID（ミッド）」関数を使って文字を取り出す方法もありますが、

今回はメモ帳を使った方法を 紹介します。

□関数を使わずに文字列を分割する。

製品名をメモ帳にコピーして、半角スペースをタブ文字に置換してエクセルに戻すという作業を行います。

慣れると簡単にでき利用になるので、繰り返し練習してみましょう。

①「Ctrl」＋「Shift」＋「↓」で最下行まで選択する。

↓

②「Ctrl」＋「C」でコピー

↓

③「Ctrl」＋「V」でメモ帳に貼り付け

↓

④「Ctrl」＋「H」で「置換」する

・「検索する文字列」に半角スペースを入力。

・メモ帳の任意の場所で「Tab」キーを押して入力。

・「Ctrl」＋「X」で切り取り。

・「置換後の文字列」に「Ctrl」＋「V」で貼り付け。

ここで注意点ですが、

直接「置換後の文字列」に「Tab」キーを押せばいいのでは？と思うかもしれませんが、

「Tab」キーは次の項目に移動するという機能を持ったキーですので、直接押すとその下の「大文字と小文字を区別する」のチェック欄に移動してしまいます。

必ず、コピー&ペーストで行いましょう。

↓

⑤すべて置換をクリック

半角スペースの部分がすべてタブ文字に変換されます。

↓

⑥「Ctrl」＋「A」で全て選択する

↓

⑦「Ctrl」＋「X」で切り取る

↓

⑧「Ctrl」＋「V」で貼り付け

↓

⑨完成

□まとめ

複数の情報が一つになっている文字列は、

メモ帳を活用すると簡単にカテゴリー分けをすることができます。

きれいな資料の作り方はピッタリ揃えること。

会社で提出する提案書や企画書、分析レポートなどにおいて、

グラフや表、図形などを用いて視覚的に作成できると周りと差がついて一目置かれます。

この時にただ作成するだけでなく、貼り付ける位置などを少しずつ調整して見やすく表現できるとより良いのですが、一つずつ調整するのは意外と面倒で時間もかかります。

今回は、簡単に見栄えの良い資料が作れる方法を解説していきます。

□セルの角に合わせて貼り付けてみる

まずは、グラフを配置してみます。

ここで大切なのは、グラフを作る表は別シートに作成しておき、一旦そこにグラフを作成することです。

結構やりがちなのですが、提出資料にグラフを入れたいときに、提出用のシートと表やグラフ用のシートが同じシートで作成されてたりします。

自動で作成してくれるボタンがあるので、簡単にグラフ化できるのですが、作成されたグラフはマウスでどこでも動かせるようになっています。

つまり、

作成したがマウスでいい感じのところに移動（調整）しなければならず手間になります。

おまけに、ズレてたりします。

そこで解消する便利な機能が「Shift」＋「X」です。

これは、「切り取り」ができるショートカットキーです。

そして、「Ctrl」＋「V」で貼り付けます。

貼り付けたい（セル）を選択して、貼り付けることでセルの角に自動的に合わせてくれます。

①別シートの表をグラフ化する

↓

②「Ctrl」＋「X」：切り取り

↓

③作成用シートで「Ctrl」＋「V」：で貼り付け

↓

④完成

□サイズ調整をするときに便利な2つの方法

・「Alt」キーを押しながら拡大や縮小をすると、グラフの角をセルに合わせたままサイズ変更ができます。

・「Shift」キーを押しながら拡大や縮小すると、縦と横の比率を保ったままサイズ変更できます。

以上、2点は意外と知られていない方法です。

貼り付けたグラフを後から、資料に合わせて調整することはよくあります。

せっかく、綺麗に貼り付けてもその後に見栄えが悪くなると意味がありません。

簡単ですので、覚えておきましょう。

□必要な部分だけ表示する：トリミング機能を活用する

貼り付ける場所や、伝えたい情報に合わせていらない部分は切り取る必要があります。

その際に使うのが、「トリミング」機能です。

例えば、

画像を小さくするだけなら縮小機能でも良いのですが、無駄な部分を切り取ったり、画質を落とさずに使いたい時には「トリミング」が適しています。

基本的には画像データなどに行います。 

□複数図形の位置を揃える

図形やグラフが一つの場合はあまり気にならないかもしれませんが、複数のグラフや図形をがある場合は、揃える方が綺麗です。

そんな時は、「Shift」キーを使います。

具体的には

①「Shift」キーで複数選択する

↓

②書式タブの「配置」を選択

↓

完成

□上達の為のワンポイントアドバイス：勝手に色が変わるのを防ぐ

グラフを貼り付けた際に、勝手に色が変わってしまう場合があります。

常にコピー元と同じものが貼り付けれるように設定をして置いた方が便利です。

「詳細設定」から「グラフのデータ要素の参照先がすべての新規ブックに設定されているプロパティ」のチェックを外します。

これで、どこに貼り付けてもコピー元が変わることはありません。

□まとめ

※グラフや表は作成資料とは別シートに作る。

　加工がしやすいため

※「Alt」キーと「Shift」キーをうまく活用する。

　「Alt」キー：グラフの角をセルに合わせたままサイズ変更ができる。

　「Shift」キー：縦と横の比率を保ったままサイズ変更できる。

※トリミング機能でいらない部分はカット

　作成する資料に合わせて、貼り付ける図形やグラフの余分なところはカットする。

※複数の図形はまとめて整える

　「Shift」キーを使ってまとめて選択。

　書式タグの「配置」機能で整える。

IF関数の使い方（条件に合わせて表示する方法）

業務で使う頻度が高い関数に、「IF（イフ）」関数があります。

例えば、

・営業成績が500万以上の社員を抽出する

・テストの成績に合わせてクラス分けをする

など、

一定の条件を満たす場合に行う処理を決める作業に使います。

この設定した条件に対して行う処理のことを「条件分岐」といいます。

「IF（イフ）」関数は条件を複数設定することができるので、

数式が結構複雑になって、後から見返したときにわかりにくい場合があります。

なるべく「シンプル」に表現できるように解説していきます。

□条件分岐を使う

上記でも記載しましたが、

エクセルの関数の中でもかなり使用頻度が高い「IF（イフ）」関数です。

「IF（イフ）」関数は、条件を設定し、条件に該当する場合とそうでない場合に分けて、表示する値を変える関数です。（条件分岐）

英語「IF」は日本語訳で「もし、○○なら」という意味になるので、なんとなくわかる気もします。

「IF（イフ）」関数には、「条件式」「条件式を満たす場合の戻り値」「満たさない場合の戻り値」の3つの引数があります。

数式にするとこんな感んじです↓

それでは、具体的な使い方を記載します。

「年数が2020年に一致する場合のみ“○“を表示」してみます。

①数式を入力

↓

②フィルハンド機能でコピー

↓

③完成

※間違いなく処理ができているかチェックしてみましょう↓

①「Ctrl」＋「Shift」＋「L」でフィルター機能を設定

↓

②「2020年」をチェック

↓

③全て“○“がついているか確認

 ※条件式の表示方法例も一緒に記載します

・「＝」：等しい

・「＜＞」：等しくない

・「＞」；〜より大きい

・「＜」：〜より小さい

・「＞＝」：以上（含む）

・「＜＝」：以下（含む）

□条件式が2つある場合のやり方

上記の内容を踏まえて、少し応用的な使い方をしてみます。

2つ以上条件がある場合のやり方は二つあります。

一つは、「IF（イフ）」関数の中に「IF（イフ）」関数を入れる方法です。

これを、「入れ子構造」と言ったりもします。

もう一つは、「AND（アンド）」関数や「OR（オア）」関数を使った方法です。

「AND（アンド）」関数は、複数の条件式を全て満たしているかを判断します。

「OR（オア）」関数は、いずれかの条件式を満たしているかを判断します。

「入れ子構造」を使った方法は、複雑化しやすく確認しにくいので、「AND（アンド）」関数や「OR（オア）」関数を積極的に使いましょう。

ここでは、こちらを解説していきます。

　○「AND（アンド）」関数

「AND（アンド）」関数でよく使うのは、「○○以上△△以下」のような条件の時です。

例えば、「2020年の4月以上9月以下」で設定してみます。

数式だとこんな感じです↓

①数式を入力

↓

②フィルハンド機能でコピー

↓

③完成

　○「OR（オア）」関数

「OR（オア）」関数でよく使われるのは、「〇〇もしくは△△」のような条件の時です。

例えば「土曜日もしくは日曜日は“○“にする」で設定してみます。

数式だとこんな感じです↓

①数式を入力

↓

②フィルハンド機能でコピー

↓

③完成

□上達のためのワンポイント：満たさない条件を表現するときは「NOT（ノット）」関数を使う

わかりやすく説明するために、上記の「OR（オア）」関数の条件に満たさない場合という設定で説明します。

土曜日もしくは日曜日以外ということになるので、月曜日〜金曜日の平日ということになります。

この「以外」という事を表す表示に「NOT（ノット）」関数を使います。

数式に表してみます↓

「OR（オア）」関数を「NOT（ノット）」関数を使用して否定するという構図になります。

具体的に

①数式を入れる

↓

②フィルハンド機能でコピー

↓

③完成

□まとめ

※「IF（イフ）」関数：条件に合わせて結果を表示する

IF（条件式，一致した場合，一致してない場合）

※「AND（アンド）」関数：全ての条件を満たしている

AND（条件式1，条件式2・・・）

※「OR（オア）」関数：どれかの条件を満たしている

OR（条件式1，条件式2・・・）

※NOT関数：条件を満たしていない

NOT（条件式）

半角・全角・空白などのバラバラなデータを整える方法

氏名や電話番号、住所に製造番号などいろいろなデータを一つにまとめると、

半角や全角などデータの形式が揃っていないことがよくあります。

形式がバラバラなデータは見た目も悪いですし、集計して分析するときなどにも不便です。

統計的な分析に使用するようなデータでは、最初に必ずデータをきれいに整えます。

これを「数値のクレンジング化」と言ったりします。

それぐらい大切な作業ですが、一つ一つ手作業で直していくのは大変な作業です。

今回は三つの関数を使用して自動でデータを整える方法を解説していきます。

□空白を取り除く：「TRIM（トリム）」関数

「TRIM（トリム）」関数は、文字列の前後の空白（スペース）を取り除きます。

文字列の中にある空白に間にしては、一つになります。

（あくまでも、一つになるだけです。削除されるわけではありません）

半角で空白を開けていても、全角で開けていても関係なく効果を発揮してくれます。

この関数の引数は一つだけで、

空白を取り除きたい対象のセル（文字列）を指定するだけです。

こんな感んじ↓

①B2セルに数式を入れる。（＝TRIM（A2））

↓

②フィルハンドをダブルクリック

↓

③完成

□全角を半角に変換する：「ASC（アスキー）」関数

「ASC（アスキー）」関数を使うことで、文字列を半角に変換することができます。

引数は一つだけで、半角にしたい対象のセル（文字列）を指定するだけです。

①C2に数式を入れる。（＝ASK（B2））

↓

②フィルハンドをダブルクリック

↓

③完成

ワンポイントとして、

「半角」ではなく「全角」で統一したい時は「JIS（ジス）」関数を使います。

使い方は同じです。

□文字列の中の一部の文字を置換する：「SUBSTITUTE（サブスティテュート）」関数

文字の中に含まれている文字を別の文字に置き換えます。

もちろん、文字だけでなく記号でもいけます。

引数は三つあり、「対象とな文字」「置き換える前の文字」「置き換えた後の文字」です。

こんな感じです↓

画像（数式）

SUBSTITUTE（サブスティテュート）関数で置き換える文字は、半角と全角を区別します。

そのため、文字の形式を半角または全角に整えておかなければなりません。

上記のASC（アスキー）関数を使いましょう。

①D2セルに数式をい入れる。（＝SUBSTITUTE（C2, “〜”, “ー”））

↓

②フィルハンドをダブルクリック

↓

③完成

□まとめ

※TRIM関数

　「文字列」の前後の空白を削除。

　「文字列」の間のセルを一つにする

　数式：TRIM（文字列）

※ASC関数

　「文字列」を全て半角に揃える

　数式：ASC（文字列）

※JIS関数

　「文字列」を全て全角に揃える

　数式：JIS（文字列）

※SUBSTITUTE関数

　「文字列」の中の文字を、指定する文字に置き換える。

　数式：SUBSTITUTE（文字列, 指定文字, 置き換える文字）

いろいろな条件を指定してセルを数える方法

エクセルの実務ではよく使う機能として、「カウント」があります。

全体のセルの数を数えるのはもちろんのこと、条件を設置してそれに合うセルのみ数えたりなど、色々な場面で出てきます。

具体的には、

・管理部なら商品の数

・営業なら契約数や顧客の数

・人事なら社員数や組織の数

あげればキリがないくらい、結構使う場面が多いです。

条件に合うセルを数えるには、セルに入っている情報を理解しておかなければなりません。

そして、その後の分析手法や表示方法も理解した上で使いこなす必要があります。

覚えてしまうととても便利ですので、覚えて活用していきましょう。

今回は、「NO」「氏名」「階級」「住所」の4つの表をそれぞれでカウントしていきながら、具体的に解説していきます。

□すべてのセルを数える

入力されている人数をすべて数えるには、「COUNTA（カウントエー）」関数を使用します。

「COUNTA（カウントエー）」関数の引数は、セル範囲を1以上で設定します。

これは難しく考える必要はなく、数えたいセル範囲をすべて選択することで自然と1つ以上設定しているので大丈夫です。

ポイントとしては、

数えたいセルの列全体で選択するといいと思います。

「A1：A10」のように範囲で選択すると、行が増えてA10を超えてしまうとカウントされないからです。

こんな感じです↓

「−1」は表の見出しを退けるためです。

表には大抵見出しがありますので、覚えておきましょう。

そして、ここで覚えておきたいポイントがもう一つあります。

「COUNTA（カウントエー）」関数は、空白でないセルをカウントします。

セルの数と必ずしも同じではないので、数値が合わない時は確認してみましょう。

□階級ごとに人数を数える

指定した条件を満たす人数を数えるには、「COUNTIFS（カウントイフス）」関数を使います。

引数には、「セルの範囲」と「検索条件」の組み合わせでいくつも設定できます。

設定したすべての条件を満たすセル数を数えます。

こんな感んじです↓

□上達のためのワンポイント

エクセルには「S」がついていない、「COUNTIF（カウントイフ）」関数があります。

これは一つの条件しか設定できません。

「COUNTIFS（カウントイフス）」関数は複数の条件を設定できるので、

こちらを覚えておけば問題なしです。大は小を兼ねる的な感じです。

エクセルはこんな関数がたくさんあります。

例えば、上記で説明した例で言うと

「COUNTA（カウントエー）」関数も、空白以外のセルは全て数える関数ですが、

（セル内の情報は、数値でも文字でも関係ない）

数値や日付を含むセルだけを数えたい場合は、「COUNT（カウント）」関数を使えば可能です。

すべて覚える必要はないので、必要なものだけ覚えていきましょう。

□ちょっとした応用編：「住所」の中から都道府県ごとに集計する方法

指定の住所に該当する都道府県が何件含まれているか数えます。

「COUNTIFS（カウントイフス）」関数には、文字列の中の「〇〇〜□□」みたいな条件指定をすることもできます。

例えば、

「愛媛県〜」みたいに愛媛県から始まる文字列という指定の仕方です。

この方法だと「愛媛県産」でも「愛媛県出身」でも「愛媛が大好き」でも、とにかく「愛媛県」から始まる文字列を全て指定できます。

このような曖昧な指定方法には「＊（アスタリスク）」を使用します。

「＊（アスタリスク）」マークは、何かしらの文字があることを示す記号です。

上の例で言えば、「県」とか「出身」とか「大好き」など全てが対象で、この「曖昧」を示す記号を「ワイルドカード」と言います。

数式だとこんな感じです↓

□まとめ

※空白以外のセルを数える：COUNTA（カウントエー）関数を使う

　COUNTA （セル範囲）

※指定条件を満たすセルを数える：COUNTIFS（カウントイフス）関数を使う

　COUNTIFS （セル範囲, 条件式）

※曖昧な条件指定をするときは、「ワイルドカード」を使う

　「＊」←こんな記号

　数式例：

※大は小を兼ねるような関数は大の方を覚える。

　例：COUNTIFS（カウントイフス）関数を覚えれば、COUNTIF（カウントイフ）関数は

　　　覚えなくて良い。

条件付き書式設定を使って、完了したタスクを一目でわかるようにする方法

製造現場などではよくあることですが、

エクセルで工程の進捗管理をすることがよくあります。

そんな時に、タスクの状況が一目でわかるようにすることは、効率的な観点からもとても大切なことです。

今回は進捗管理表（タスク管理表）から、タスクが完了すると色が変わるように設定するという作業から具体的に説明していきます。

□完了したタスクを赤色にする

まず最初に行う作業は「条件付き書式設定」です。

これは、指定したセルの値や数式の計算結果に対して何らかの条件を設定し、条件に当てはまるとセルに定めた書式を反映させるという機能です。

例えば、セルの文字色を変えたり、背景の色を変えるなどです。

ここで指定しなければならないのは、「範囲（どこからどこまで）」「条件（ルール、数式）」「書式（色、書体）」の3つです。

今回の進捗管理表では、

・作業が終了したら「完了」

・作業中の場合は「進行中」」

・作業を開始してない場合は「未着手」

と設定します。この中からタスクを「完了」にしたら、自動的に行全体を赤色にします。

①列を選択する。→「ホーム」タブ→「条件付き書式設定」→「ルールの管理」⇦  範囲

今回は列で指定しています。範囲で選択することもできますが、行を追加してしまうと適用先を変更しないと反映されなくなり意外と二度手間になります。

↓

②「条件付き書式ルールの管理」→「新規のルール」をクリック

↓

③「数式を使用して、、、。」をクリック

④「次の数式を満たす、、、。」の欄に数式を入力

　数式：= $E1 = "完了"      ⇦ ルール

セルの範囲は必ず一番端（最初）のセルを基準にします。

今回だとE列の文字が「完了」になれば、赤にするという条件なので、E列が対象になり、E1が最初のセルになるのでE1を数式に入れます。

↓

⑤書式をクリック

↓

⑥「セルの書式設定」ダイアログの「塗りつぶし」タブから“赤色“をクリック　⇦書式

↓

⑦完成画面

上記の使い方はほんの一例です。

色をつける以外にも文字の書式を変えたり、横線を引いたり使い方は様々です。

数式も「＝」以外にも「<」「>」などの比較演算を活用すれば、「〇〇値が△△以上」みたいな条件設定もできます。

多種多様な使い方ができるので、また別の記事でも取り上げたいと思います。

□条件を複数設定する場合は？

一つ条件付き書式設定を行い、続けてルールを設定したい場合は「適用」をクリックしてください。

最初に作ったルールが保存され、新しくルールを作成することができます。

ここで注意が必要ですが、

「適用」をクリックせずに「新規ルール」をクリックしてしまう場合があります。

これだと、最初に作ったルールが「上書き」されてしまいます。

意外とやりがちなミスですので注意しましょう。

□上達のためのワンポイント：「条件付きの書式設定」のルールは範囲を決めてから行う！

条件付き書式設定のルールを決定した後に、そのルールを適用する範囲を設定することもできます。

①「条件付き書式ルールの管理」のダイアログをクリック

しかし、これだと大きなデメリットがあります。

それは、数式が壊れてしまう不具合が起こる場合があるからです。

こんな時は、再度数式を書き直せば直りますが、リスクは少ないにこした事はありません。

必ず、範囲を設定してから数式などルールを設定しましょう。

□まとめ

※タスクの変化に合わせてセルを変化させるには「条件付き書式設定」を使う。

　タスクが「完了」　→  その行が赤色になる

※条件付き書式設定には順番がある

　適用範囲を選択してルール（数式）を設定する

日付から期間を算出する方法

2つの日付の差を求める関数を利用します。これをマスターすることで、

例えば、

・支払請求日から期限を算出したり

・建築物の着工までの期間を算出したり

など、色々なところで利用できるようになります。

意外と、色々なところで出てきます。

更に、応用できるようになると「算出した期間に応じてランク付け（記号）を付ける」という合わせ技もできるようになったりします。

今回は、

日付けから継続期間を算出してランク付けをする作業で具体的に解説していきます。

□2つの期間の日付の差を求める。

2つの日付の差を求めるには、「DATEDIF（デイトディフ）」関数を使います。

日付の差は、「Y（年数）」「M（月数）」「D（日数）」のいずれの単位でも表示可能です。

「開始日」「終了日」「単位」の3つの引数を使って表します。

注意点として、

2つの日付は、1つ目の日付より後の日付になるようにします。

当たり前ですが、順番を間違えるとエラーで表示されます。

※因みに、

「DATEDIF（デイトディフ」関数は、入力候補に表示されません。

「Tab」による自動入力が使えないので、関数の文字はすべて入力しましょう。

□数値に応じてランク分けしてみよう

継続期間に合わせて、ランクをつけてみます。

条件に合わせて表示を変えるには、「IFS（イフズ）」関数を使います。

これは、条件式と値のセット設定してその条件に合致した時に表示を変える関数です。

条件式は左から順に精査していきます。

上の画像では、↑

1つ目の条件式「継続月が100以上」の場合は「A」、

2つ目の条件式「継続月が50以上」の場合は「B」、

3つ目の条件式「継続月が30未満」の場合は「C」と表示します。

注意点として、

条件を満たさない場合は設定できません。

例えば、

1つ目の条件式に「30以上50未満」など2つの値を設定することはできません。

1つの条件式には必ず1つの値しか設定できないので注意しましょう。

□上達の為のワンポイント：今日の日付は「TODAY（）」関数を使う！

今日の日付を入力する際に、ほとんど人は手入力にて打ち込むと思います。

この「TODAY（）」関数を使うと、自動的に今日の日付が入力されます。

この関数には、引数がないのでエクセルを開いた日付に毎回自動的に入力されます。

固定したい時は注意が必要です。

必ず作成した日付を入力しなければならない資料作成にはとても便利な関数です。

 

□まとめ

※2つの日付の差を調べるには：「DATEDIF（デイトディフ）」関数を使う。

　DATEDIF（開始日, 終了日, 単位）

　単位を「Y」にすると年数、「M」にすると月数、「D」にすると日数を表示します。

※条件を満たすか判定するには：「IFS（イフス）」関数を使う。

　IFS（条件式1, 条件式1が満たされた場合の値, 条件式2, 条件式2が満たされた場合の値, ...）

　左から順に条件式を判定していき、満たされた場合は値を表示する

一瞬で苗字や名前を結合し、ふりがなを自動で入力するする方法

顧客名簿や従業員名簿など、「名簿」に触れることは多いと思います。

性と名に分けたり、振り仮名をつけたり、後の分析がしやすいように集計したいところですが、

一人ひとり作業していくのはとても大変です。

今回は、これらの作業が一瞬で終わらせる方法を解説していきます。

とても便利ですので、是非覚えましょう。

□文字列の結合方法は「&（アンド）」を使います。

文字列を結合するとき最も簡単なのは「&（アンド）」でつなげる方法です。

例えば、3つの文字列を結合してみます。

この時に見やすくするために、（文字　空白　文字）に表記されるようにルールを決めます。

①セルに数式を入力します

　↓

②フィルハンドをダブルクリック

これで苗字と名前がくっつきました。

数式の具体的な説明はこんな感んじです。↓

「苗字」＋「空白」＋「名前」で並べて、「＋」のところを「&（アンド）」に置き換えていると言えそうです。

 因みに、「＆」のボタンがわかりずらいので画像載せておきます。↓

□振り仮名は「PHONETIC関数」（フォネティック）を使います。

「PHONETIC関数」は、振り仮名を自動で作成してくれる関数です。

とっても便利な関数ですが、デメリットもあります。

この関数は、セルに文字を入力して漢字にした時の情報に基づいて、振り仮名を表示していきます。

その為、自分で入力していないとコンピューターが振り仮名を取り出すことができません。

つまり、他のデータからコピーして貼り付けたデータなどは振り仮名を表示できないと言うことです。

（厳密には、マクロやVBAを使えばできますが、内容が濃いので別記事で書きます）

また、

漢字を正しくない「読み」で入力するとそのまま表示されるので、間違って表示されます。

例えば、

「幸太」を「コウタ」と入力せず、「シアワセ　フトイ」と入力して余分な所を削除する形で変換すると「シアワセ　フトイ」と表示されます。

デメリットはありますが、それを差し引いても便利な機能です。

要は使い方の問題です。

必ず二重チェックは必要ですが、、、。

 

□上達の為のワンポイント：データは最小単位で管理しよう！

名簿を作成するときに、「性と名を分けて管理するか」それとも「性と名を結合して一つの値として管理するか」一度は迷ったことがあると思います。

一から作成する場合は、必ず分けて作成しましょう。

なぜなら、データは一回作ると終わりではなく、必ず後で集計や分析をしやすいように加工します。

その時に、一つのセルに二つ以上の情報があると加工がしにくくなったり、再度作り直したりとにかく二度手間です。

人間は同じ作業を何度もするのはストレスでしかありません。

例えば、

今回の「性と名前をつなげたり」、「振り仮名をつけたり」と言う作業もセルが分かれているから一発でできる数式が機能するのです。

一つのセルに入っているとできません。

基本的な考え方として「エクセルは一つのセルに一つの情報（最小単位）」と言う考え方を持っておきましょう。

□まとめ

※文字を結合する時は「&（アンド）」を使う

　＝「セル番号　&“ “&   セル番号」

　→セルの文字列を結合する。

※漢字の振り仮名を取り出す時は「PHONETIC関数」を使う

　PHONETIC（文字列）

　→文字列の振り仮名を取り出す。

※データは最小単位で管理する

　→一つのセルに一つの情報（文字や数字など、、、。）

文字列データから希望部分を取り出す方法

文字列の一部を取り出す関数と言われると、ピンと来ない人もいるかもしれません。

しかし製造現場などではよく使われたりします。

例えば、

製造番号や管理番号から一部取り出す。などといった使い方です。

これらは意味を持った英数字を繋げて管理する場合が多い為、どうしても長くなりがちです。

具体的に説明すると、

「商品記号＋製造年月日＋機械番号」で商品を管理しているとします。すると、

「HL20200405G」こんな感じになります。分解すると、

「HL」：商品記号

「20200405」：2020年4月5日

「G」：機械の番号

（※あくまでも例です。）

これらを集計して分析したいときに、このままだと集計しにくくなります。

その為、セル内の文字を分解する必要があります。

「HL」と「20200405」と「G」の3つのカテゴリーに分けることで、

初めて「どんな商品」が「いつ」「どの器械」で作られてのかなどが集計、分析ができると言うわけです。

それでは、具体的に文字列を分解して希望部分を取り出す方法を解説します。

取り出したい文字の場所によって3つの方法を使い分けます。

□左から文字を取り出したい時は「LEFT関数」を使用する。

 「LEFT関数」は左から文字を取り出したい時に使う関数です。

例えば、

「HL20200405G」の「「HL」という記号を取り出してみます。

これは言い換えると「A2セルの文字を左から（LEFT）2文字取り出す」と言えそうです。

これを関数で表すとこうなります。↓

□真ん中から文字を取り出したい時は「MID関数」を使用する。

「MID関数」は真ん中の文字を取り出すときに使う関数です。

例えば、

「HL20200405G」の「20200405」という日付の部分をとり出してみます。

これは、言い換えると「A2セルの文字列を、3文字目から8文字を取り出す」と言えそうです。

これを関数で表すとこうなります。

注意点としては、

引数の値を間違って設定してしまうことです。

「REFT関数」や「RIGHT関数」取り出す文字のスタート地点が、既に関数に組み込まれているのですが、「MID関数」は文字列の真ん中ですので、取り出す文字のスタート地点を教えてあげる必要があります。

その分打ち込む量が多いので、希望通りの文字列が取り出せているか必ず確認しましょう。

□右から文字を取り出したい時は「RIGHT関数」を使用する。

「RIGHT関数」は右から文字列を取り出す関数です。

例えば、

「HL20200405G」の「「G」という記号を取り出してみます。

これは言い換えると「A2セルの文字を右から（RIGHT）1文字取り出す」と言えそうです。

これを関数で表すとこうなります。

「REFT関数」と考え方はおなじです。

□上達の為のワンポイント：フラッシュフィル機能を使おう！

フラッシュフィルを使って文字列を取り出す。

フラッシュフィルとは、入力したルールを自動的に判別して他のデータ反映する方法です。

覚えてしまうとものすごく便利です。

具体的な方法としては、

①セルに関数を入力します。

　↓

②次のセルを選択して、ショートカットキー「Ctrl」＋「E」押す。

　↓

③以降のデータが自動的に作成される。

めちゃくちゃ簡単に取り出せます！！

□まとめ

※「LEFT関数」：左から「○文字」を取り出す

　　＝LEFT（文字列，文字数）

※「MID関数」：真ん中から「○文字」を取り出す

　　＝MID（文字列，開始位置，文字数）

※「RIGHT関数」：右から「○文字」を取り出す

　　＝RIGHT（文字列，文字数）

関数の行や列を固定する具体的な方法。

関数や数式を入力した場合、大抵の場合はそれをコピーして複製します。

しかし、これには大きなデメリットがあります。

それは、コピーして複製すると関数や数式が対象とするセルが勝手にずれてしまう事です。

これだと正しい結果を得ることができません。

その為、ずれないように回避するためには、「相対参照」と「絶対参照」の概念を理解して、使いこなせるようになることが大切です。

とても重要な概念のですので、しっかり理解する必要があります。

最もわかりやすく、代表的な学習方法に「九九表」の作成がありますので、

これを基に解説していきます。

□「相対参照」と「絶対参照」について

 関数や数式が参照するセルを固定することを絶対参照といい、

参照するセルを固定しないことを相対参照といいます。

エクセルでは、指定しない限り関数や数式は相対参照になっています。

つまり、

一つのセルで数式を作り、コピーして使うと最初に設定している数式がずれてしまいます。

九九の表を使って確認してみます。

①B2のセルに数式を入れます「＝A2＊B1」

　↓

②J10のセルまでコピー

結果、九九の表ができませんでした。

なぜこうなるのか？

他のセルをクリックして数式がどうなっているか、確認してみます。

セルがズレているのがわかります。

例えば、

E5のセルを見てみると「＝D5＊E4」となっています。本来であれば、A5とE4を掛けるのが正解です。

こうなっていないのは、上記にも記載していますが「A列を固定したり」や「1行目を固定」していないからです。

これが相対参照になっているから起きる現象です。

もちろんこの設定の方がいい場合もあります。

例えば、

各支店の各月の合計を求める際に、合計値を入れている場合などです。

4月の合計を求める数式を一つ作って、横にコピーすると各月の合計を求められます。

これを絶対参照にしておくと、掛けるセルが固定されてしまうので、各月の合計はすべて4月の合計になってしまいます。

理屈はとても簡単です。

後は、固定する方法について解説します。

□参照する列や行を固定する方法

「A列を固定する」や「1行目を固定する」など、参照先のセルを固定する方法はとても簡単です。

それは、固定したい行または列の前に「$」（ドル記号）をつけるだけです。

具体的に説明します。例えば、

「$A2」とすれば、A列を固定すると言う意味になります。

これを使った数式をコピーすると、常にA列が固定されます。

「B$1」とすれば、1行目を固定すると言う意味になります。

これを使った数式をコピーすると、常に1行目が固定されます。

こんなイメージです↓

これを使って、先程の九九表を作成してみます。

数式「$A2＊B$1」を入力して、コピーにて作成 

しっかりと九九表になりました。

□上達のワンポイント

数式を入力した後に、間に「$」（ドル）を入れていくのは面倒です。

そんな時は、「F4」キーを押すと「A2」→「$A$2」のように一発で参照方法を切り替えることができます。

更に、続けて「F4」キー押すことで「A$2」→「$A2」→「A2」と言うように、固定する場所を変更することができます。

覚えておきましょう。

□まとめ

※関数には、「絶対参照」と「相対参照」がある。（初期設定は相対参照）

　→絶対参照：関数が参照するセルが固定されること。

　→相対参照：関数が参照するセルが固定されていないこと。

※セルを固定する絶対参照を使用する場合は、行や列の記号の前に「$」を記入する。

※「F4」キーを使うと、切り替えが容易にできる。

絶対に周りと差がつく関数の基本的な考え方と４つの入力方法

関数とは、複雑な計算処理を一瞬で終わらせてしまうエクセルの便利機能です。

エクセルにはあらかじめ関数が内蔵されたおり、簡単に利用することができますが、

その数はなんと全484個もあります。

一見するとすごい数ですが、基本的な考え方は実はすべて共通しています。

今回はこの基本的な考え方について少し解説していきます。

□関数の基本

関数を使ううえで知っておくべきことが２つあります。それは、「引数」（ひきすう）と「戻り値」（もどりち）です。

例えば、

合計を計算する「SUM」（サム）という関数は、合計したいセル範囲を投入し、SUM関数の中で計算した結果、合計値を出します。

この投入するセル範囲のことを「引数」（ひきすう）と呼び、最終的に出てくる合計値を「戻り値」（もどりち）といいます。

ルール1：引数がなくても、関数には必ずカッコがつきます。

このカッコは、引数を投入するための入り口のようなものです。

ルール2：因数が複数ある場合は、半角カンマ「,」で引数を区切ります。

因みに、「：」で範囲を選択すこともできます。

□入力方法①：セルに直接入力する

セルに直接計算式を入力して、結果を求める方法です。

①セルを選択

　↓

②関数を手入力（ここではSUM関数を使用）

 

□入力方法②：オートコンプリート機能を使って入力する

この機能はExcel2007バージョンから新機能として追加されました。

セルに「＝」と入力して、続けて文字を入力すると、その文字から名前が始まる関数の候補が一覧で表示されます。

①セルを選択

　↓

②関数を入力（ここではSUM関数を使用）

□入力方法③：ダイアログボックスを使って入力する

関数名がわからない場合や、関数の機能、引数についての説明を確認しながら

入力したい場合は、［関数の挿入］ダイアログボックスを使います。

 ①セルを選択

　↓

②ダイアログボックスを選択

・使いたい関数がある場合

　→「関数名」から関数を選択します
・「関数名」に使用したい関数がない場合

　→「関数の検索」に関数名を入力して隣にある「検索開始」ボタンを押して

　　関数を検索することも可能です。

③関数の因数ダイアログが表示されます

範囲を設定してOKをクリック

□入力方法④：関数ライブラリを使って入力する

「数式」タブ内の関数ライブラリ「数学/三角」を押下すると、

それに関連する関数名が一覧で出てきますので、その中から選択します。

①セルを選択

　↓

②「数式」タブの「数学/三角」を選択

※好きな関数を選択します。

　↓

...この後の方法は入力方法③と同じ。なので割愛します。

□まとめ

今回は、関数の基礎的な考え方と４つの入力方法について解説しました。

どの方法でも結果は同じなので、使いやすい方法を使うといいと思います。

個人的には、関数ライブラリをよく使っています。

理由としては、

①今回はSUM関数でしたが、ほかの便利な関数もカテゴリーに分類されている為、わかりやすい。

②オートSUMなど、よく使う関数は一発で結果を出してくれるボタンがあるから。

関数はエクセルの必須条件ですので、いろいろ試して使いこなしていきましょう。