Konversi data mingguan ke excel harian

Halo jdunkerley79,

saya mendapatkan kesalahan "Kesalahan. Hasilkan Baris (182). Nilai tidak berubah setelah Loop Expression. "

dan di Alat Hasilkan Baris, bolehkah saya tahu apa arti ungkapan ini Left(DateTimeAdd([Date],1,"days"),10)

Saya menebak, di bidang tanggal tambahkan 1 hari ke tanggal mulai periode, tapi Bolehkah saya tahu apa yang dimaksud dengan '10'. Apakah loop ini hanya berfungsi untuk 10 baris?

hai @vickyd,

Anda dapat merujuk ke contoh di bawah ini untuk menghasilkan tabel jam kerja harian analisis

1. Menggabungkan tabel target

Merged = UNION(Table1,Table2) 

2. Hasilkan tabel kalender dengan id dan target

Result =
VAR startDate =
    MINX ( Merged, [End Date] )
        + ( 1 - WEEKDAY ( MINX ( Merged, [End Date] ), 1 ) )
VAR endDate =
    MAXX ( Merged, [End Date] )
RETURN
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR ( startDate, endDate ),
            DISTINCT ( SELECTCOLUMNS ( Merged, "ID", [ID] ) )
        ),
        "Target", LOOKUPVALUE (
            Merged[Work Hour],
            Merged[ID], [ID],
            Merged[End Date], [Date]
                + ( 7 - WEEKDAY ( [Date], 1 ) )
        )
            + 0,
        "Day of Week", FORMAT ( [Date], "ddd" ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Work Hour", IF ( WEEKDAY ( [Date], 1 ) <> 1 && WEEKDAY ( [Date], 1 ) <> 7, 8, 0 )
    )

3. Tambahkan kolom terhitung untuk menghitung jam kerja yang tersedia

Available Hours =
VAR isWorkDay =
    IF (
        WEEKDAY ( [Date], 1 ) <> 1
            && WEEKDAY ( [Date], 1 ) <> 7,
        TRUE (),
        FALSE ()
    )
VAR rolling =
    SUMX (
        FILTER (
            ALL ( Result ),
            [ID] = EARLIER ( [ID] )
                && WEEKNUM ( [Date], 1 ) = WEEKNUM ( EARLIER ( Result[Date] ), 1 )
                && [Date] < EARLIER ( [Date] )
        ),
        [Work Hour]
    )
RETURN
    IF (
        [Target] = 0,
        0,
        IF (
            rolling < [Target]
                && rolling + 8
                > [Target],
            [Target] - rolling,
            IF ( rolling < [Target] && isWorkDay, 8 )
                + 0
        )
    )

Salam,

Xiaoxin Sheng

Tim Dukungan Komunitas _ Xiaoxin
Jika postingan ini membantu, harap pertimbangkan untuk menerima sebagai solusi untuk membantu anggota lain menemukannya dengan lebih cepat

Tutorial ini akan mengajari Anda cara mengonversi data ringkasan mingguan menjadi data total bulanan dengan mengalokasikan hari dalam setiap minggu ke bulan yang sesuai dalam setahun. Mari selami

Periksa Kumpulan Data

Pertama, mari kita lihat data sampel kami ...

Di sini kami memiliki beberapa data penjualan gabungan mingguan yang cukup standar. Di Kolom A, kami memiliki kolom tanggal yang mencantumkan tanggal akhir setiap minggu (Sabtu). Di Kolom B, kami memiliki total penjualan yang terjadi pada minggu tersebut

Sepanjang tutorial ini, kami akan menganggap bahwa tanggal yang diberikan dalam data mingguan Anda adalah akhir pekan. Masih memungkinkan untuk mengonversi tanggal dan data awal minggu, tetapi rumusnya perlu diubah

Asumsi Konversi

Karena kami hanya meringkas data setiap minggu, tidak ada cara untuk mengetahui secara pasti data setiap hari, jadi Anda perlu membuat beberapa asumsi tentang data harian. Dalam tutorial ini, kami akan menganggap bahwa setiap hari adalah rata-rata – 1/7 dari total minggu. Anda bisa membuat asumsi yang berbeda, tetapi itu akan membuat rumusnya lebih rumit

