Top

【Excel】の記事一覧

  個人情報は既存の商品購入履歴や新規顧客獲得するために非常に重要な情報となります。   しかしシステムの規模が大きくなるにつれて業務を行う時間は変わらないのに情報が膨大になり必要な情報を取得するのに大量の時間を要することもあるでしょう。そこでExcel関数を上手く用いて「Excel(エクセル)でデータ抽出」を行う方法をご紹介したいと思います。業務の効率化に利用して下さい。 Excelでデータ抽出する方法その1:「VLOOKUP関数」 顧客情報は管理する項目が多いため、1つのまとまった情報ではなくExcelデータ抽出すると別シートにて表記されるケースがあります。一緒の情報として確認したいといった場合に利用したい関数が「VLOOKUP関数」になります。「VLOOKUP関数」は各Excelデータに共通項目があれば突き合わせをして1つのシート上にまとめられる機能となります。   ○顧客情報例: ※例えば上記の情報の中で、「顧客情報A」の情報に「顧客情報B」の「最終購入日」の情報を追加して確認したい場合を例に突合せして「データ抽出」します。   1-1.「関数の挿入」を選択します。※「=」で直接入力出来る方法もあります 1-2.「関数の分類」を「すべて表示」に変更して、「VLOOKUP関数」を選択します 1-3.検索値に共通項目である「顧客№」を選択します(サンプルではA5セルを検索値に設定) 1-4.範囲指定は取得したい情報側に合わせます。(今回は最終購入日まで合わせます) 1-5.取得したい「最終購入日」は4列目なので列番号に「4」を入力します 1-6.最終購入日が挿入されました。 右下の■マークにマウスをあててドラッグすると以下の項目にも同じ関数が使われ同じ条件でデータ抽出されます。 Excelでデータ抽出する方法その2:「IF関数」 「IF関数」を用いることで条件指定の結果をExcel上にデータ抽出することが出来ます。今回は購入金額によってランクの表記を表したいと思います。以下のサンプルを用いて「IF関数」でランキングを設定していきたいと思います。   ○ランキングのルール 購入額: ...

  ネットショップを運営して時間が経ってくると、手元にはさまざまな情報が含まれた膨大なデータが溜まっていきます。   近年、ビックデータと呼ばれるこれらの巨大な情報群がビジネスの役に立つとされ、高値で取引され、さまざまな研究が行われています。そこで今回は、Excelの標準機能である、ピポットテーブルを用いてExcel(エクセル)で行うデータ分析のやり方を紹介します。 「Excelでデータ分析」ピポットテーブルとは Excelを用いると、集計データからさまざまな情報を得ることができます。しかし、普段から使っている人でも「データ分析」と聞くだけで標準偏差や最小二乗法といった専門用語がいっぱい出てきて難しそうなものといったイメージをもたれてしまうかもしれません。   そこでExcelデータ分析用いるのがピポットテーブルです。表の作成からデータ整理までを全て自動で行ってくれるため、知りたい情報をクリックするだけで、すぐに目的の情報を得ることができるようになります。 「Excelでデータ分析」実際にピポットテーブルを作ってみる まず、売上一覧表を用意します。ここでは簡単に、注文ナンバー、日付、商品名、単価、数量、金額、担当者の7個のデータを用いて進めていきます。 売上一覧表が用意できましたら、画面の左上に注目してください。 挿入タブを選択し、一番左側にあるピポットテーブルをクリックします。ピポットテーブルの作成という名前のウィンドウが出れば成功です。 そのまま、用意した売上データを左上からドラッグして、点線で囲みましょう。囲み終わりましたらOKボタンを。ピポットテーブルの完成です。 あれ????レポートの作成?混乱する気持ちを抑えながらExcel画面の右側を見てみましょう。 普段、見慣れない画面が新しく表示されています。ピポットテーブルはこの部分を操作することで集計データを表示させていきます。 何はともあれ売上の集計ですから、注文番号がないと困ります。レポートに追加するフィールドの中から「No.」と書かれた項目をクリックしたらそのまま離さずに「行ラベル」のエリアまでドラッグ&ドロップします。 行ラベルという名前の表ができました。このままだと物足りないどころか、注文番号がただ一列に並んでいるだけで全く役に立たないので、さらに要素を増やしていきます。   次は「商品名」を「列ラベル」の部分に持っていきましょう。同様にして「Σ 値」のエリアに「単価」「数量」「金額」の順に持っていきましょう。 全て終わると、このようになります。単価の部分が勝手に「合計/単価」、数量の部分が「合計/数量」、金額の部分が「合計/金額」と変更されてしまい、見づらいので「合計/」の部分を削除していきます。目的のセルをダブルクリックすると「値フィールドの設定」ウィンドウが表示されます。 名前の指定(C):の部分に表示されている「合計/」を削除してOKボタンを押します。表示されている表は普段作成する表と変わらない操作が可能なためレイアウトを整理していきます。 ぐっと見やすくなりました。 「Excelでデータ分析」ピポットテーブルでデータ分析を行ってみる これだけでも十分ですが、日付、担当者のデータもせっかく用意したので使ってみたいと思います。「行ラベル」のエリア内にすでにある「No.」の下に「担当者」を、一番上に「日付」を並べてみます。 表の一番左に示される内容が日付>注文番号>担当者に変化しました。   このように並び順を変えることで   「この日に誰が何を何個売ったか」 「誰が何を何個どの日に売ったか」   といったデータを瞬時に知ることが可能になります。他にもいろいろ試せますが、まずは順番を変えてみるだけでも大きな発見があるかもしれません。   今回は基本的な内容のデータしか用いていませんが、お客様からのレビューや評価、発送までの日数や特別なサービスの有無など、ありとあらゆるいろいろな情報を追加していくことで、意外な相関関係を見つけることができるかもしれません。     ...

