Excel for Mac でデータをインポートおよび整形する (Power Query)

データ ソースのインポート

Power Query を使用して、Excel ブック、テキスト/CSV、XML、JSON、SQL Server データベース、SharePoint Online リスト、OData、空のテーブル、空のクエリなど、さまざまなデータ ソースから Excel にデータをインポートできます。

1. [データ>データの取得] を選択します。

   

2. 目的のデータ ソースを選択するには、[データの取得 (Power Query)] を選択します。

3. [ データ ソースの選択 ] ダイアログ ボックスで、使用可能なデータ ソースのいずれかを選択します。

   

4. データ ソースに接続します。 各データ ソースに接続する方法の詳細については、「データ ソース からデータをインポートする」を参照してください。

5. インポートするデータを選択します。

6. [読み込み] ボタンをクリックして、データを 読み込 みます。

結果

インポートされたデータが新しいシートに表示されます。

次のステップ

Power Query エディターを使用してデータを整形および 変換するには、[データの変換] 選択します。 詳細については、「Power Query エディターを使用してデータを整形する」を参照してください。

Power Query エディターを使用してデータを整形する

手順

1. [データ>データの取得 (Power Query) を選択します。

2. クエリ エディターを開くには、[Power Query エディターの起動] を選択します。

   

   ヒント

   [データの取得 (Power Query)] を選択し、データ ソースを選択し、[次へ] をクリックして、クエリ エディターにアクセスすることもできます。

3. Excel for Windows の場合と同様に、クエリ エディターを使用してデータを整形および変換します。

   

   詳細については、「Power Query for Excel のヘルプ」を参照してください。

4. 完了したら、[ホーム] >[閉じる] & [読み込み] を選択します。

結果

新しくインポートされたデータが新しいシートに表示されます。

データ ソースの更新

SharePoint ファイル、SharePoint リスト、SharePoint フォルダー、OData、テキスト/CSV ファイル、Excel ブック (.xlsx)、XML ファイルと JSON ファイル、ローカル テーブルと範囲、Microsoft SQL Server データベース、フォルダーなど、次のデータ ソースを更新できます。

初回の更新

ブック クエリでファイル ベースのデータ ソースを初めて更新する場合は、ファイル パスの更新が必要になる場合があります。

1. [データ]、[データの取得] の横にある矢印、[データ ソースの設定] の順に選択します。 [データ ソースの設定] ダイアログ ボックスが表示されます。
2. 接続を選択し、[ファイル パスの変更] を選択します。
3. [ ファイル パス ] ダイアログ ボックスで、新しい場所を選択し、[ データの取得] を選択します。
4. [Close] (閉じる) を選択します。

その後の更新

更新するには:

• ブック内のすべてのデータ ソースで、[ データ>Refresh All] を選択します。
• 特定のデータ ソースで、シート上のクエリ テーブルを右クリックして、[更新] を選択します。
• ピボットテーブルで、ピボットテーブル内のセルを選択し、[ピボットテーブルの分析]、[参照データ] の順に選択>。

資格情報の入力とクリア

SharePoint、SQL Server、OData、またはアクセス許可が必要なその他のデータ ソースに初めてアクセスするときは、適切な資格情報を提供する必要があります。 資格情報をクリアして新しい資格情報を入力することもできます。

資格情報の入力

クエリを初めて更新すると、ログインを求められる場合があります。 認証方法を選択し、ログイン資格情報を指定してデータ ソースに接続し、更新を続行します。

ログインが必要な場合は、[ 資格情報の入力 ] ダイアログ ボックスが表示されます。

次に例を示します。

• SharePoint 資格情報:

  

• SQL Server 資格情報:

  

資格情報のクリア

1. [ Data>Get Data>Data Source Settings] を選択します。
2. [データ ソースの設定] ダイアログ ボックスで、目的の接続を選択します。
3. 下部にある [ アクセス許可のクリア] を選択します。
4. これが目的であることを確認し、[削除] を選択します。

Power Query VBA コードの作成および転送

Excel for Mac では Power Query エディターでの作成は利用できませんが、VBA では Power Query の作成がサポートされています。 ファイル内の VBA コード モジュールの Excel for Windows から Excel for Mac への転送は、2 段階の処理で行われます。 このセクションの最後にサンプル プログラムが用意されています。

手順 1: Excel for Windows

1. Excel Windows では、VBA を使用してクエリを作成します。 Excel のオブジェクト モデルで次のエンティティを使用する VBA コードは、query オブジェクト、WorkbookQuery オブジェクト、Workbook.Queries プロパティのExcel for Macでも機能します。詳細については、「Excel VBA リファレンス」を参照してください。

2. Excel で、Alt + F11 キーを押して、Visual Basic エディターが開いていることを確認します。

3. モジュールを右クリックして、[エクスポート ファイル] を選択します。 [エクスポート] ダイアログ ボックスが表示されます。

4. ファイル名を入力し、ファイル拡張子が .bas であることを確認してから、[保存] を選択します。

5. VBA ファイルをオンライン サービスにアップロードして、Mac からファイルにアクセスできるようにします。

   Microsoft OneDrive を使用できます。 詳細については、「Mac OS X の OneDrive とファイルを同期する」を参照してください。

手順 2: Excel for Mac

1. VBA ファイルをローカル ファイルにダウンロードします。この VBA ファイルは "手順 1: Excel for Windows" で保存し、オンライン サービスにアップロードしたものです。
2. Excel for Macで、[ツール>Macro>Visual Basic Editor] を選択します。 [Visual Basic エディター] ウィンドウが表示されます。
3. [プロジェクト] ウィンドウでオブジェクトを右クリックし、[ファイルのインポート] を選択します。 [ファイルのインポート] ダイアログ ボックスが表示されます。
4. VBA ファイルを見つけて、[開く] を選択します。

サンプル コード

適応して使用できる基本的なコードを次に示します。 これは、1 から 100 までの値を持つリストを作成するサンプル クエリです。

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub

TXT ファイルまたは CSV ファイルのインポートと更新

1. Excel ブックを開きます。

2. 外部データ接続が無効になっているというセキュリティ警告が表示された場合は、[ コンテンツの有効化] を選択します。

3. [ファイル アクセスの許可] ダイアログ ボックスが表示された場合は、[選択] を選択し、データ ソース ファイルを含む最上位フォルダーへの [アクセス許可を付与] を選択します。

4. [ Data>From Text (レガシ)] を選択します。 [ Finder ] ダイアログ ボックスが表示されます。

5. .txt または .csv ファイルを見つけて、[開く] を選択します。テキスト インポート ウィザードが表示されます。

   ヒント選択したデータ ウィンドウのプレビューを繰り返しチェックして、選択内容を確認します。

6. 最初のページで、次の操作を行います。
   ファイルの種類 テキスト ファイルの種類を選択するには、[ 区切り記号付き] または [ 固定幅] を選択します。

   行番号[インポートを開始する行] で、行番号を選択して、インポートするデータの最初の行を指定します。

   文字セット [ ファイルの配信元] で、テキスト ファイルで使用される文字セットを選択します。 ほとんどの場合、この設定は既定のままにしておくことができます。

7. 2 番目のページで、次の操作を行います。

   区切り記号付き

   最初のページで [区切り記号] を選択した場合は、[区切り記号] で区切り文字を選択するか、[その他のチェック] ボックスを使用して一覧にない文字を入力します。

   データフィールド間に複数の文字の区切り記号が含まれている場合、またはデータに複数のカスタム区切り記号が含まれている場合は、[連続する区切り記号を 1 つとして扱う] を選択します。

   [テキスト修飾子] で、テキスト ファイル内の値を囲む文字を選択します。これは、ほとんどの場合、引用符 (") 文字です。
   固定幅

   最初のページで [固定幅 ] を選択した場合は、指示に従って、[ 選択したデータのプレビュー ] ボックスで改行を作成、削除、または移動します。

8. 3 番目のページで、次の操作を行います。

   [ 選択したデータのプレビュー] で列ごとに選択し、必要に応じて別の列形式に変更します。 日付形式をさらに設定し、[ 詳細設定 ] を選択して数値データの設定を変更できます。 インポート後にデータを変換することもできます。

   [完了] をクリックします。 [データのインポート] ダイアログ ボックスが表示されます。

9. データを追加する場所として、既存のシート、新しいシート、またはピボットテーブル内を選択します。

10. [OK] を選択します。
    接続が動作していることを確認するには、データを入力し、[ 接続>Refresh] を選択します。

SQL Database (ODBC) に接続する

1. [Data>From SQL Server ODBC] を選択します。 [SQL Server ODBC データ ソースへの接続] ダイアログ ボックスが表示されます。

   

2. [サーバー名] ボックスにサーバーを入力し、必要に応じて [データベース名] ボックスにデータベースを入力します。

   この情報はデータベース管理者から入手します。

3. [認証] で、リストから方法として、[ユーザー名/パスワード]、[Kerberos]、または [NTLM] を選択します。

4. [ ユーザー名] ボックスと [ パスワード ] ボックスに資格情報を入力します。

5. [接続] を選択します。 [ ナビゲーター ] ダイアログ ボックスが表示されます。

6. 左側のウィンドウで、目的のテーブルに移動し、それを選択します。

7. 右側のウィンドウで SQL ステートメントを確認します。 SQL ステートメントは、必要に応じて変更できます。

8. データをプレビューするには、[実行] を選択 します。

9. 準備ができたら、[データを返す] を選択します。 [データのインポート] ダイアログ ボックスが表示されます。

   

10. データを追加する場所として、既存のシート、新しいシート、またはピボットテーブル内を選択します。

11. [プロパティ] ダイアログ ボックスの [使用状況] タブと [定義] タブで接続プロパティを設定するには、[プロパティ] を選択します。 データをインポートした後、[ データ>接続] を選択し、[ 接続のプロパティ ] ダイアログ ボックスで [ プロパティ] を選択することもできます。

12. [OK] を選択します。

13. 接続が動作していることを確認するには、データを入力し、[ データ>Refresh All] を選択します。

別のデータベースに接続する (Microsoft Query)

SQL データベースではない外部ソース (FileMaker Pro など) を使用する場合は、Mac にインストールされている ODBC (Open Database Connectivity) ドライバーを使用できます。 ドライバーの情報は、この Web ページに記載されています。 データ ソースのドライバーをインストールしたら、次の手順を実行します。

1. [ Data>From Database (Microsoft Query)] を選択します。

2. データベースのデータ ソースを追加して、[OK] を選択します。

3. SQL Server 資格情報プロンプトで、認証方法、ユーザー名、パスワードを入力します。

   

4. 左側で、サーバーの横にある矢印を選択し、データベースを表示します。

5. 必要なデータベースの横にある矢印を選択します。

6. 目的のテーブルを選択します。

7. データをプレビューするには、[実行] を選択 します。

8. 準備ができたら、[データを返す] を選択します。

9. [データのインポート] ダイアログ ボックスで、データを配置する場所として、既存のシート、新しいシート、またはピボットテーブル内を選択します。

10. [OK] を選択します。

11. 接続が動作していることを確認するには、データを入力し、[ データ>Refresh All] を選択します。

ログイン資格情報のクリア

アクセス許可が機能していない場合は、最初にそれらをクリアしてからログインしないでください。

1. [ データ>接続] を選択します。 [ブックの接続] ダイアログ ボックスが表示されます。

2. リストから目的の接続を選択し、[アクセス許可のクリア] を選択します。