Untuk mengalokasikan jumlah hari yang tepat untuk setiap bulan, kita perlu membuat rumus yang menghitung berapa hari dalam seminggu di bulan sebelumnya dan berapa banyak di bulan ini. Sebagian besar minggu akan sepenuhnya berada di bulan saat ini, sehingga bulan tersebut akan mendapatkan semua hari. Untuk melakukan ini dengan jelas, kami akan membuat kolom pembantu yang membantu kami menghitung konversi

Membuat Kolom Pembantu

Di kolom di sebelah data mingguan Anda (dalam kasus kami Kolom C), buat tajuk baru dan beri nama "Hari Dalam Bulan" atau yang serupa (judul hanya untuk referensi). Di baris dengan data baris pertama, ketik rumus berikut dan tekan ENTER

=MIN(A2-EOMONTH(A2,-1),7)

Rumus ini menggunakan fungsi EOMONTH(). Fungsi ini memberikan nilai tanggal untuk hari terakhir setiap bulan, relatif terhadap tanggal yang diberikan (Akhir BULAN). Sintaks untuk fungsi EOMONTH() adalah sebagai berikut

EOMONTH(start_date, months)

Tanggal_mulai adalah tanggal referensi dalam format tanggal Excel. Masukan bulan adalah bilangan bulat apa saja, positif atau negatif. Jika bulan adalah nol (0), EOMONTH() memberikan tanggal terakhir dari bulan tanggal_mulai. Jika 1, hari terakhir bulan depan. Jika -1, hari terakhir dari bulan sebelumnya, dan seterusnya

Dalam contoh ini, EOMONTH(A2,-1) menampilkan hari terakhir bulan sebelum tanggal di Kolom A

Fungsi MIN() hanya menampilkan angka terendah dari dua angka. Dalam contoh ini, ini akan menghitung (A2 – EOMONTH(A2,-1)) dan mengembalikan apa pun yang lebih rendah – jawabannya, atau angka 7

Di sel C2, Rumus menghitung sebagai berikut

MIN(7-Jan-17 - 31-Dec-16 = 7, 7)

Dalam hal ini, jawabannya sama, sehingga fungsi MIN() menampilkan 7

Seret rumus ke bawah untuk menyalinnya ke semua baris dalam kumpulan data Anda. Setelah selesai, Anda harus memiliki tabel data seperti ini

Sekarang, kita dapat menjumlahkan semua bagian bulan untuk mendapatkan total bulanan…

Membangun Rumus Total Bulanan, Bagian 1

Untuk memulai, kita perlu menyiapkan tabel bulanan. Di sebelah tabel data Anda, buat baris judul kalender bulan menggunakan tanggal, seperti yang ditunjukkan

Sekarang, kita dapat mulai menangkap porsi data yang termasuk dalam setiap bulan. Untuk memulai, ketik rumus berikut di sel E2 dan tekan ENTER

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1),1))*$C$2:$C$54/7*($B$2:$B$54))

Ini akan terlihat seperti di bawah ini, dan jangan khawatir – kami akan menelusuri apa yang sedang terjadi

Fungsi SUMPRODUCT() adalah satu perhitungan perkalian yang besar, tetapi ia melakukan perkalian tersebut untuk setiap baris dalam tabel data. Untuk itulah rentangnya ( $A$2. $A$54 ,  $B$2. $B$54 , dan  $C$2. $C$54 ). Bagian dengan fungsi DATE() sebenarnya membandingkan dua tanggal dan menampilkan 1 jika sama dan 0 jika tidak sama. Mari kita lihat lebih dekat.

(DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1) = DATE(YEAR(E$1),
MONTH(E$1),1))

Fungsi DATE() pertama membuat nilai tanggal Excel sama dengan bulan dan tahun setiap baris dalam tabel data. Fungsi DATE() kedua membuat nilai tanggal Excel sama dengan bulan dan tahun tabel kalender bulanan

Jika sama, SUMPRODUCT() akan menyertakan baris dalam penjumlahan. Jika tidak, rumus akan menambahkan nol (mengecualikannya dari jumlah)