データベースとはExcelに搭載されている機能で、膨大なデータを整理することができます。   Excelでデータベースを作成することにより、無数のデータを一瞬で並び替えたり、検索したり、目的の情報を抽出したりすることができるようになります。 Excelでデータベースを作成するための準備 Excelでデータベースを作るといっても特別な準備はほとんど必要ありません。   まず、注文番号、日付、商品名、値段、個数、金額の書かれた売上表を用意します。 このときに重要なのが、それぞれの情報の一番上にしっかりと何のデータなのかを記入することです。自分で見ればわかるので、無くてもよいと思い、記入しないでいるとデータベースを作成することができません。しっかりと記入しておきましょう。 Excelでデータベースを作成する データの準備ができたところでExcelでデータベースを作って行きましょう。まずデータタブに移動して、図の①の部分をクリックします。クリックすると、その下の部分が図のように変化します。次に②の詳細設定をクリックしてください。 「フィルタオプションの設定」という名前のウィンドウが表示されたら、そのままデータベース化したい表のタイトルから一番下まで全ての範囲を囲います。 次に赤枠の中の「フィルタ」を押します。   この際、指定された範囲を使用してコマンドを完了できませんでした。範囲内のセルを1つ選択し、コマンドを再実行してください。というメッセージが表示されてしまう場合は、先ほど選択した表の内側のセルをどこでもいいのでクリックしてから「フィルタ」を押しましょう。 無事に押せたら「No.」「日付」などの真横にボタンが追加されているかと思います。これでデータベースの完成です。 Excelデータベースを操作してみる それでは、できあがったExcelデータベースを操作してみましょう。例として用意された売上表ですが、よく見るとNo.1から表示されていますね?このままだと、新しい(注文番号の大きい)データがどんどん下に行ってしまいます。しかし、大昔の注文を参照する時などの特殊な場合を除けば、主に知りたいデータは下の方にスクロールしなければたどりつけなくなってしまいます。   そこで、「No.」が最も大きいものを一番上に、最も小さいものを一番下に表示させたいと思います。   まず「No.」の横に新しくできたボタンをクリックします。図中①クリックしたら表示されるメニューから降順を選択し、クリックします。図中② 並び順が変更されて、下までスクロールしなくても最新のデータが見られるようになりました。 Excelデータベースから目的の情報を抽出する 続いて、商品名がキーホルダーの物だけをリストアップする方法を紹介します。最新のデータが一番上に来るようにソート(並び替え)をしましたが、商品名がごちゃごちゃと並び、何がどれだけ売れたかがとても見にくいままです。そこで、キーホルダー以外の物を非表示にします。   商品名の横のボタンを押します。図中① 今回はキーホルダーA,B,Cのみを表示させたいのでそれ以外のケースやストラップの左側にあるチェックボックスを外します。図中② 完了したらOKボタンを押します。図中③ 先ほどまでごちゃごちゃと並んでいた商品名が嘘のようにすっきりとした表示になりました。 ですが、こちらも良く見るとキーホルダー商品が抜粋されているものの、キーホルダーAやB, Cがめちゃくちゃな順番で並んでいます。そこで、最初の操作と同じようにソート(並び替え)を行いたいと思います。   今度も商品名の横のボタンを押してメニューを開きます。図中① そして昇順を選択します。図中② 最初のごちゃごちゃした売上表が一転。とっても見やすい表になりました。 Excelでデータベース作成を作成することは想像されていたより非常に簡単に作ることができたかと思います。残念ながら今回だけでは機能の全てを紹介しきれませんでしたが、ほかにも便利な機能が多数実装されていますので、ぜひ皆さんでいろいろ動かしてみて触れてみて、この便利な機能を使いこなしてください。    ...

