仕事で使えるEXCELのスキルをわかりやすく紹介するブログです。 「こんなことできたんだ！もっと早く知っていたら…」という気づきに つながれば幸いです。
IF関数とIFS関数の違い：初心者向けガイド Excelで条件に基づいたデータ処理を行う際に便利な関数として、IF関数とIFS関数があります。これらの関数の違いと、それぞれの使い方について詳しく説明します。 1. IF関数の使い方 IF関数は、指定した条件が真（TRUE）か偽（FALSE）かを判定し、それに応じて異なる値を返す関数です。基本的な構文は以下の通りです。 =IF(条件, 条件が真の場合の値, 条件が偽の場合の値) 基本的な例 以下の例では、セルA1の値が10以上であれば「合格」、それ以外なら「不合格」を表示します。 =IF(A1 >= 10, "合格", "不合格") 2. IFS…
エクセルのSUMIF関数で、1行ごとのとびとびの合計を出す方法：初心者向けガイド
エクセルで1行ごとのとびとびの合計を出す方法：初心者向けガイド エクセルで1行ごとのとびとびの値の合計を出す方法を解説します。特定の範囲内の一部のセルに対して合計を求めるケースで便利です。 1. 具体的な例 各月で予算、実績と並んだデータにおいて、それぞれの合計を計算します。 2. 基本的な方法 「偶数/奇数列の作成」 計算する列とは別に、偶数と奇数を判別する列を作成します。以下の数式を各行に入力することで、予算の行には1、実績の行には0の数値が付与されます。 =MOD(ROW(),2)) 奇数行(予算)の合計 D列が1の時、同じ行のC列の値を合計することが出来る以下の数式を、予算の合計値を出…
エクセルのIF関数の使い方：初心者向けガイド エクセルのIF関数は、条件に基づいて処理を行う際に便利な関数です。この記事では、IF関数の基本的な使い方と応用例について解説します。 1. 基本的な構文 IF関数の構文は次のとおりです： =IF(論理式, 真の場合の値, 偽の場合の値) 論理式：評価する論理式（例えば、セルの値が特定の条件に合致するかどうか）。 真の場合の値：論理式が真の場合に返す値（結果値）。 偽の場合の値：論理式が偽の場合に返す値（結果値）。 2. 基本的な例 例えば、セルA1に数値を入力し、その数値が50以上なら「合格」と表示し、そうでない場合は「不合格」と表示する場合： =…
エクセルの「COUNTIF」関数を用いた重複データのチェック / 削除方法：初心者向けガイド
エクセルの「COUNTIF」関数を用いた重複データのチェック / 削除方法：初心者向けガイド エクセルの「COUNTIF」関数を活用すると、重複データを簡単に特定し、それを削除することができます。このガイドでは、具体的な手順と方法を解説します。 1. 重複の特定 データの準備 例えば、セルA1からA10に以下のようなデータが含まれるとします。 「COUNTIF」関数の使用 列の先頭に重複数のカウントを表示するために、列B1に「=COUNTIF($A$1:$A$10, A1)」と入力します。 データをコピーし、フィルタ機能を使う 列Bのカウント数値をコピーし、データの重複が2以上のセルを検索しま…
エクセルでの四則演算の使い方：初心者向けガイド エクセルは数式や関数を使って効率的に計算を行うツールです。この記事では、エクセルでの基本的な四則演算（足し算、引き算、掛け算、割り算）に加えて、平方（二乗）操作の使い方も解説します。 足し算（＋） 例：セルA1に10、セルA2に5と入力し、セルA3に「=A1 + A2」と入力します。 結果として、セルA3に「15」と表示されます。 引き算（－） 例：セルB1に20、セルB2に7と入力し、セルB3に「=B1 - B2」と入力します。 結果として、セルB3に「13」と表示されます。 掛け算（＊） 例：セルC1に4、セルC2に3と入力し、セルC3に「=…
TEXT関数で曜日を表示する方法：初心者向けガイド TEXT関数は、Excelで日付や数値を指定した形式で文字列に変換するための関数です。特に、日付を曜日として表示する場合に非常に便利です。以下に、TEXT関数を使って曜日を表示する方法を中心に、基本的な使い方とポイントをわかりやすく説明します。 1. 基本的な構文 =TEXT(値, 表示形式) 値：日付や数値などの変換したいデータ。セル参照や特定の値を指定します。 表示形式：変換後の形式を指定する文字列。 2. 日付を曜日として表示する基本例 以下のデータを例にします。(エクセルにコピペできます) セル データ A1 2024/06/16 例…
VLOOKUPの使い方：初心者向けガイド VLOOKUP（ブイ・ルックアップ）は、Excelで広く使われる関数の一つで、データベースの中から指定した値を検索し、その値に関連するデータを取得するのに役立ちます。以下に、VLOOKUPの使い方をわかりやすく説明します。 1. 基本的な構文 VLOOKUP関数の基本的な構文は次のとおりです： =VLOOKUP(検索値, 範囲, 列番号, 検索の型) 検索値：検索する値。セル参照や特定の値を指定します。 範囲：検索するデータの範囲。範囲の左端の列に検索値が含まれている必要があります。 列番号：範囲内の検索結果を取得する列の番号（最初の列を1として数えま…
スキル/テクニック 01. データが何番目にあるか調べる (MATCH関数)02. IFを使わない簡単な条件分岐 (CHOOSE関数)03. 数字を切りよく整理する (切り上げる/切り下げる)04. 「トップ5 / ワースト5」を調べる (LARGE/SMALL関数)05. 行や列を削除したり移動しても、変わらない連番/通し番号をつける 06. 一部/特定の言葉を条件にして、合計値を出す07. 複数のセル/文字を、一つのセルに簡単に繋げたい08. 特定の文字「～～」を含むセルの数を数えたい09. 重複を除いたデータ一覧を出したい / 1回だけ出現するデータを見つけたい10. 数値の順位付け、ラ…
どんな時に使える？ 指定したデータが、リストの何番目にあるかを検索したい、数字で表したい。 指定したデータの位置(～番目)を表す MATCH関数の使い方 ①値を表示したいセルに以下のようにMATCH関数を入力する (例：F2に「 =MATCH(E2,$B$2:$B$11,0) 」を入力) ポイント ・「MATCH関数」は「指定したデータ」が「指定した範囲の何番目にあるか」を示す 例：=MATCH(E2,$B$2:$B$11,0)) 青字＝指定したデータ。この場合は「リンゴ」と自由入力された隣のE2 赤字＝指定した範囲。この場合は商品名が並んだB2～B11 緑字＝照合の型。0と入力することで完全…
どんな時に使える？ アンケートなど、1, 2, 3, 4,,,などの番号で分かれている結果に対して、 表示する値を切り替える IFを使わない条件分岐 CHOOSE関数の使い方 ①値を表示したいセルにCHOOSE関数を入力する (例：C3に「=CHOOSE(B3,"満足","やや満足","やや不満","不満")」を入力) ポイント ・「CHOOSE関数」は1からの数字に該当する値を出力する 例：=CHOOSE(B3,"満足","やや満足","やや不満","不満") 青字＝番号が入力されたセル 赤字＝1からの数値に対応する結果。 この場合「1から4まで」4つの数字に該当する結果が出力される 1：満…
どんな時に使える？ 入力された数値を切りがよい数値になるように「切り上げ/切り下げ」する (10, 50, 100, 1000単位など) 切り下げの場合 ：FLOOR.MATH関数 切り上げの場合 ：CEILING.MATH関数 近い方向にまとめる場合：MROUND関数 例：「50」の単位で切り下げ (FLOOR.MATH関数の場合) ①C2セルに「=FLOOR.MATH(B2,50)」を入力 （詳細は以下ポイント参照） ②必要なセルまでコピー＆ペースト ポイント ・「FLOOR.MATH関数」「CEILING.MATH関数」「MROUND関数」を活用する ことで、「ある数値の単位」で切り下げ…
どんな時に使える？ 売り上げや点数などから、トップとワーストの数値を調べる トップ5の数値の出し方 ①順位付けのため、1～5まで入力しておく(例：E3～E7) ②F3のセルに「=LARGE($B$2:$B$11,E3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2～5のセルまでコピー＆ペーストする (例：F4～F7) ワースト5の数値の出し方 ①順位つけをするために、1～5まで入力しておく(例：H7～H7) ②F3のセルに「=SMALL($B$2:$B$11,H3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2…
行や列を削除したり移動しても、変わらない連番/通し番号をつける
どんな時に使える？ 削除や移動など、編集を行っても、変わらない連番や通し番号がつけられる よくある困りごと せっかく番号を付けたのに、編集によって番号が変わってしまい毎回やり直している 変わらない連番/通し番号の付け方 ①連番の先頭のセルの行番号に注目。(例の場合は2) ②行番号が2の場合は「=row()-1」を入力 (考え方は以下ポイントを参照) ③番号を付けたい下部までコピー＆ペーストする ポイント ・ROW関数を活用する。 ROW関数は「=row()が入力された行の番号」を出力する。 2行目に入力すると2、10行目に入力すると10となる。 ・つまり、2行目から1としたい場合、=row()…
どんな時に使える？ ・一部の言葉を条件に合計値を出したい 例題：詳細な住所と売り上げが入ったリストがあるが、大阪、東京、埼玉の地域ごと の売上合計値を算出したい 困りごと：リストには細かい住所も入っているため、SUM関数やSUMIF関数では、 条件としてヒットさせることが出来ない 例題の手順 ①F2のセルに「=SUMIF(A:A,E2&"*",C:C)」を入力 ②F3からF4までコピー＆ペースト ポイント ・「ワイルドカード」の機能を使う。 条件の箇所に「E2 & "*"」とすることで、条件は「大阪*」となる。 エクセルではワイルドカード「*」をつけることで「任意の文字を許す」となり、 「大阪…
どんな時に使える？ ・複数に分かれたセルの文字/データを一つのセルに繋げて(結合して)表示したい 例題の場合の手順 ①B2のセルに「=CONCAT(B2:D2)」を入力 (=CONCAT()を入力してB2からD2まで選択) ②B2からD2まで、3つのセルに分かれたセルが一つにまとめて表示される 別のやり方：結合するデータの間に「文字」を入れて繋げる場合 ①B2のセルに「=TEXTJOIN(", ",TRUE,B14:D14)」を入力 ②B2からD2まで、3つのセルに分かれたセルが一つに結合される 結合する文字の間に「, (カンマと半角スペース)」が入る ポイント =TEXTJOIN(", ",…
どんな時に使える？ ・「Countif」関数を活用して、「特定の文字」が含まれるセル/データの 数を数えたい 例題の場合の手順 ①D2のセルに「=COUNTIF(A2:A11,"*中央区*")」を入力、条件を「*」で囲む ②「中央区」の文字が含まれるセルの数が出力される ポイント ・「Countif」関数の条件に「*」を活用する。 「*」は任意の0から複数の文字列を含むことを許可する。 今回は「～～中央区～～」の形になっているので「*」で「中央区」を挟む。 条件が、 "中央区" の場合：セルが「中央区」のみのセルが算出される。例題では0となる。 "中央区*" の場合：「中央区～」のセルがカウン…
重複を除いたデータ一覧を出したい / 1回だけ出現するデータを見つけたい
どんな時に使える？ ・重複したデータが含まれる表やリストから、重複を除いたデータの一覧を 整理したい 例題の場合の手順 ①C2のセルに「=UNIQUE(A2:A21)」を入力 ②重複を除いた形で、入力されたデータが自動的に整理される (1回しか現れないものも含む) ポイント ・もし、「=UNIQUE(A2:A21,,TRUE)」のように、3つ目の引数をTRUEに 指定すると、「1回だけ現れるデータのみ」抽出するようにも出来る。
どんな時に使える？ ・表中のデータの数値から、順位/ランキングをつけたい。 例題の場合の手順 ①C2のセルに「=RANK.EQ(B2,$B$2:$B$11)」を入力 (範囲のB2～B11に対して固定を意味する$マークを付けることを忘れない。 一つずつ付けなくても、F4キーで簡単に付与することが出来る) ②コピーしてC3～C11に貼り付け ポイント ・「RANK.EQ」関数を使うと数値の順位付けが可能。 もし同じ数値だった場合には「同列」となる。 (例：4位が同列だった場合＝1, 2, 3, 4, 4, 6)
どんな時に使える？ ・表中のデータの個数を数えたいが、数値以外のデータや空欄がある。 この中から、数値が入ったデータのみ数えたい。 例題の場合の手順 ①B12のセルに「=COUNT(B2:B11)」を入力 ②C12のセルにコピー＆ペースト ポイント ・「COUNT」関数は「数値が入力されているセルの数」を計算する。 空白や他の文字はカウントしないため、数値が入ったセルの数だけ表示される。 ・これ以外にも「COUNTA」関数を使うと、「空白以外のセルの数」を計算できる。
どんな時に使える？ ・表中の数値の平均値を出したいが、データが無い「0」などが含まれている 場合でも、正しい平均値を出したい。 手順 ①B13のセルに「=SUM(B2:B11)/COUNTIF(B2:B11,"<>0")」と入力 (上記2つの「B2:B11」を「平均値を算出したい数値セルのエリア」に変更) ポイント ・平均値は「合計÷個数」で求める。 ・そこで「SUM」関数で「全体の合計」を算出。 次に「COUNTIF」関数で「B2からB11の中で、0以外のデータの個数」を算出。 *ここで「0以外の」という条件には比較演算子を使った「"<>0"」を使っている。 <>は、「0ではない＝0を除く」…
どんな時に使える？ ・通常、エクセルで「3/5」と分数を入力すると、勝手に日付に代わってしまう。 これを3/5と分数でそのまま表示させたい。 手順 ①表示させたい分数の前に「 0と半角スペース」を入力するだけ。 ②3/5がそのまま表示される。(データ上も0.6=3/5で入力される) ポイント ・この方法で入力すると自動的に約分される。 (5/10 = 1/2となる)
日付のデータを「年」「月」「日」に分けたい / 年月日の一部のみを取り出したい
どんな時に使える？ ・年月日がまとまった日付のデータを、「年」「月」「日」それぞれ分けたい ・日付のデータから、年月日の一部のみを取り出したい 手順 ①「年」を入力するセルには「=YEAR(A2)」(A2の箇所を日付のセルに変更) ②「月」を入力するセルには「=MONTH(A2)」(A2の箇所を日付のセルに変更) ③「日」を入力するセルには「=DAY(A2)」(A2の箇所を日付のセルに変更) ④下段までコピー＆ペースト ポイント ・YEAR関数、MONTH関数、DAY関数を使うことで、日付のデータから それぞれ年月日の情報のみを抜き出すことができる。
どんな時に使える？ ・エクセルの住所データから都道府県だけまとめて抜き取りたい 手順 ①B2のセルに「=IF(MID(A2,4,1)="県", LEFT(A2,4),LEFT(A2,3))」と入力 (もしくは上記の全ての「A2」を「住所が入っているセル」に変更) ②下段までコピー＆ペースト ポイント ・都道府県のうち、「神奈川県、和歌山県、鹿児島県」のみが4文字で全て 「県」となる。その他の都道府県は全て3文字となる。 ・そこで「IF関数」「MID関数」「LEFT関数」を使い、 - 4文字目に「県」があれば「左から4文字を抜き出す」 - そうでなければ「左から3文字を抜き出す」 ・この処理を行…
【Hlookup関数】Vlookupでは抽出できない、縦に整理された表からデータを抽出する
どんな時に使える？ ・縦に整理された表から、該当するデータを抽出したい ・Vlookupでは列方向の検索しか出来ないが、縦(列)方向から抽出したい 手順 ①B7のセルに「=HLOOKUP(A7,B2:E3,2,FALSE)」と入力 ②製品No「A100」に該当する製品名「ジュース」が出力される ポイント ・Hlookup関数は検索する値に一致した、「縦(列)のデータ抽出」に活用できる ・「=HLOOKUP(A7,B2:E3,2,FALSE)」 - 「A7」に入力されたデータを、 - 「B2からE3」から探し、 - 該当する箇所の「2」行目のデータを抽出する
どんな時に使える？ ・複数の行に分かれたエクセルのデータを1行にまとめて直したい 手順 ①B2のセルに=CLEAN(A2)と入力 ②下段までコピー＆ペースト ポイント ・CLEAN関数は「改行(改行文字)」など、印刷できない文字を自動的に削除する関数。これを使うことで、改行されたセルをまとめて1行に直すことができる。
「姓」と「名」を別々のセルに分ける (スペース前後の言葉を分けたい)
どんな時に使える？ ・一つのセルに記入された姓名を別々のセルに分けたい ・スペースで分けられた言葉を別々のセルに分けたい 手順 「姓」の抜き出し ①B2のセルに「=LEFT(A2,FIND(" ", A2)-1)」を入力 ②C2のセルに「=RIGHT(A2,LEN(A2)-FIND(" ",A2))」を入力 ③それぞれ下段までコピー＆ペースト。 ポイント ・LEFT関数とRIGHT関数はそれぞれ、「選んだセルの左、もしくは右側から文字を 抜き出す」関数。 例：=LEFT(A2, 3) A2セルに入力された文字の、左から3文字を抜き出す。 A2が「あいうえお」の場合、「あいう」が出力される。 ・…
どんな時に使える？ ・表やデータに含まれる「スペースや文字」を纏めて消したい 手順 添付は人の名前の入ったリストですが、苗字と名前の間にスペースが入っています。これを無くして苗字と名前を続けた形に直したいのですが、一つずつ作業するのは面倒ですよね。 ①スペースを消したいデータを選択。キーボードから「CTRLとF」を入力。 ②「置換」を選択。検索のところに「 」とスペースを入力する。 (この時、全角のスペースと半角のスペースに注意。入力されているのが半角のスペ ースだった場合、半角のスペースで置換をしないと消えない) ③置き換えのところには何も入力しない。これによりスペースが「無」に置換される。…
「表の中でこのデータに紐づく情報を自動的に抜き出すにはどうすればいい？」 どんな時に使える？ ・表やデータの中から、あるデータに紐づく情報を自動的に抜き出す 手順 例題：製品Noに基づく製品名と単価を自動的に抜き出す ①抜き出したい情報を表示するセルをクリック。「=vlookup()」を入力。 (例ではI2に、「製品No 100にマッチする製品名」を抜き出したい) ②()の間をクリック。 ③紐づかせるキーデータがあるセルを選択 (この場合「製品No」をキーとするためH2セルを選択) ④「,」を入力。キーとなるデータを必ず左側の列にして範囲を指定。 (この場合製品NoがあるB列から、抜き出したい…
「表の中でこの商品、顧客、モデルはどれくらい含まれているんだろう？」 どんな時に使える？ ・表やデータの中から、数値以外にデータの数を算出したい 手順 ①集計した値を表示したいセルをクリック。「=countif()」を入力。(例ではE3) ②()の間をクリック。 ③集計した値を出したいエリアを選択。(この場合A2からA9) ④「,」を入力。集計する条件が入ったセルを選択。(例：D2=リンゴ) ⑤キーボードよりENTERを入力 コピペ用 =countif() 集計した値を表示したいセルに貼り付け。()の間をクリックした以下を選択。 =countif(数を数えたいエリア, 数える条件のセル)
どんな時に使える？ ・表や数値の中から、条件毎に合計値を算出したい場合 手順 ①合計値を表示したいセルをクリック。「=sumif()」を入力。 ②()の間をクリック。 ③合計値を出したい(数値欄ではない)のエリアを選択。(この場合A2からA9) ④「,」を入力。合計値を出したい条件が入ったセルを選択。(例：A11=リンゴ) ⑤「,」を入力。合計値を出したいセルをクリックして選択。(この場合B2からB9) ⑥キーボードでEnterをタイプ。「リンゴ」の合計値が算出される。 コピペ用 =sumif() 合計値を表示したいセルに貼り付け。()の間をクリックした以下を選択。 =sumif(条件のエリア…
どんな時に使える？ ・表や数値の合計値を算出したい場合 手順 ①合計値を表示したいセルをクリック。「=sum()」を入力。 ②()の間をクリックするとセルが選択できるようになるので、 合計を出したいセルをクリックして選択。(この場合B2からB9) ③キーボードでEnterをタイプ。 コピペ用 =sum() 合計値を表示したいセルに貼り付け、()の間をクリックして合計対象のセルを選択してENTER。
「ブログリーダー」を活用して、keiEXさんをフォローしませんか？
指定した記事をブログ村の中で非表示にしたり、削除したりできます。非表示の場合は、再度表示に戻せます。
画像が取得されていないときは、ブログ側にOGP(メタタグ)の設置が必要になる場合があります。
IF関数とIFS関数の違い：初心者向けガイド Excelで条件に基づいたデータ処理を行う際に便利な関数として、IF関数とIFS関数があります。これらの関数の違いと、それぞれの使い方について詳しく説明します。 1. IF関数の使い方 IF関数は、指定した条件が真（TRUE）か偽（FALSE）かを判定し、それに応じて異なる値を返す関数です。基本的な構文は以下の通りです。 =IF(条件, 条件が真の場合の値, 条件が偽の場合の値) 基本的な例 以下の例では、セルA1の値が10以上であれば「合格」、それ以外なら「不合格」を表示します。 =IF(A1 >= 10, "合格", "不合格") 2. IFS…
エクセルで1行ごとのとびとびの合計を出す方法：初心者向けガイド エクセルで1行ごとのとびとびの値の合計を出す方法を解説します。特定の範囲内の一部のセルに対して合計を求めるケースで便利です。 1. 具体的な例 各月で予算、実績と並んだデータにおいて、それぞれの合計を計算します。 2. 基本的な方法 「偶数/奇数列の作成」 計算する列とは別に、偶数と奇数を判別する列を作成します。以下の数式を各行に入力することで、予算の行には1、実績の行には0の数値が付与されます。 =MOD(ROW(),2)) 奇数行(予算)の合計 D列が1の時、同じ行のC列の値を合計することが出来る以下の数式を、予算の合計値を出…
エクセルのIF関数の使い方：初心者向けガイド エクセルのIF関数は、条件に基づいて処理を行う際に便利な関数です。この記事では、IF関数の基本的な使い方と応用例について解説します。 1. 基本的な構文 IF関数の構文は次のとおりです： =IF(論理式, 真の場合の値, 偽の場合の値) 論理式：評価する論理式（例えば、セルの値が特定の条件に合致するかどうか）。 真の場合の値：論理式が真の場合に返す値（結果値）。 偽の場合の値：論理式が偽の場合に返す値（結果値）。 2. 基本的な例 例えば、セルA1に数値を入力し、その数値が50以上なら「合格」と表示し、そうでない場合は「不合格」と表示する場合： =…
エクセルの「COUNTIF」関数を用いた重複データのチェック / 削除方法：初心者向けガイド エクセルの「COUNTIF」関数を活用すると、重複データを簡単に特定し、それを削除することができます。このガイドでは、具体的な手順と方法を解説します。 1. 重複の特定 データの準備 例えば、セルA1からA10に以下のようなデータが含まれるとします。 「COUNTIF」関数の使用 列の先頭に重複数のカウントを表示するために、列B1に「=COUNTIF($A$1:$A$10, A1)」と入力します。 データをコピーし、フィルタ機能を使う 列Bのカウント数値をコピーし、データの重複が2以上のセルを検索しま…
エクセルでの四則演算の使い方：初心者向けガイド エクセルは数式や関数を使って効率的に計算を行うツールです。この記事では、エクセルでの基本的な四則演算（足し算、引き算、掛け算、割り算）に加えて、平方（二乗）操作の使い方も解説します。 足し算（＋） 例：セルA1に10、セルA2に5と入力し、セルA3に「=A1 + A2」と入力します。 結果として、セルA3に「15」と表示されます。 引き算（－） 例：セルB1に20、セルB2に7と入力し、セルB3に「=B1 - B2」と入力します。 結果として、セルB3に「13」と表示されます。 掛け算（＊） 例：セルC1に4、セルC2に3と入力し、セルC3に「=…
TEXT関数で曜日を表示する方法：初心者向けガイド TEXT関数は、Excelで日付や数値を指定した形式で文字列に変換するための関数です。特に、日付を曜日として表示する場合に非常に便利です。以下に、TEXT関数を使って曜日を表示する方法を中心に、基本的な使い方とポイントをわかりやすく説明します。 1. 基本的な構文 =TEXT(値, 表示形式) 値：日付や数値などの変換したいデータ。セル参照や特定の値を指定します。 表示形式：変換後の形式を指定する文字列。 2. 日付を曜日として表示する基本例 以下のデータを例にします。(エクセルにコピペできます) セル データ A1 2024/06/16 例…
VLOOKUPの使い方：初心者向けガイド VLOOKUP（ブイ・ルックアップ）は、Excelで広く使われる関数の一つで、データベースの中から指定した値を検索し、その値に関連するデータを取得するのに役立ちます。以下に、VLOOKUPの使い方をわかりやすく説明します。 1. 基本的な構文 VLOOKUP関数の基本的な構文は次のとおりです： =VLOOKUP(検索値, 範囲, 列番号, 検索の型) 検索値：検索する値。セル参照や特定の値を指定します。 範囲：検索するデータの範囲。範囲の左端の列に検索値が含まれている必要があります。 列番号：範囲内の検索結果を取得する列の番号（最初の列を1として数えま…
スキル/テクニック 01. データが何番目にあるか調べる (MATCH関数)02. IFを使わない簡単な条件分岐 (CHOOSE関数)03. 数字を切りよく整理する (切り上げる/切り下げる)04. 「トップ5 / ワースト5」を調べる (LARGE/SMALL関数)05. 行や列を削除したり移動しても、変わらない連番/通し番号をつける 06. 一部/特定の言葉を条件にして、合計値を出す07. 複数のセル/文字を、一つのセルに簡単に繋げたい08. 特定の文字「～～」を含むセルの数を数えたい09. 重複を除いたデータ一覧を出したい / 1回だけ出現するデータを見つけたい10. 数値の順位付け、ラ…
どんな時に使える？ 指定したデータが、リストの何番目にあるかを検索したい、数字で表したい。 指定したデータの位置(～番目)を表す MATCH関数の使い方 ①値を表示したいセルに以下のようにMATCH関数を入力する (例：F2に「 =MATCH(E2,$B$2:$B$11,0) 」を入力) ポイント ・「MATCH関数」は「指定したデータ」が「指定した範囲の何番目にあるか」を示す 例：=MATCH(E2,$B$2:$B$11,0)) 青字＝指定したデータ。この場合は「リンゴ」と自由入力された隣のE2 赤字＝指定した範囲。この場合は商品名が並んだB2～B11 緑字＝照合の型。0と入力することで完全…
どんな時に使える？ アンケートなど、1, 2, 3, 4,,,などの番号で分かれている結果に対して、 表示する値を切り替える IFを使わない条件分岐 CHOOSE関数の使い方 ①値を表示したいセルにCHOOSE関数を入力する (例：C3に「=CHOOSE(B3,"満足","やや満足","やや不満","不満")」を入力) ポイント ・「CHOOSE関数」は1からの数字に該当する値を出力する 例：=CHOOSE(B3,"満足","やや満足","やや不満","不満") 青字＝番号が入力されたセル 赤字＝1からの数値に対応する結果。 この場合「1から4まで」4つの数字に該当する結果が出力される 1：満…
どんな時に使える？ 入力された数値を切りがよい数値になるように「切り上げ/切り下げ」する (10, 50, 100, 1000単位など) 切り下げの場合 ：FLOOR.MATH関数 切り上げの場合 ：CEILING.MATH関数 近い方向にまとめる場合：MROUND関数 例：「50」の単位で切り下げ (FLOOR.MATH関数の場合) ①C2セルに「=FLOOR.MATH(B2,50)」を入力 （詳細は以下ポイント参照） ②必要なセルまでコピー＆ペースト ポイント ・「FLOOR.MATH関数」「CEILING.MATH関数」「MROUND関数」を活用する ことで、「ある数値の単位」で切り下げ…
どんな時に使える？ 売り上げや点数などから、トップとワーストの数値を調べる トップ5の数値の出し方 ①順位付けのため、1～5まで入力しておく(例：E3～E7) ②F3のセルに「=LARGE($B$2:$B$11,E3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2～5のセルまでコピー＆ペーストする (例：F4～F7) ワースト5の数値の出し方 ①順位つけをするために、1～5まで入力しておく(例：H7～H7) ②F3のセルに「=SMALL($B$2:$B$11,H3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2…
どんな時に使える？ 削除や移動など、編集を行っても、変わらない連番や通し番号がつけられる よくある困りごと せっかく番号を付けたのに、編集によって番号が変わってしまい毎回やり直している 変わらない連番/通し番号の付け方 ①連番の先頭のセルの行番号に注目。(例の場合は2) ②行番号が2の場合は「=row()-1」を入力 (考え方は以下ポイントを参照) ③番号を付けたい下部までコピー＆ペーストする ポイント ・ROW関数を活用する。 ROW関数は「=row()が入力された行の番号」を出力する。 2行目に入力すると2、10行目に入力すると10となる。 ・つまり、2行目から1としたい場合、=row()…
どんな時に使える？ ・一部の言葉を条件に合計値を出したい 例題：詳細な住所と売り上げが入ったリストがあるが、大阪、東京、埼玉の地域ごと の売上合計値を算出したい 困りごと：リストには細かい住所も入っているため、SUM関数やSUMIF関数では、 条件としてヒットさせることが出来ない 例題の手順 ①F2のセルに「=SUMIF(A:A,E2&"*",C:C)」を入力 ②F3からF4までコピー＆ペースト ポイント ・「ワイルドカード」の機能を使う。 条件の箇所に「E2 & "*"」とすることで、条件は「大阪*」となる。 エクセルではワイルドカード「*」をつけることで「任意の文字を許す」となり、 「大阪…
どんな時に使える？ ・複数に分かれたセルの文字/データを一つのセルに繋げて(結合して)表示したい 例題の場合の手順 ①B2のセルに「=CONCAT(B2:D2)」を入力 (=CONCAT()を入力してB2からD2まで選択) ②B2からD2まで、3つのセルに分かれたセルが一つにまとめて表示される 別のやり方：結合するデータの間に「文字」を入れて繋げる場合 ①B2のセルに「=TEXTJOIN(", ",TRUE,B14:D14)」を入力 ②B2からD2まで、3つのセルに分かれたセルが一つに結合される 結合する文字の間に「, (カンマと半角スペース)」が入る ポイント =TEXTJOIN(", ",…
どんな時に使える？ ・「Countif」関数を活用して、「特定の文字」が含まれるセル/データの 数を数えたい 例題の場合の手順 ①D2のセルに「=COUNTIF(A2:A11,"*中央区*")」を入力、条件を「*」で囲む ②「中央区」の文字が含まれるセルの数が出力される ポイント ・「Countif」関数の条件に「*」を活用する。 「*」は任意の0から複数の文字列を含むことを許可する。 今回は「～～中央区～～」の形になっているので「*」で「中央区」を挟む。 条件が、 "中央区" の場合：セルが「中央区」のみのセルが算出される。例題では0となる。 "中央区*" の場合：「中央区～」のセルがカウン…
どんな時に使える？ ・重複したデータが含まれる表やリストから、重複を除いたデータの一覧を 整理したい 例題の場合の手順 ①C2のセルに「=UNIQUE(A2:A21)」を入力 ②重複を除いた形で、入力されたデータが自動的に整理される (1回しか現れないものも含む) ポイント ・もし、「=UNIQUE(A2:A21,,TRUE)」のように、3つ目の引数をTRUEに 指定すると、「1回だけ現れるデータのみ」抽出するようにも出来る。
どんな時に使える？ ・表中のデータの数値から、順位/ランキングをつけたい。 例題の場合の手順 ①C2のセルに「=RANK.EQ(B2,$B$2:$B$11)」を入力 (範囲のB2～B11に対して固定を意味する$マークを付けることを忘れない。 一つずつ付けなくても、F4キーで簡単に付与することが出来る) ②コピーしてC3～C11に貼り付け ポイント ・「RANK.EQ」関数を使うと数値の順位付けが可能。 もし同じ数値だった場合には「同列」となる。 (例：4位が同列だった場合＝1, 2, 3, 4, 4, 6)
どんな時に使える？ ・表中のデータの個数を数えたいが、数値以外のデータや空欄がある。 この中から、数値が入ったデータのみ数えたい。 例題の場合の手順 ①B12のセルに「=COUNT(B2:B11)」を入力 ②C12のセルにコピー＆ペースト ポイント ・「COUNT」関数は「数値が入力されているセルの数」を計算する。 空白や他の文字はカウントしないため、数値が入ったセルの数だけ表示される。 ・これ以外にも「COUNTA」関数を使うと、「空白以外のセルの数」を計算できる。
どんな時に使える？ ・表中の数値の平均値を出したいが、データが無い「0」などが含まれている 場合でも、正しい平均値を出したい。 手順 ①B13のセルに「=SUM(B2:B11)/COUNTIF(B2:B11,"<>0")」と入力 (上記2つの「B2:B11」を「平均値を算出したい数値セルのエリア」に変更) ポイント ・平均値は「合計÷個数」で求める。 ・そこで「SUM」関数で「全体の合計」を算出。 次に「COUNTIF」関数で「B2からB11の中で、0以外のデータの個数」を算出。 *ここで「0以外の」という条件には比較演算子を使った「"<>0"」を使っている。 <>は、「0ではない＝0を除く」…
どんな時に使える？ アンケートなど、1, 2, 3, 4,,,などの番号で分かれている結果に対して、 表示する値を切り替える IFを使わない条件分岐 CHOOSE関数の使い方 ①値を表示したいセルにCHOOSE関数を入力する (例：C3に「=CHOOSE(B3,"満足","やや満足","やや不満","不満")」を入力) ポイント ・「CHOOSE関数」は1からの数字に該当する値を出力する 例：=CHOOSE(B3,"満足","やや満足","やや不満","不満") 青字＝番号が入力されたセル 赤字＝1からの数値に対応する結果。 この場合「1から4まで」4つの数字に該当する結果が出力される 1：満…
どんな時に使える？ 入力された数値を切りがよい数値になるように「切り上げ/切り下げ」する (10, 50, 100, 1000単位など) 切り下げの場合 ：FLOOR.MATH関数 切り上げの場合 ：CEILING.MATH関数 近い方向にまとめる場合：MROUND関数 例：「50」の単位で切り下げ (FLOOR.MATH関数の場合) ①C2セルに「=FLOOR.MATH(B2,50)」を入力 （詳細は以下ポイント参照） ②必要なセルまでコピー＆ペースト ポイント ・「FLOOR.MATH関数」「CEILING.MATH関数」「MROUND関数」を活用する ことで、「ある数値の単位」で切り下げ…
どんな時に使える？ 売り上げや点数などから、トップとワーストの数値を調べる トップ5の数値の出し方 ①順位付けのため、1～5まで入力しておく(例：E3～E7) ②F3のセルに「=LARGE($B$2:$B$11,E3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2～5のセルまでコピー＆ペーストする (例：F4～F7) ワースト5の数値の出し方 ①順位つけをするために、1～5まで入力しておく(例：H7～H7) ②F3のセルに「=SMALL($B$2:$B$11,H3)」を入力 (範囲はずれないようにF4キーを活用し、絶対参照しておく＝$で囲む) ③順位2…
どんな時に使える？ 削除や移動など、編集を行っても、変わらない連番や通し番号がつけられる よくある困りごと せっかく番号を付けたのに、編集によって番号が変わってしまい毎回やり直している 変わらない連番/通し番号の付け方 ①連番の先頭のセルの行番号に注目。(例の場合は2) ②行番号が2の場合は「=row()-1」を入力 (考え方は以下ポイントを参照) ③番号を付けたい下部までコピー＆ペーストする ポイント ・ROW関数を活用する。 ROW関数は「=row()が入力された行の番号」を出力する。 2行目に入力すると2、10行目に入力すると10となる。 ・つまり、2行目から1としたい場合、=row()…
どんな時に使える？ ・一部の言葉を条件に合計値を出したい 例題：詳細な住所と売り上げが入ったリストがあるが、大阪、東京、埼玉の地域ごと の売上合計値を算出したい 困りごと：リストには細かい住所も入っているため、SUM関数やSUMIF関数では、 条件としてヒットさせることが出来ない 例題の手順 ①F2のセルに「=SUMIF(A:A,E2&"*",C:C)」を入力 ②F3からF4までコピー＆ペースト ポイント ・「ワイルドカード」の機能を使う。 条件の箇所に「E2 & "*"」とすることで、条件は「大阪*」となる。 エクセルではワイルドカード「*」をつけることで「任意の文字を許す」となり、 「大阪…
どんな時に使える？ ・複数に分かれたセルの文字/データを一つのセルに繋げて(結合して)表示したい 例題の場合の手順 ①B2のセルに「=CONCAT(B2:D2)」を入力 (=CONCAT()を入力してB2からD2まで選択) ②B2からD2まで、3つのセルに分かれたセルが一つにまとめて表示される 別のやり方：結合するデータの間に「文字」を入れて繋げる場合 ①B2のセルに「=TEXTJOIN(", ",TRUE,B14:D14)」を入力 ②B2からD2まで、3つのセルに分かれたセルが一つに結合される 結合する文字の間に「, (カンマと半角スペース)」が入る ポイント =TEXTJOIN(", ",…
どんな時に使える？ ・「Countif」関数を活用して、「特定の文字」が含まれるセル/データの 数を数えたい 例題の場合の手順 ①D2のセルに「=COUNTIF(A2:A11,"*中央区*")」を入力、条件を「*」で囲む ②「中央区」の文字が含まれるセルの数が出力される ポイント ・「Countif」関数の条件に「*」を活用する。 「*」は任意の0から複数の文字列を含むことを許可する。 今回は「～～中央区～～」の形になっているので「*」で「中央区」を挟む。 条件が、 "中央区" の場合：セルが「中央区」のみのセルが算出される。例題では0となる。 "中央区*" の場合：「中央区～」のセルがカウン…
どんな時に使える？ ・重複したデータが含まれる表やリストから、重複を除いたデータの一覧を 整理したい 例題の場合の手順 ①C2のセルに「=UNIQUE(A2:A21)」を入力 ②重複を除いた形で、入力されたデータが自動的に整理される (1回しか現れないものも含む) ポイント ・もし、「=UNIQUE(A2:A21,,TRUE)」のように、3つ目の引数をTRUEに 指定すると、「1回だけ現れるデータのみ」抽出するようにも出来る。
どんな時に使える？ ・表中のデータの数値から、順位/ランキングをつけたい。 例題の場合の手順 ①C2のセルに「=RANK.EQ(B2,$B$2:$B$11)」を入力 (範囲のB2～B11に対して固定を意味する$マークを付けることを忘れない。 一つずつ付けなくても、F4キーで簡単に付与することが出来る) ②コピーしてC3～C11に貼り付け ポイント ・「RANK.EQ」関数を使うと数値の順位付けが可能。 もし同じ数値だった場合には「同列」となる。 (例：4位が同列だった場合＝1, 2, 3, 4, 4, 6)
どんな時に使える？ ・表中のデータの個数を数えたいが、数値以外のデータや空欄がある。 この中から、数値が入ったデータのみ数えたい。 例題の場合の手順 ①B12のセルに「=COUNT(B2:B11)」を入力 ②C12のセルにコピー＆ペースト ポイント ・「COUNT」関数は「数値が入力されているセルの数」を計算する。 空白や他の文字はカウントしないため、数値が入ったセルの数だけ表示される。 ・これ以外にも「COUNTA」関数を使うと、「空白以外のセルの数」を計算できる。
どんな時に使える？ ・表中の数値の平均値を出したいが、データが無い「0」などが含まれている 場合でも、正しい平均値を出したい。 手順 ①B13のセルに「=SUM(B2:B11)/COUNTIF(B2:B11,"<>0")」と入力 (上記2つの「B2:B11」を「平均値を算出したい数値セルのエリア」に変更) ポイント ・平均値は「合計÷個数」で求める。 ・そこで「SUM」関数で「全体の合計」を算出。 次に「COUNTIF」関数で「B2からB11の中で、0以外のデータの個数」を算出。 *ここで「0以外の」という条件には比較演算子を使った「"<>0"」を使っている。 <>は、「0ではない＝0を除く」…
どんな時に使える？ ・通常、エクセルで「3/5」と分数を入力すると、勝手に日付に代わってしまう。 これを3/5と分数でそのまま表示させたい。 手順 ①表示させたい分数の前に「 0と半角スペース」を入力するだけ。 ②3/5がそのまま表示される。(データ上も0.6=3/5で入力される) ポイント ・この方法で入力すると自動的に約分される。 (5/10 = 1/2となる)
どんな時に使える？ ・年月日がまとまった日付のデータを、「年」「月」「日」それぞれ分けたい ・日付のデータから、年月日の一部のみを取り出したい 手順 ①「年」を入力するセルには「=YEAR(A2)」(A2の箇所を日付のセルに変更) ②「月」を入力するセルには「=MONTH(A2)」(A2の箇所を日付のセルに変更) ③「日」を入力するセルには「=DAY(A2)」(A2の箇所を日付のセルに変更) ④下段までコピー＆ペースト ポイント ・YEAR関数、MONTH関数、DAY関数を使うことで、日付のデータから それぞれ年月日の情報のみを抜き出すことができる。
どんな時に使える？ ・エクセルの住所データから都道府県だけまとめて抜き取りたい 手順 ①B2のセルに「=IF(MID(A2,4,1)="県", LEFT(A2,4),LEFT(A2,3))」と入力 (もしくは上記の全ての「A2」を「住所が入っているセル」に変更) ②下段までコピー＆ペースト ポイント ・都道府県のうち、「神奈川県、和歌山県、鹿児島県」のみが4文字で全て 「県」となる。その他の都道府県は全て3文字となる。 ・そこで「IF関数」「MID関数」「LEFT関数」を使い、 - 4文字目に「県」があれば「左から4文字を抜き出す」 - そうでなければ「左から3文字を抜き出す」 ・この処理を行…
どんな時に使える？ ・縦に整理された表から、該当するデータを抽出したい ・Vlookupでは列方向の検索しか出来ないが、縦(列)方向から抽出したい 手順 ①B7のセルに「=HLOOKUP(A7,B2:E3,2,FALSE)」と入力 ②製品No「A100」に該当する製品名「ジュース」が出力される ポイント ・Hlookup関数は検索する値に一致した、「縦(列)のデータ抽出」に活用できる ・「=HLOOKUP(A7,B2:E3,2,FALSE)」 - 「A7」に入力されたデータを、 - 「B2からE3」から探し、 - 該当する箇所の「2」行目のデータを抽出する
どんな時に使える？ ・複数の行に分かれたエクセルのデータを1行にまとめて直したい 手順 ①B2のセルに=CLEAN(A2)と入力 ②下段までコピー＆ペースト ポイント ・CLEAN関数は「改行(改行文字)」など、印刷できない文字を自動的に削除する関数。これを使うことで、改行されたセルをまとめて1行に直すことができる。
どんな時に使える？ ・一つのセルに記入された姓名を別々のセルに分けたい ・スペースで分けられた言葉を別々のセルに分けたい 手順 「姓」の抜き出し ①B2のセルに「=LEFT(A2,FIND(" ", A2)-1)」を入力 ②C2のセルに「=RIGHT(A2,LEN(A2)-FIND(" ",A2))」を入力 ③それぞれ下段までコピー＆ペースト。 ポイント ・LEFT関数とRIGHT関数はそれぞれ、「選んだセルの左、もしくは右側から文字を 抜き出す」関数。 例：=LEFT(A2, 3) A2セルに入力された文字の、左から3文字を抜き出す。 A2が「あいうえお」の場合、「あいう」が出力される。 ・…
どんな時に使える？ ・表やデータに含まれる「スペースや文字」を纏めて消したい 手順 添付は人の名前の入ったリストですが、苗字と名前の間にスペースが入っています。これを無くして苗字と名前を続けた形に直したいのですが、一つずつ作業するのは面倒ですよね。 ①スペースを消したいデータを選択。キーボードから「CTRLとF」を入力。 ②「置換」を選択。検索のところに「 」とスペースを入力する。 (この時、全角のスペースと半角のスペースに注意。入力されているのが半角のスペ ースだった場合、半角のスペースで置換をしないと消えない) ③置き換えのところには何も入力しない。これによりスペースが「無」に置換される。…
「表の中でこのデータに紐づく情報を自動的に抜き出すにはどうすればいい？」 どんな時に使える？ ・表やデータの中から、あるデータに紐づく情報を自動的に抜き出す 手順 例題：製品Noに基づく製品名と単価を自動的に抜き出す ①抜き出したい情報を表示するセルをクリック。「=vlookup()」を入力。 (例ではI2に、「製品No 100にマッチする製品名」を抜き出したい) ②()の間をクリック。 ③紐づかせるキーデータがあるセルを選択 (この場合「製品No」をキーとするためH2セルを選択) ④「,」を入力。キーとなるデータを必ず左側の列にして範囲を指定。 (この場合製品NoがあるB列から、抜き出したい…
「表の中でこの商品、顧客、モデルはどれくらい含まれているんだろう？」 どんな時に使える？ ・表やデータの中から、数値以外にデータの数を算出したい 手順 ①集計した値を表示したいセルをクリック。「=countif()」を入力。(例ではE3) ②()の間をクリック。 ③集計した値を出したいエリアを選択。(この場合A2からA9) ④「,」を入力。集計する条件が入ったセルを選択。(例：D2=リンゴ) ⑤キーボードよりENTERを入力 コピペ用 =countif() 集計した値を表示したいセルに貼り付け。()の間をクリックした以下を選択。 =countif(数を数えたいエリア, 数える条件のセル)