Untuk setiap baris dengan tanggal pada bulan/tahun berjalan, sisa rumus akan dipertimbangkan

$C$2:$C$54/7*($B$2:$B$54)

Karena  Kolom C adalah kolom pembantu kita, ini berisi jumlah hari yang dihitung dalam bulan ini. Angka itu, dibagi 7, memberi kita bagian dari total mingguan untuk dialokasikan ke total bulan berjalan. Kami mengalikannya dengan total di Kolom B , dan hanya itu.

SUMPRODUCT() mengambil jumlah semua baris dengan sebagian minggu di bulan berjalan dan menjumlahkannya untuk mendapatkan bagian pertama dari total bulanan kami

Membangun Rumus Total Bulanan, Bagian 2

Rumus SUMPRODUCT() pertama mendapatkan sebagian besar nilai yang kami butuhkan, tetapi mengabaikan sebagian bulan yang berakhir pada pertengahan minggu. Misalnya, pekan yang berakhir pada 4 Februari 2017 memiliki 3 hari di bulan Januari. Untuk menambahkan total hari ini, kita perlu membuat formula SUMPRODUCT() lainnya…

Ketik rumus berikut di sel E3 dan tekan ENTER

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))*(7-$C$2:$C$54)/7*($B$2:$B$54))

Ini akan terlihat seperti di bawah ini

Logika yang sama sedang bekerja di sini seperti pada rumus sebelumnya dengan beberapa penyesuaian kecil. Alih-alih membandingkan tanggal di  Kolom A dengan bulan saat ini di tabel kalender bulanan, rumus membandingkannya dengan bulan berikutnya di tabel kalender.

(DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))

Satu-satunya perbedaan adalah +1 kecil ditambahkan ke bagian MONTH() dari rumus DATE(), disorot di sini dalam merah .

Rumus SUMPRODUCT() lainnya juga telah berubah

(7-$C$2:$C$54)/7*($B$2:$B$54)

Alih-alih menggunakan penolong  Kolom C sebagai pecahan, kita mengambil pecahan kebalikannya. Karena 7 adalah penyebut kita, kita mulai dengan 7 dan kurangi jumlah hari di bulan (berikutnya) untuk mendapatkan sisanya. Pecahan minggu itu dikalikan dengan total data mingguan dan kemudian ditambahkan ke total SUMPRODUCT() kita.

Menggabungkan Rumus Total Bulanan

Untungnya, kedua fungsi SUMPRODUCT() ini dapat ditambahkan bersama dalam satu sel untuk membuat angka total bulanan untuk setiap kolom kalender bulanan. Sesederhana menempelkan rumus bersama dengan + di tengah

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1),1))*$C$2:$C$54/7*($B$2:$B$54)) +
SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))*(7-$C$2:$C$54)/7*($B$2:$B$54))

Ini akan terlihat seperti di bawah ini ketika selesai

Anda dapat menyeret ketiga rumus ke kanan untuk menyalinnya ke setiap kolom tabel kalender bulanan. Pada akhirnya, Anda harus memiliki jumlah total yang sama di semua bulan Anda seperti yang Anda miliki di kumpulan data mingguan asli Anda. Anda dapat menghapus dua baris pertama kalender jika diinginkan, hanya menyisakan jumlah total bulanan gabungan

Unduh File Contoh

Contoh data yang digunakan di sini, serta semua rumus yang dibahas tersedia untuk diunduh di bawah ini. Anda dapat bereksperimen dengan contoh data yang disematkan, atau mengunduh seluruh spreadsheet Excel dengan mengeklik unduhan di kanan bawah

Dapatkan tips dan trik Excel terbaru dengan bergabung dalam buletin

Bergabunglah dengan buletin untuk tetap mengikuti artikel terbaru. Daftar dan Anda akan mendapatkan panduan gratis dengan 10 pintasan keyboard hemat waktu

Bagaimana cara mendapatkan data mingguan dari data harian di Excel?

Bagaimana cara meringkas data mingguan di Excel?

Bagaimana cara mengonversi mingguan menjadi bulanan di Excel?

Bagaimana cara mengelompokkan data harian ke dalam Minggu di Excel?