日々のネットショップ運営お疲れ様です。   楽天やYahoo!など複数のネットショップを運営していると必ず出てくる悩みが在庫管理。 本日はネットショップ運営に欠かせない在庫管理について、Excel(エクセル)が苦手だけどなんとか頑張って使っている人に簡単に作成できる在庫管理表の作成方法をまとめました。     在庫管理改善支援センター様が考案された「3欄式在庫管理表」をご紹介いたします。     Excelで在庫管理その1.:まずは表の項目を作成する まずは表の項目を入力しましょう。行タイトルの「品番号」や「品名」など個々に違うので自分のニーズに合うように変更してもOKです。列タイトルで日ごとに管理できる仕様にしましょう。   「コピー」機能を使って表の項目欄のコピーする方法 同じ項目を繰り返す場合はコピー機能を使うといくつでもスピーディにコピーできます。d~eを繰り返せばいくつでもコピー可能です。   a.コピー元をドラッグする   b.右クリックする   c.「コピー」をクリックする   d.コピー先のセルを右クリックする   e.「貼り付け」アイコンをクリックする   f.コピー完了!   「フィルハンドル」を使って表の項目欄のコピーする方法 セルごとのワードの配置を自動で読み取って入力することができるフィルハンドルは非常に便利な機能です。あらゆる規則を読み取って入力できるとっても賢い機能です。在庫管理表の作成で大いに役立つでしょう。   A.コピー元で先頭となるセルをクリックする   B.コピー元の規則となるセルをドラッグする   C.フィルハンドルを続きの方向へドラッグする すると規則正しく「2月4日」「2月5日」…と順に入力されます。   D.コピー完了 Excelで在庫管理その2.:表を作成する a.表を作成したい範囲をドラックして範囲指定する b.「挿入」タブをクリックする c.「テーブル」アイコンをクリックする d.「テーブルの作成」ダイアログで範囲を確認する ①aで範囲指定したのが間違えていないか確認し、間違えていたら「テーブルの作成」ダイアログで指定し直します。   ②クリックすると先頭行を見出し項目に指定できます。任意で利用すると便利ですよ。今回はスルーします。   ③できたら「OK」ボタンをクリックします。   e.テーブル完成! Excelで在庫管理その3.日々の在庫が自動で入力されるよう関数を挿入する a.セルを指定して関数を入力する 例として2月1日の在庫は「前月繰越の在庫」+「2月1日の入庫数」-「2月1日の出庫数」になります。よって在庫のセルには「=D7+E5-E6」と入力します。①をクリックして②に入力し、最後はEnterキーを押しましょう。 つづいて2月2日から4日くらいまでも同じように入力してください。上でご紹介したフィルハンドルを使えるので便利に計算式を規則正しくコピーできますよ。日付だけでなく計算式もコピーできるとても賢い機能です。   b.「フィルハンドル」を使ってセルの式を規則正しくコピーする まずはコピー元となる計算式が入力されているセルをドラックします。 つづいてフィルハンドルを最後のセルまでドラッグします。   c.コピー完了! つづいて靴とバックも同じように計算式を入力してください。   Excelで在庫管理その4.:在庫管理表の使い方 日々の入庫と出庫を入力するだけで瞬時に日々の在庫を把握できますよ。自分で計算する手間なし、自分で作成するため0円で使える魅力の在庫管理表です。 最後に 日々の在庫状況が把握できる在庫管理表を使って管理しましょう。ここでご紹介したものを「3欄式在庫管理表」といい出庫と入庫・在庫のみを管理できます。見やすいのが特徴で現品管理に適しているためネット通販を運営する方にもぴったりです。   逆にいろいろな項目を管理できる在庫管理表のほうがよいのでは?と心配になる方もいるでしょう。しかしあまり複雑なものを使うと使いづらいため面倒になってしまい、やがて使わなくなってしまうおそれもあるためなるべく単純なものがベストです。   基本的に在庫管理は入庫と出庫のみを把握できていればよく、管理項目はなるべく少ないもののほうが優秀なのですよ。そのようなときに適している在庫管理表を自分で作成して役立てましょう。     ▼本記事は「在庫管理改善支援センター」様のご協力のもと制作しております▼ 3欄式在庫管理表(在庫管理改善支援センター様考案)     ...