Mengubah Tata Letak Jenis Kalendar Menjadi Format Jadual Dalam LuckyTemplates Menggunakan Editor Pertanyaan

Menganalisis data yang kita ada boleh menjadi sukar jika ia tidak disusun dengan betul. Dalam tutorial ini, saya akan menerangkan cara mengubah data berulang menegak dan mendatar ke dalam format jadual. Kami melakukan ini supaya kami boleh mempunyai masa yang lebih mudah menganalisisnya menggunakan LuckyTemplates. Anda boleh menonton video penuh tutorial ini di bahagian bawah blog ini.

Tutorial ini dibuat untuk menjawab soalan  ahli  . Ahli itu meminta cara dia boleh mengubah suai datanya daripada reka letak jenis kalendar kepada format jadual.

Sebelum kita mula, mari kita periksa dahulu data yang kita ada.

Di sini, kami mempunyai reka letak data di mana hari ditetapkan secara mendatar dan minggu ditetapkan secara menegak. Susunan jenis ini biasa kepada kebanyakan kita kerana ini adalah cara kita boleh memahami data dengan mudah.

Walau bagaimanapun, reka letak jenis ini lebih sukar untuk dianalisis kerana terdapat banyak lajur dan baris untuk dipertimbangkan.

Dengan itu, mari kita ubah data menjadi format jadual yang betul di mana kita berakhir dengan lajur untuk tarikh, lajur untuk pergerakan dan lajur lain untuk nama.

Isi kandungan

• Membersihkan Data Untuk Format Jadual yang Betul
• Pecah Meja
• Mengenalpasti Senarai Untuk Jadual Baharu Untuk Format Jadual yang Betul
• Menentukan Nilai Yang Mana Ada Dalam Jadual
• Mengumpulkan Data Untuk Format Jadual yang Betul
• Melampirkan Jadual Asing
• Kesimpulan

Membersihkan Data Untuk Format Jadual yang Betul

Untuk memudahkan urusan, mari tukar data kami di dalam Editor Power Query . Tetapi sebelum kita berbuat demikian, mari pastikan Formula Bar kelihatan pada skrin anda. Jika tidak, pergi ke tab Lihat kemudian tandai kotak di sebelah Formula Bar.

Langkah pertama ialah mengalih keluar nilai yang tidak diperlukan dalam data kami. Mari kita keluarkan lajur pertama dan dua baris teratas data kita kerana kita tidak begitu memerlukannya. Untuk berbuat demikian, klik pada tab Alih Keluar Baris, klik Alih Keluar Baris Teratas, masukkan 2, kemudian tekan OK.

Dan sekarang untuk mengalih keluar lajur pertama, pergi ke tab Pilih Lajur dan kemudian nyahpilih lajur 1 dengan menyahtanda kotak di sebelahnya.

Mari kita juga mengalih keluar indeks baris 14 hingga 16 kerana ia diisi sepenuhnya dengan nilai nol sahaja. Sekali lagi, pergi ke tab Alih Keluar Baris kemudian klik Alih Keluar Baris Kosong .

Isu lain yang kami ada ialah nilai tarikh melangkau lajur setiap kali. Tarikh sentiasa diikuti dengan nilai nol seperti yang diserlahkan di bawah. Ini berlaku bukan sahaja pada baris atas tetapi pada baris di bawah juga.

Untuk mengisi jurang ini, mari kita tukar jadual data kita dahulu dengan pergi ke  tab Transform kemudian mengklik butang Transpose.

Jadual kini sepatutnya kelihatan seperti telah diterbalikkan selepas melakukan tindakan itu.

Seterusnya, pilih semua lajur yang mengandungi tarikh, klik kanan pengepala lajur, kemudian pilih Isi.

Ini sepatutnya menduplikasi tarikh dan mengisi jurang dalam lajur yang dipilih.

Pecah Meja

Melihat kepada data yang kita ada sekarang, kita melihat bahawa terdapat empat jadual bersebelahan antara satu sama lain. Lajur 1 hingga 7 membentuk satu jadual, lajur 8 hingga 13 membentuk jadual lain, dan seterusnya.

