Mengimpor dan membentuk data dalam Excel untuk Mac (Power Query)

Excel untuk Mac menggabungkan Power Query (disebut juga teknologi Dapatkan & Transformasi) untuk menyediakan kapabilitas yang lebih besar saat mengimpor, merefresh, dan mengautentikasi sumber data, mengelola sumber data Power Query, menghapus kredensial, mengubah lokasi sumber data berbasis file, dan membentuk data ke dalam tabel yang sesuai dengan persyaratan Anda. Anda juga bisa membuat kueri Power Query dengan menggunakan VBA.

Microsoft 365 Warisan

Catatan: SQL Server Sumber data Database hanya dapat diimpor di Insider Beta.

Anda bisa mengimpor data ke Excel menggunakan Power Query dari berbagai sumber data: Buku Kerja Excel, Teks/CSV, XML, JSON, database SQL Server, Daftar SharePoint Online, OData, Tabel Kosong, dan Kueri Kosong.

Pilih Data > Dapatkan Data.
Untuk memilih sumber data yang diinginkan, pilih Dapatkan Data (Power Query).
Dalam kotak dialog Pilih sumber data , pilih salah satu sumber data yang tersedia.
Menyambungkan ke sumber data. Untuk mempelajari selengkapnya tentang cara menyambungkan ke setiap sumber data, lihat Mengimpor data dari sumber data.
Pilih data yang ingin Anda impor.
Muat data dengan mengklik tombol Muat .

Hasil

Data yang diimpor muncul dalam lembar baru.

Hasil umum untuk kueri

Langkah berikutnya

Untuk membentuk dan mentransformasi data menggunakan Editor Power Query, pilih Transformasi Data. Untuk informasi selengkapnya, lihat Membentuk data dengan Editor Power Query.

Catatan: Fitur ini umumnya tersedia bagi pelanggan Microsoft 365, menjalankan Versi 16.69 (23010700) atau yang lebih baru dari Excel untuk Mac. Jika Merupakan pelanggan Microsoft 365, pastikan Anda memiliki office versi terbaru.

Prosedur

Pilih Data > Dapatkan Data (Power Query).
Untuk membuka Editor Kueri, pilih Luncurkan Editor Power Query.

Tips: Anda juga bisa mengakses Editor Kueri dengan memilih Dapatkan Data (Power Query), memilih sumber data, lalu mengklik Berikutnya.
Bentuk dan ubah data Anda dengan menggunakan Editor Kueri seperti yang Anda lakukan di Excel untuk Windows. Untuk informasi selengkapnya, lihat Power Query untuk Bantuan Excel.
Setelah selesai, Pilih Beranda > Tutup & Muat.

Hasil

Data yang baru diimpor muncul dalam lembar baru.

Hasil umum untuk kueri

Anda bisa merefresh sumber data berikut ini: file SharePoint, daftar SharePoint, folder SharePoint, OData, file teks/CSV, buku kerja Excel (.xlsx), file XML dan JSON, tabel dan rentang lokal, dan database Microsoft SQL Server.

Merefresh untuk pertama kalinya

Saat pertama kali Anda mencoba merefresh sumber data berbasis file dalam kueri buku kerja, Anda mungkin perlu memperbarui jalur file.

Pilih Data,panah di samping Dapatkan Data, lalu Pengaturan Sumber Data. Kotak dialog Pengaturan sumber data muncul.
Pilih koneksi, lalu pilih Ubah Jalur File.
Dalam kotak dialog Jalur file , pilih lokasi baru, lalu pilih Dapatkan Data.
Pilih Tutup.

Refresh waktu berikutnya

Untuk merefresh:

Semua sumber data dalam buku kerja, pilih Data > Refresh Semua.
Sumber data tertentu, klik kanan tabel kueri pada lembar, lalu pilih Refresh.
PivotTable, pilih sel dalam PivotTable, lalu pilih Analisis PivotTable > Refresh Data.

