すべての専門家が知っておくべきExcel /シートの5つの機能

公開: 2019-01-23

私は最初に10代の頃にMicrosoftExcelを使用し、数字をスプレッドシートに整理し、後でQuickBooksに整理しようとしたときに、領収書をいじくり回しました。私は父の友人が彼の納税申告書の準備をするのを手伝っていました、そして私は取引から数ドルを得ました。なぜ彼がこのようなことを処理するために16歳（スプレッドシートの経験がない）を信頼したのかわかりません。ペーパークリップのキャラクターであるClippyに、それが主張したように私を助けられなかった後、邪魔にならないように叫んだとき、私は「彼は私にこの仕事を素敵にしてくれた」と思わずにはいられませんでした。

それから何年も経った今、振り返ってみると、その当時の苛立たしい仕事に感謝しています。これは.XLSXで始まったキャリアの基盤であり、私はこのファイルタイプをGo FishDigitalで毎日使用しています。コンテンツマーケティングチームでは、クライアントのキャンペーンにデータを活用することがよくあります。これにより、ジャーナリストがストーリーで使用するための説得力のある資料を作成し、その結果、クライアントのバックリンクやニュース報道を獲得することができます。

マーケティングやSEOのようにデータ量の多い分野にいなくても、スプレッドシートの一般的な理解は、働く専門家にとって不可欠になっています。 MicrosoftExcelとGoogleSheetsは、遭遇する2つの主要なツールであり、非常によく似た構造になっています。以下は、データを正常に管理するために使用できるいずれかのツールの5つの機能のガイドです。

貴重なExcel関数とその使用方法

VLookup式
連結式
列へのテキスト
重複を削除する
ピボットテーブル

VLookup式

例：

ハンバーガーショップのスプレッドシートがあり、個々のハンバーガーショップが列Aにリストされており、住所、市、州、所有者名、年間売上高、およびその他の情報が隣接する列に表示されています。

あなたのハンバーガーショップの長いリストの中で、あなたが情報を必要とするのはほんの一握りです。理論的には、スプレッドシートをスクロールするか、リストを並べ替え/フィルタリングするか、Vlookup関数を使用して必要な情報を正確に引き出すことができます。この式には多くの使用例があり、慣れれば学ぶほど、時間を節約するために使用する方法を見つけることが多くなります。

設定方法：

まだ作成していない場合は、別のタブで新しいテーブルを作成します。

最初の列には、「ルックアップ値」が表示されます。これは、メインのスプレッドシートで検索する数式に指示する情報です。この情報は、上の最初のスクリーンショットのように、数式を確認するように指示しているテーブルの左端の列にある必要があります。
次に、次のように数式を隣接するセルに追加します。

= VLOOKUP（lookup_value、table_array、col_index_num、[range_lookup]）

lookup_value ：数式がプライマリテーブルで検索する必要のある情報を見つけることができるセル。この場合、数式をセルB2に入れると、この値はA2になります。

table_array ：数式が情報を検索およびプルするテーブル。私のハンバーガーショップのテーブルは「バーガーショップ」という名前のタブにあり、列Hまでの列にデータがあるため、この値は「バーガーショップ」！A：Hになります。

col_index_num ：これは、数式にどのような情報を戻したいかを伝える方法です。この例では、メインテーブルの列Fにある電話番号を探しています。 Fはアルファベットの6番目の文字であるため、数式のこの値は6になります。

[range_lookup] ：これは数式の論理セクションであり、オプションはTRUEまたはFALSEです。あなたがTRUEに置く場合は、式は、それが正確なものではないのです場合でも、あなたのルックアップ値に最も近いものに基づいて値を返します。あなたはFALSEを置く場合、それはあなたが参照され、正確なルックアップ値を見つけた場合、それは唯一の値を返します。ほとんどの場合、そしてこのハンバーガーショップの例では、 FALSEを入れたいと思います

最終式：

= VLOOKUP（A2、 'バーガーショップ'！A：H、3、FALSE）

連結式

例：

ハンバーガーショップの例を続けて、都市と州を1つの列にまとめたいとします。都市ごとにデータを分析したい場合もありますが、異なる州に同じ名前の都市があるため、それらを組み合わせる必要があります。連結関数は、さまざまなセルの内容を組み合わせるために使用できる非常に簡単な数式です。

設定方法：

まず、テーブルに新しい列を追加する必要があります。この列には、新しい結合データが存在します。

次に、次のように数式を追加します。

= CONCATENATE（text1、text2、…）

この数式にはテキストや参照セルを入力できるため、非常に用途が広くなります。テキストを追加するには、必要なものを入力し、その周りに引用符を追加します。この例では、市と州の間にコンマとスペースを追加します。

