pembagian sama rata

  1. 6 bulan yang lalu

    Dear Master Excel semua

    mau minta bantuan nya nih untuk kasus (dengan narasi berikut ini). Ada 3 toko yang mempunyai Avg Sales dan Stock yang berbeda beda. ada yang over stock dan ada yang under stock. yang ingin saya tanyakan bagaimana cara meratakan stock dari masing masing toko tersebut sesuai dengan AVG sales yang ada dari masing masing toko juga (contoh file saya kirimkan) mohon bantuannya dari master2 semua

    terima kasih

  2. Caton

    Apr 9 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 6 bulan yang lalu oleh Caton

    @alfri...

    Pada sel P12, kolom STOCK untuk TOKO 2, nilainya adalah 1. Sedangkan nilai kolom AVG SALES-nya adalah 2. Apakah ada kondisi tertentu yang membuat nilainya seperti demikian, atau hanya kesalahan pengisian contoh saja? Jika hal itu terjadi karena kesalahan pengisian, mungkin contoh formula pada file terlampir dapat diterapkan (tabel pada range C14:V17). Jika tidak demikian, mungkin bisa dijelaskan bagaimana kondisi atau kriteria kalkulasinya yang lebih tepat.

    Demikian.

  3. Dear Master @Caton

    untuk barang B sudah saya revisi contoh kasus nya, untuk case barang A mantab...excellence. mohon pencerahannya kembali

  4. Caton

    Apr 9 Terverifikasi Indonesia + 11.522 Poin

    @alfri ...

    ... bagaimana cara memindahkan stock yang ada di toko 1, 2 dan 4 ke toko 3. agar stock toko 3 full (sama dengan nilai avg sales). untuk case ini kenapa saya pindahkan stock dari toko lain ke toko 3 karena AVG sales di toko 3 paling besar dibandingkan dengan AVG Sales 3 toko lainnya ...

    Pada file terlampir, saya berikan 3 contoh dimana formula pada :

    — Contoh 1 bertujuan untuk mencari nilai tertinggi dari kolom AVG SALES (kolom C, kolom E, kolom G dan kolom I). Berdasarkan nilai tertinggi tersebut, kemudian ditentukan indeks kolom STOCK-nya. Indeks kolom yang di dapat kemudian dijadikan kolom untuk menjumlahkan seluruh nilai STOCK, sementara indeks kolom STOCK lainnya dikosongkan atau dibuat menjadi 0 (nol). Kelemahan formula ini adalah jika terdapat nilai yang sama (duplikat) karena formula pada dasarnya tidak dapat menentukan kolom mana yang akan digunakan. Dalam hal ini, untuk mengatasi nilai AVG SALES yang sama, saya gunakan fungsi MAX untuk mendapatkan indeks kolom tertinggi terakhir.

    — Contoh 2 menggunakan kolom bantu IDX (kolom M) sebagai rujukan untuk menentukan indeks kolom mana yang akan digunakan sebagai kolom penjumlahan nilai STOCK.

    — Contoh 3 merupakan gabungan formula pertama (BARANG A), formula pada Contoh 1 dan Contoh 2. Untuk menentukan formula mana yang akan digunakan, masukkan nilai 1, 2 atau 3 pada kolom MODE (kolom M). Jika menggunakan MODE 3, maka kolom IDX (kolom Y) harus diisi dengan indeks kolom yang akan dijadikan kolom penjumlahan.

    And please don't call me master... ;)

    Demikian, semoga sesuai dan bermanfaat.

  5. waw . . . ruarrr biasa . . .

    untuk contohya saya lebih suka untuk contoh yang pertama. oiya mas @caton, ada yang ingin saya tanyakan lagi (nanya mulu yah...#maapin) bagaimana jika..." setelah menentukan AVG sales yang paling tinggi, kemudian ditentukan indeks kolom STOCK-nya. Indeks kolom yang di dapat kemudian dijadikan kolom untuk menjumlahkan seluruh nilai STOCK, sementara indeks kolom STOCK lainnya dikosongkan atau dibuat menjadi 0 (nol)". bagaimana jika indeks kolom stock lainnya jangan dikosongkan/jangan dibuat menjadi 0 (nol). Contoh :

    before : Toko 1 : AVG Sales = 10, Stock 2
    Toko 2 : AVG sales = 0, Stock 9

    After : Toko 1 : AVG Sales = 10, Stock 10
    Toko 2 : AVG sales = 0, Stock 1 ----> sisa dari 9 - 8 (8 pcs yang di transfer ke toko 1)

    demikian pertanyaan lanjutannya, terima kasih yang sebesar besarnya untuk @Caton atau yang lainnya atas jawabannya :)

  6. Caton

    Apr 10 Terverifikasi Indonesia + 11.522 Poin

    @alfri...

    Kalau secara langsung dimasukkan ke dalam formula, sepertinya sulit, jika sifatnya variatif begitu. Formula hanyalah sekedar barisan fungsi yang tidak tahu apapun mengenai keinginan pengguna. Contohnya, bagaimana formula akan menentukan kondisi STOCK untuk TOKO1 tetap, STOCK untuk TOKO2 dikurangi 8? Apa kriterianya? Jika ada kondisi minimum yang harus dipenuhi, misalkan STOCK minimum TOKO1 adalah 10, STOCK minimum TOKO2 adalah 5 dan sebagainya, mungkin saja hal tersebut dapat diterjemahkan ke dalam bentuk formula meskipun formulanya menjadi panjang.

    Solusi lainnya, Anda bisa mencoba menggunakan kolom bantu yang berisi nilai STOCK minimum yang harus disisakan. Contohnya, pada kolom X diisi nilai 10 untuk STOCK minimum TOKO1, kolom Y diisi STOCK minimum TOKO2 dan seterusnya. Dan buatkan 1 kolom untuk menjumlahkan seluruh STOCK minimum tersebut. Kolom terakhir akan digunakan untuk mengurangi total STOCK yang sudah digabungkan dikurangi dengan STOCK minimum yang harus disediakan. Contoh terlampir.

    Demikian.

  7. hallo mas @Caton

    terima kasih banyak atas jawaban jawabanya yang sangat luar biasa. mau nanya lagi :D
    bisa di narasikan kah syntax / formula yang digunakan untuk case diatas. misal : if(mod(coloumns($C8:D8);2)....--> formula ini maksudnya apa . .
    hehehehehe. sebagai bahan pembelajaran saya, jika ketemu case yang lain mungkin formula nya bisa digunakan

    terima kasih . . :D

  8. 5 bulan yang lalu

    Caton

    Apr 12 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 5 bulan yang lalu oleh Caton

    @alfri ...

    Saya coba jelaskan formula pada sel N20 di bawah ini. Formulanya adalah sebagai berikut:

    =IF(MOD(COLUMNS($C20:C20);2);INDEX($C20:C20;COLUMN()-COLUMN($M20));IF((COLUMN()-COLUMN($M20))=SUMPRODUCT(
    MAX(($C$19:$J$19="AVG SALES")*($C20:$J20=SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*$C20:$J20)))*
    (COLUMN($C$20:$J$20)-2))+1);SUMPRODUCT(($C$19:$J$19="STOCK")*$C20:$J20)-$AC20;INDEX($Y20:$AC20;(COLUMN()-COLUMN($M20))/2)))

    Fungsi IF pada formula di atas digunakan sebagai penentu formula mana selanjutnya yang akan dieksekusi, dimana alur prosesnya lebih kurang sebagai berikut:

    [1]. KOLOM GANJIL ATAU GENAP — Periksa apakah sel berada pada kolom ganjil atau kolom genap. Kolom ganjil adalah kolom yang berada di bawah judul AVG SALES, dan kolom genap adalah kolom yang berada di bawah judul STOCK. Formulanya:

    =IF(MOD(COLUMNS($C20:C20);2); ...

    Notasi COLUMNS($C20:C20) akan mengembalikan nilai berupa jumlah kolom mulai dari kolom C baris ke-n (dalam contoh ini baris ke-20). Perhatikan, notasi sel awalnya dibuat menjadi kolom absolut, sehingga saat formula disalin ke kolom di sebelah kanannya, posisi sel awal tersebut akan tetap sama dan alhasil jumlah kolom akan bertambah. Sehingga notasi COLUMNS($C20:C20) akan bernilai 1, notasi COLUMNS($C20:C21) akan bernilai 2 dan seterusnya.

    Oleh karena notasi COLUMNS($C20:C20) akan berisi indeks kolom, maka untuk mendapatkan nilai ganjil atau genapnya, digunakan fungsi MOD dengan nilai pembagi 2 sehingga akan di dapat nilai 1 untuk indeks kolom ganjil dan 0 untuk indeks kolom genap. Nilai 1 akan ditranslasikan sebagai TRUE dan nilai 0 ditranslasikan sebagai FALSE.

    [2]. KOLOM GANJIL — Oleh karena saat berada pada kolom ganjil (AVG SALES), sel hanya akan berisi nilai aktual dari kolom datanya, maka yang diperlukan adalah mengambil nilai data pada baris yang sama pada indeks kolom yang sama. Jika diformulasikan akan menjadi:

    ... INDEX($C20:C20;COLUMN()-COLUMN($M20)) ... 

    Notasi COLUMN()-COLUMN($M20) pada formula di atas akan menghasilkan nilai berurutan yang akan digunakan sebagai indeks baris pada fungsi INDEX. Oleh karena fungsi INDEX tersebut hanya akan tereksekusi saat notasi MOD(COLUMNS($C20:C20);2) menghasilkan nilai 1 (TRUE), maka notasi COLUMN()-COLUMN($M20) hanya akan berisi nilai ganjil yakni 1, 3, 5 dan seterusnya sesuai kolom formulanya. Pada formula tersebut, array nilai fungsi INDEX akan terisi secara dinamis dikarenakan sel terakhir tidak bersifat absolut. Ilustrasinya:

    N20 = INDEX($C20:C20;COLUMN()-COLUMN($M20)) = INDEX({10};1) = 10
    P20 = INDEX($C20:E20;COLUMN()-COLUMN($M20)) = INDEX({10\1\30};3) = 30
    R20 = INDEX($C20:G20;COLUMN()-COLUMN($M20)) = INDEX({10\1\30\5\20};5) = 20
    T20 = INDEX($C20:I20;COLUMN()-COLUMN($M20)) = INDEX({10\1\30\5\20\12\15};7) = 15

    [3]. KOLOM GENAP — Jika sel dimana formula berada merupakan kolom genap, maka ada beberapa tahap proses, yakni:

    — 1) Tentukan nilai AVG SALES tertinggi pada baris data terkait:

    ... MAX(($C$19:$J$19="AVG SALES")*$C20:$J20) ...

    — 2) Bandingkan kembali data pada kolom C sampai dengan kolom J tersebut dengan nilai tertinggi pada tahap pertama:

    ... ($C20:$J20=SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*$C20:$J20))) ...

    — 3) Pada tahap di atas, untuk setiap nilai pada range C20:J20 yang sama dengan nilai tertinggi pada range tersebut akan menghasilkan TRUE, selain itu FALSE. Karena yang dibutuhkan adalah posisi kolom STOCK dari nilai AVG SALES tertinggi, maka ambil indeks kolomnya:

    ...
    ($C$19:$J$19="AVG SALES")*
    ($C20:$J20=SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*$C20:$J20)))*
    (COLUMN($C$20:$J$20)-2)
    ...

    — 4) Apabila ada 2 atau lebih nilai tertinggi AVG SALES yang sama, maka ambil tertinggi terakhir. Hal ini dimungkinkan karena hasil pada tahap 3 di atas bukan berupa nilai AVG SALES, namun sudah berupa nilai indeks kolom yang memiliki nilai tertinggi:

    ...
    SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*
    ($C20:$J20=SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*$C20:$J20)))*
    (COLUMN($C$20:$J$20)-2))+1
    ...

    Oleh karena range data yang dirujuk ke dalam fungsi MAX pada formula tersebut berupa array, maka digunakan fungsi SUMPRODUCT untuk mendapatkan nilai akhir dari fungsi MAX. Sedangkan notasi +1 diakhir formula tersebut tujuannya adalah menambah indeks kolom yang dihasilkan 1 kolom ke kanan. Ilustrasinya:

    SUMPRODUCT(MAX({0\0\3\0\0\0\0\0}) + 1 = SUMPRODUCT(3) + 1 = 4

    — 5) Bandingkan apakah indeks kolom sel dimana formula berada sama dengan indeks kolom yang di dapat dari tahap ke 4 di atas:

    ...
    (COLUMN()-COLUMN($M20))=
    SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*
    ($C20:$J20=SUMPRODUCT(MAX(($C$19:$J$19="AVG SALES")*$C20:$J20)))*
    (COLUMN($C$20:$J$20)-2))+1
    ...

    — 6) Hasil pengujian pada tahap 5 di atas akan menentukan formula mana yang akan dieksekusi selanjutnya, dimana jika kondisi tahap 5 adalah TRUE, maka formula yang dieksekusi adalah:

    SUMPRODUCT(($C$19:$J$19="STOCK")*$C20:$J20)-$AC20

    sedangkan jika kondisi tahap 5 adalah FALSE, maka formula yang dieksekusi adalah:

    INDEX($Y20:$AC20;(COLUMN()-COLUMN($M20))/2)

    Untuk kedua formula terakhir saya rasa tidak terlalu sulit untuk dipahami. Itu saja yang dapat saya jelaskan, semoga penjelasan di atas dapat dipahami dan tidak membuat bingung. Dan semoga bermanfaat.

    Demikian.

 

atau Mendaftar untuk ikut berdiskusi!