Saat pertama kali Anda mengakses SharePoint, SQL Server, OData, atau sumber data lain yang memerlukan izin, Anda harus menyediakan kredensial yang sesuai. Anda mungkin juga ingin menghapus kredensial untuk memasukkan kredensial baru.

Masukkan kredensial

Saat merefresh kueri untuk pertama kalinya, Anda mungkin diminta untuk masuk. Pilih metode autentikasi dan tentukan kredensial masuk untuk tersambung ke sumber data dan lanjutkan dengan refresh.

Jika masuk diperlukan, kotak dialog Masukkan kredensial akan muncul.

Misalnya:

Kredensial SharePoint:
kredensial SQL Server:

Menghapus kredensial

Pilih Data > DapatkanPengaturan Sumber Data > Data.
Dalam kotak dialog Pengaturan Sumber Data, pilih koneksi yang Anda inginkan.
Di bagian bawah, pilih Hapus Permissions.
Konfirmasi inilah yang ingin Anda lakukan, lalu pilih Hapus.

Meskipun penulisan dalam Editor Power Query tidak tersedia di Excel untuk Mac, VBA mendukung penulisan Power Query. Mentransfer modul kode VBA dalam file dari Excel untuk Windows ke Excel untuk Mac adalah proses dua langkah. Program sampel disediakan untuk Anda di akhir bagian ini.

Langkah satu: Excel untuk Windows

Di Excel Windows, kembangkan kueri menggunakan VBA. Kode VBA yang menggunakan entitas berikut dalam model objek Excel juga berfungsi di Excel untuk Mac: Objek kueri, objek WorkbookQuery, Properti Workbook.Queries.Untuk informasi selengkapnya, lihat Referensi VBA Excel.
Di Excel, pastikan Editor Visual Basic terbuka dengan menekan ALT+F11.
Klik kanan modul, lalu pilih Ekspor File. Kotak dialog Ekspor akan muncul.
Masukkan nama file, pastikan ekstensi file adalah .bas, lalu pilih Simpan.
Unggah file VBA ke layanan online agar file mudah diakses dari Mac.Anda dapat menggunakan Microsoft OneDrive. Untuk informasi selengkapnya, lihat Menyinkronkan file dengan OneDrive di Mac OS X.

Langkah kedua: Excel untuk Mac

Unduh file VBA ke file lokal, file VBA yang Anda simpan di "Langkah satu: Excel untuk Windows" dan diunggah ke layanan online.
Di Excel untuk Mac, pilih Alat > Makro > Visual Basic Editor. Jendela Visual Basic Editor akan muncul.
Klik kanan objek di jendela Proyek, lalu pilih Impor File. Kotak dialog Impor File akan muncul.
Temukan file VBA, lalu pilih Buka.

Contoh kode

Berikut adalah beberapa kode dasar yang dapat Anda adaptasi dan gunakan. Ini adalah kueri sampel yang membuat daftar dengan nilai dari 1 hingga 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

Buka buku kerja Excel.
Jika Anda mendapatkan peringatan keamanan tentang koneksi data eksternal yang dinonaktifkan, pilih Aktifkan Konten.
Jika kotak dialog Berikan Akses File muncul, pilih Pilih, lalu pilih Berikan Akses ke folder tingkat atas yang berisi file sumber data.
Pilih Data > Dari Teks (Warisan). Kotak dialog inder Fakan muncul.
Temukan file .txt atau .csv, lalu pilih Buka. Panduan Impor Teks muncul.Tips Periksa berulang kali panel Pratinjau data yang dipilih untuk mengonfirmasi pilihan Anda.
Di halaman pertama, lakukan hal berikut:

Tipe File Untuk memilih tipe file teks, pilih Dipisahkan atau Lebar tetap.Nomor Baris Di Mulai impor di baris, pilih nomor baris untuk menentukan baris pertama data yang ingin Anda impor.Kumpulan Karakter Di Asal file, pilih kumpulan karakter yang digunakan dalam file teks. Biasanya, Anda dapat membiarkan pengaturan ini tetap default.
Di halaman kedua, lakukan hal berikut:Dipisahkan Jika Anda memilih Dipisahkan pada halaman pertama, di bawah Pemisah, pilih karakter pemisah atau gunakan kotak centang Lainnya untuk memasukkan karakter yang tidak tercantum.Pilih Perlakukan pemisah berurutan sebagai satu jika data Anda berisi pemisah lebih dari satu karakter di antara bidang data atau jika data Anda berisi beberapa pemisah kustom.Dalam Pengualifikasi teks, pilih karakter yang mengapit nilai dalam file teks Anda, yang paling sering adalah karakter tanda kutip (").

Lebar tetap Jika Anda memilih Lebar tetap di halaman pertama, ikuti instruksi untuk membuat, menghapus, atau memindahkan garis hentian dalam kotak Pratinjau data yang dipilih .
Di halaman ketiga, lakukan hal berikut: Untuk setiap kolom di bawah Pratinjau data yang dipilih, pilih, lalu ubah menjadi format kolom berbeda jika Anda mau. Anda dapat mengatur format tanggal lebih lanjut dan memilih Tingkat Lanjut untuk mengubah pengaturan data numerik. Anda juga dapat mengonversi data setelah mengimpornya.Pilih Selesai. Kotak dialog Impor Data akan muncul.
Pilih tempat Anda ingin menambahkan data: baik pada lembar yang sudah ada, di lembar baru, atau dalam PivotTable.
Pilih OK.

Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Koneksi > Refresh.

Pilih Data > DariSQL Server ODBC. Kotak dialog Tersambung ke Sumber Data SQL Server ODBC akan muncul.
Masukkan server dalam kotak Nama Server , dan secara opsional, masukkan database dalam kotak Nama Database .Dapatkan informasi ini dari administrator database.
Di bawah Autentikasi, pilih metode dari daftar: Nama Pengguna/Kata Sandi, Kerberos, atau NTLM.
Masukkan kredensial dalam kotak Nama Pengguna dan Kata Sandi.
Pilih Sambungkan. Kotak dialog Navigator akan muncul.
Di panel kiri, navigasikan ke tabel yang Anda inginkan, lalu pilih tabel.
Konfirmasi pernyataan SQL di panel kanan. Anda dapat mengubah pernyataan SQL sesuai keinginan.
Untuk mempratinjau data, pilih Jalankan.
Jika sudah siap, pilih Kembalikan Data. Kotak dialog Impor Data akan muncul.
Pilih tempat Anda ingin menambahkan data: baik pada lembar yang sudah ada, di lembar baru, atau dalam PivotTable.
Untuk mengatur properti koneksi pada tab Penggunaan dan Definisi dari kotak dialog Properti , pilih Properti. Setelah mengimpor data, Anda juga bisa memilih Data > Koneksi, lalu dalam kotak dialog Properti Koneksi , pilih Properti.
Pilih OK.
Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Data > Refresh Semua.

Jika ingin menggunakan sumber eksternal yang bukan merupakan SQL Database (misalnya, FileMaker Pro), Anda dapat menggunakan driver Konektivitas Database Terbuka (ODBC) yang terinstal di Mac. Informasi pada driver tersedia di halaman web ini. Setelah driver untuk sumber data Anda diinstal, ikuti langkah-langkah ini:

Pilih Data > Dari Database (Microsoft Query).
Tambahkan sumber data untuk database Anda, lalu pilih OK.
Di perintah kredensial SQL Server, masukkan metode autentikasi, nama pengguna dan kata sandi.
Di sebelah kiri, pilih panah di samping server untuk melihat database.
Pilih panah di samping database yang Anda inginkan.
Pilih tabel yang Anda inginkan.
Untuk mempratinjau data, pilih Jalankan.
Jika sudah siap, pilih Kembalikan Data.
Dalam kotak dialog Impor Data , pilih tempat Anda ingin data berada: baik di lembar yang sudah ada, di lembar baru, atau di PivotTable.
Pilih OK.
Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Data > Refresh Semua.