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

Excel for Microsoft 365 for Mac その他…表示数を減らす

Excel for the Mac には Power Query (Get & Transform とも呼ばれます) テクノロジが組み込まれており、データソースのインポート、更新、認証、Power Query データソースの管理、資格情報のクリア、ファイルベースのデータソースの場所の変更、要件に合ったテーブルへのデータの整形の際により優れた機能を提供します。 VBA を使用して Power Query のクエリを作成することもできます。

Microsoft 365 Begin >>>

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

注: SQL Server データベースデータソースは Insider ベータでのみインポートできます。

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

[データ] > [データの取得] の順に選択します。

目的のデータソースを選択するには、[データの取得 (Power Query)] を選択します。
[データソースの選択] ダイアログボックスで、使用可能なデータソースのいずれかを選択します。

データソースに接続します。各データソースに接続する方法の詳細については、「データソースからデータをインポートする」を参照してください。
インポートするデータを選択します。
[読み込み] ボタンをクリックして、データを読み込みます。

結果

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

次のステップ

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

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

注: この機能は、バージョン 16.69 (23010700) 以降の Excel for Mac を実行していて、Microsoft 365 サブスクリプションをご利用の方が一般的に利用できます。 Microsoft 365 サブスクリプションをお持ちの場合は、最新バージョンの Office をインストールしているかどうか確認してください。

手順

[データ] > [データの取得 (Power Query)] の順に選択します。
クエリエディターを開くには、[Power Query エディターの起動] を選択します。

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

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

詳細については、「 target=”_blank” title=”Power Query for Excel のヘルプ” rel=”noopener”>Power Query for Excel のヘルプ」を参照してください。

完了したら、[ホーム] > [閉じて読み込む] の順に選択します。

結果

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

データソースの更新

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

初回の更新

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

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

その後の更新

更新するには:

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

資格情報の入力とクリア

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

資格情報の入力

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

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

次に例を示します。

SharePoint 資格情報:

SQL Server 資格情報:

資格情報のクリア

[データ] > [データの取得] > [データソースの設定] の順に選択します。
[データソースの設定] ダイアログボックスで、目的の接続を選択します。
下部にある [アクセス許可のクリア****] を選択します。
これが目的であることを確認し、[削除] を選択します。

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

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

手順 1: Excel for Windows

Excel Windows では、VBA を使用してクエリを作成します。 Excel のオブジェクトモデルで次のエンティティを使用する VBA コードは、Excel for Mac でも機能します: Queries オブジェクト、WorkbookQuery オブジェクト、Workbook.Queries プロパティ。詳細については、「Excel VBA リファレンス」を参照してください。
Excel で、Alt + F11 キーを押して、Visual Basic エディターが開いていることを確認します。
モジュールを右クリックして、[エクスポートファイル] を選択します。 [エクスポート] ダイアログボックスが表示されます。
ファイル名を入力し、ファイル拡張子が .bas であることを確認してから、[保存] を選択します。
VBA ファイルをオンラインサービスにアップロードして、Mac からファイルにアクセスできるようにします。

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

手順 2: Excel for Mac

VBA ファイルをローカルファイルにダウンロードします。この VBA ファイルは "手順 1: Excel for Windows" で保存し、オンラインサービスにアップロードしたものです。
Excel for Mac で、[ ツール] > [マクロ] > [Visual Basic エディター] の順に選択します。 [Visual Basic エディター] ウィンドウが表示されます。
[プロジェクト] ウィンドウでオブジェクトを右クリックし、[ファイルのインポート] を選択します。 [ファイルのインポート] ダイアログボックスが表示されます。
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

Microsoft 365 End <<<

過去のプラン Begin >>>

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

Excel ブックを開きます。
外部データ接続が無効になっているというセキュリティ警告が表示された場合は、[コンテンツを有効にする] を選択します。
[ファイルアクセスの許可] ダイアログボックスが表示された場合は、[選択] を選択し、データソースファイルを含む最上位フォルダーへの [アクセス許可を付与] を選択します。
[データ] > [テキストから (レガシ)] の順に選択します。 [検索****] ダイアログボックスが表示されます。
.txt または .csv ファイルを見つけて、[開く] を選択します。[テキストインポートウィザード] が表示されます。

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

最初のページで、次の操作を行います。

ファイルの種類 テキストファイルの種類を選択するには、[区切り記号付き] または [固定幅] を選択します。

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

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

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

区切り記号付き

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

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

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

固定幅

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

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

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

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

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

接続が動作していることを確認するには、データを入力し、[接続] > [更新] の順に選択します。

SQL Database (ODBC) に接続する

[データ] > [SQL Server ODBC****から] の順に選択します。 [SQL Server ODBC データソースへの接続] ダイアログボックスが表示されます。

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

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

[認証] で、リストから方法として、[ユーザー名/パスワード] 、[Kerberos] 、または [NTLM] を選択します。
[ユーザー名] ボックスと [パスワード] ボックスに資格情報を入力します。
[接続] を選択します。 [ナビゲーター] ダイアログボックスが表示されます。
左側のウィンドウで、目的のテーブルに移動し、それを選択します。
右側のウィンドウで SQL ステートメントを確認します。 SQL ステートメントは、必要に応じて変更できます。
データをプレビューするには、[実行] を選択します。
準備ができたら、[データを返す] を選択します。 [データのインポート] ダイアログボックスが表示されます。

データを追加する場所として、既存のシート、新しいシート、またはピボットテーブル内を選択します。
[プロパティ] ダイアログボックスの [使用法] タブと [定義] タブで接続プロパティを設定するには、[プロパティ] を選択します。データをインポートしたら、[データ] > [接続] の順に選択し、[接続のプロパティ] ダイアログボックスで [プロパティ] を選択することもできます。
[OK] を選択します。
接続が動作していることを確認するには、データを入力し、[データ] > [すべて更新] の順に選択します。

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

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

[データ] > [データベースから (Microsoft Query)] の順に選択します。
データベースのデータソースを追加して、[OK] を選択します。
SQL Server 資格情報プロンプトで、認証方法、ユーザー名、パスワードを入力します。

左側で、サーバーの横にある矢印を選択し、データベースを表示します。
必要なデータベースの横にある矢印を選択します。
目的のテーブルを選択します。
データをプレビューするには、[実行] を選択します。
準備ができたら、[データを返す] を選択します。
[データのインポート] ダイアログボックスで、データを配置する場所として、既存のシート、新しいシート、またはピボットテーブル内を選択します。
[OK] を選択します。
接続が動作していることを確認するには、データを入力し、[データ] > [すべて更新] の順に選択します。

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

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

[データ] > [接続] の順に選択します。 [ブックの接続] ダイアログボックスが表示されます。
リストから目的の接続を選択し、[アクセス許可のクリア] を選択します。

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

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

Microsoft 365 Begin >>>

Microsoft 365 End <<<

過去のプラン Begin >>>

過去のプラン End <<<

関連項目