Untuk menambahkannya ke dalam satu jadual, kita perlu melakukan operasi unpivot asas untuk mengubahnya menjadi format jadual yang betul. Tetapi oleh kerana jadual bersebelahan antara satu sama lain, kita tidak boleh begitu sahaja. Kita perlu memecahkan meja ini dan mencantumkannya semula menjadi meja yang lebih kecil.

Untuk melakukan itu, kami akan mencipta formula pertama untuk tutorial ini. Mari klik butang fx kemudian tambah formula yang akan kita gunakan. Kami akan menggunakan operasi Table.ToColumns . Ini mencipta senarai senarai bersarang nilai lajur daripada jadual. Setiap lajur akan dijadikan senarai.

Ini adalah cara saya menyediakan formula.

Formula mencipta senarai lajur yang kami ada sebelum ini. Nilai pertama senarai mengandungi lajur 1, nilai kedua mengandungi lajur 2 dan seterusnya.

Mengenalpasti Senarai Untuk Jadual Baharu Untuk Format Jadual yang Betul

Sekarang, kita harus mengenal pasti nilai dalam senarai yang mengandungi nilai tarikh. Setelah kami mengetahui di mana tarikh ini terletak, kami boleh menentukan di mana setiap jadual berasingan kami bermula atau berakhir.

Untuk berbuat demikian, mari kita tukar semula senarai itu kepada jadual dengan mengklik butang Ke Jadual di penjuru kiri sebelah atas.

Kemudian tambahkan lajur indeks di sebelahnya.

Perkara seterusnya yang akan kita lakukan ialah menambah lajur lain di sebelah lajur indeks. Lajur ini seharusnya membantu kami menentukan nilai pada Lajur1 yang mengandungi nilai tarikh. Mari namakan lajur ini Indeks2.

Untuk melakukan itu, kami akan memasukkan formula dalam Index2, yang mengembalikan nilai indeks Column1 jika ia mengandungi tarikh.

Kami akan menggunakan , dan kemudian merujuk Column1. Tanda kurung akan berfungsi sebagai pengendali; menambah 0 di antara kurungan ini bermakna kita mendapat nilai pertama daripada senarai; maka kita tentukan sama ada ia adalah tarikh. Jika ya, saya mahu formula mengembalikan nombor indeks; jika tidak, saya tidak mahu ia mengembalikan sebarang nilai sama sekali.

Memandangkan nilai pertama dalam Lajur1 mengandungi tarikh, nilai pertama dalam Index2 hendaklah mengandungi nombor indeksnya 0. Selain itu, memandangkan nilai kedua dalam Lajur1 tidak mengandungi tarikh, maka nilai kedua dalam Index2 hendaklah hanya mengandungi nilai nol.

Beginilah cara saya menyediakan formula untuk Index2. Hanya pastikan bahawa tiada ralat sintaks dalam formula anda.

Selepas melakukan itu, lajur Index2 kami kini sepatutnya mempunyai indeks nilai dalam Lajur1 yang mengandungi tarikh. Ia juga akan memasukkan beberapa nilai nol hasil daripada formula yang telah kami sediakan.

Menentukan Nilai Yang Mana Ada Dalam Jadual

Kini, nilai indeks di dalam Index2 ini menunjukkan permulaan dan penghujung jadual berasingan yang kita ada. Iaitu, 0 menandakan permulaan jadual pertama, 7 menandakan berakhirnya jadual pertama, dan permulaan jadual kedua, dan seterusnya.

Untuk membantu kami mengenal pasti nilai dalam Lajur1 yang tergolong dalam jadual yang sama, mari gunakan tindakan isian dalam lajur baharu kami.

Selepas melakukan tindakan, nilai nol kini harus digantikan dengan sifar, tujuh, tiga belas dan dua puluhan. Nombor ini menunjukkan bahawa ia tergolong dalam jadual yang sama. Iaitu, sifar membentuk satu jadual, tujuh membentuk jadual lain, dan seterusnya.

Mengumpulkan Data Untuk Format Jadual yang Betul