最終式：

= CONCATENATE（C2、”、“、D2）

列へのテキスト

例：

前の例とは逆の問題があるとします。ハンバーガーショップごとに市と州を組み合わせていますが、それらを別々の列に配置したいとします。「テキストから列へ」機能は、特定の基準に基づいてデータを複数の列に分割する簡単な方法です。スペースまたはダッシュやコンマなどの特定の区切り文字に基づいてテキストを区切ることができます。

設定方法：

以下のスクリーンショットのように、分離する列の右側に列を追加します（または、データを3つ以上のセクションに分離する場合は複数の列を追加します）。

区切るテキストで列全体を強調表示します（この場合は列C）
ツールバーの「データ」タブをクリックします
「テキストから列へ」をクリックします
「区切り」が選択されていることを確認します
「次へ」をクリックします
次に、適用する区切り文字を選択します

ハンバーガーショップの例では、適用される区切り文字はコンマであるため、[コンマ]チェックボックスのみが選択されていることを確認します。この時点で、単純なテキストから列への操作で、「完了」をクリックすると、データが分割されていることがわかります。列のタイトルを更新する必要がある場合があります。

重複を削除する

例：

このハンバーガーショップのスプレッドシートでは、同じハンバーガーショップが複数回リストされている場合があります。カウントまたは平均の形式でピボットテーブルでこのデータを分析することを計画している場合は、データセットに重複する値は絶対に必要ありません。

この例では、各ハンバーガーショップには一意の名前があるため、同じショップ名の2つの行は、削除する必要がある重複を示します。特定のセルに同じタイトルのアイテムが複数あるスプレッドシートを使用している場合、住所や都市などによって異なりますが、単純な連結式（上記を参照）を使用して、テーブル内の各アイテムに一意の「名前」を付け、それに基づいて重複を削除します。

設定方法：

まず、最初の列（列A）をクリックし、マウスを最後の関連する列にドラッグして、テーブル内のすべての列を強調表示します。

次に、ツールバーから[データ]をクリックし、[重複を削除]をクリックします。ウィンドウが表示され、一意である必要がある値を持つ列を除くすべての列のチェックを外します。この例では、ハンバーガーショップ名があるため、列Aです。

最後に、[OK]をクリックすると、削除された重複がある場合はExcelから通知されます。これは、Excelが重複が見つかった行のすべてのデータを削除したことを意味します。

ピボットテーブル

例：

州全体の年間売上高を比較したいとします。ピボットテーブルを使用すると、データセットを取得してさまざまな方法で要約できます。これは、Excelまたはスプレッドシートの最も便利な機能の1つです。

設定方法：

データのテーブルに移動し、その中の任意の場所をクリックします
- ピボットテーブルから特定の行または列を除外することを目的としない限り、関連するすべてのセルを手動で強調表示する必要はありません。ピボットテーブル関数は、ブレークのない完全なテーブル内のすべてのデータを自動的に取得します。
Excelの[挿入]セクションまたはスプレッドシートの[データ]セクションで、[ピボットテーブル]をクリックします。
Excelでは、ウィンドウが表示され、[OK]をクリックします。これで、ピボットテーブルを設定して、データを希望どおりに要約できるようになります。

州ごとの平均年間売上高を取得するには、「州」を「フィールド名」リストから「行」というタイトルのセクションにドラッグします。
「年間売上高」を「値」というタイトルのセクションにドラッグします
この例では、合計ではなく、これらの値の平均を確認する必要があります。これを変更するには、[値]セクションの[年間売上高の合計]の横にある[i]アイコンをクリックし、表示されるリストから[平均]を選択します。

次に、州ごとの平均年間売上高が表示されます

その他のユースケース：

ピボットテーブルを使用する方法は無限にあります。分析するデータセットによって異なります。売り出し中の住宅のスプレッドシートでは、市または州ごとの売り出し中の住宅の数を見つけることができます。過去4年間の月ごとの収益の表では、月ごとまたはその年の四半期ごとの平均収益額を見つけることができます。計算フィールドを含む、より複雑なピボットテーブルを設定することもできます。

まとめ

確かに、これらの関数の1つを初めて使用するときは、それを機能させようとするのは苛立たしい経験かもしれません。これが起こった場合、私はあなたがそれに固執することをお勧めします。これらの重要な機能をしっかりと把握することは、データの使用を必要とするあらゆる業界で価値があります。データはほぼすべてです。練習すればするほど、スプレッドシートをより簡単かつ迅速に使用できるようになり、将来の分析をまとめるときに時間を節約できます。