Memandangkan kita sudah mengetahui nilai dalam Column1 yang tergolong dalam jadual yang sama, mari kita kumpulkan nilai tersebut bersama-sama. Untuk melakukan itu, kami akan menggunakan operasi Kumpulan Mengikut.

Dengan berbuat demikian, kami kini hanya mendapat 4 nilai dalam lajur Index2 kami. Perhatikan bahawa lajur lain bernama Count berada di sebelah lajur Index2 kami. Lajur ini mengira bilangan baris dalam setiap nilai kami dalam lajur Index2.

Walau bagaimanapun, ini sebenarnya bukan apa yang kita mahu lakukan. Apa yang perlu kita lakukan ialah membentuk jadual baharu menggunakan lajur yang kita ada.

Untuk melakukan itu, kami akan menggunakan operasi Table.FromColumns , rujuk lajur yang mengandungi nilai senarai kami iaitu Column1, dan kemudian tukar jenis pulangan kepada jadual.

Adalah penting untuk memastikan bahawa kami telah merujuk lajur yang betul dalam formula kami. Semak sama ada Column1 benar-benar mengandungi nilai senarai.

Ini mencantumkan semua nilai dalam Lajur1 dengan nilai Indeks2 0 ke dalam satu jadual, nilai Indeks2 7 ke dalam jadual lain dan seterusnya.

Kami menyemak setiap rekod kepada nilai 0, kemudian mendapatkan semula nilai senarai daripada Lajur1. Akhirnya, kami mencantumkan senarai tersebut untuk membentuk satu jadual menggunakan kod M ini.

Kami mengambil 7 lajur pertama dan mencantumkannya ke dalam satu jadual, mengambil beberapa lajur seterusnya, mencantumkannya ke meja lain, dan seterusnya.

Melampirkan Jadual Asing

Memandangkan kita telah mengumpulkannya bersama-sama, mari cuba tambah semua jadual dengan menambah satu lagi langkah. Sekali lagi, mari tekan butang fx untuk menambah formula baharu.

Operasi Table.Combine menggabungkan senarai jadual, dan kemudian mencipta satu jadual tunggal daripadanya. Di sini, kami akan merujuk lajur yang mengandungi senarai jadual kami, iaitu lajur Count.

Kami kini mempunyai jadual di mana semua jadual bersarang dilampirkan semula ke dalam satu meja tunggal yang besar.

Selepas menambahkan semua jadual, kami kini boleh melakukan beberapa langkah terakhir dalam mengubah data kami kepada format jadual yang betul. Pilih dua lajur pertama jadual anda dan kemudian klik butang Nyahpaut Lajur Lain dalam tab Ubah.

Selepas itu, alih keluar lajur atribut kerana kami tidak begitu memerlukannya. Pergi ke Pilih Lajur , kemudian nyahpilih lajur atribut.

Kemudian akhirnya, tetapkan jenis yang betul bagi semua lajur dan labelkannya dengan betul.

Klik dua kali pada pengepala lajur setiap lajur untuk menukar nama mereka dengan sewajarnya.

Untuk lajur pertama, tukar jenisnya kepada tarikh kerana ia mengandungi nilai tarikh kami.

Kemudian, pilih lajur kedua dan ketiga, dan tetapkan jenisnya kepada teks.

Semua selesai! Beginilah rupa format jadual kami yang betul.

Kesimpulan

Dalam tutorial ini, kami menukar beberapa data, yang dibentangkan dalam susun atur jenis kalendar, kepada format jadual yang betul. Kami mula-mula membersihkan data kami, memecahkannya kepada jadual yang lebih kecil, mengumpulkan jadual yang tergolong bersama, dan kemudian menambahkan semuanya pada akhirnya.

Tutorial ini menawarkan anda banyak pengetahuan tentang cara memodelkan data anda dengan betul untuk analisis yang lebih mudah.

Memahami konsep yang digunakan di sini adalah sangat penting kerana ia juga boleh digunakan dalam banyak cara lain.

Jika konsep masih tidak jelas kepada anda, menyemak pautan di bawah mungkin membantu anda.

Selamat bekerja melalui yang ini!

Melissa