Perbarui daftar excel secara dinamis tanpa menggunakan skrip
Saya agak mandek dan butuh bantuan.
Saya mencoba untuk membuat daftar di excel diperbarui secara dinamis, berdasarkan kondisi di sel lain. Sesuatu di sepanjang garis (JIKA kedua nilai sel di A1 dan A2 SESUAI dengan nilai sel di A3 dan A4, kembalikan A5). Idealnya saya hanya ingin menggunakan rumus dan tidak ada skrip, tetapi tidak yakin apakah itu memungkinkan.
Berikut skenarionya. Gina (A3) memiliki keranjang belanja dengan 6 item di dalamnya (B3: B8): Lemon, Pasta Gigi, Brownie, Sikat Rambut, Anggur, Sandwich -
Keranjang Gina
![](https://post.nghiatu.com/assets/images/s/sOK3M.png)
Gina dan teman-temannya suka bertukar barang satu sama lain, dan ketika mereka melakukannya mereka membuat catatan untuk setiap perdagangan (D3: F5) -
catatan perdagangan
![](https://post.nghiatu.com/assets/images/s/XLOTU.png)
Apa yang saya coba lakukan adalah mencari cara untuk memperbarui keranjang belanja Gina setiap kali dia mencatat perdagangan baru.
Dan sebagai tambahan, perlu memungkinkan untuk memperdagangkan item yang sama dua kali (untuk item yang berbeda pada setiap kesempatan) yang dihitung hanya untuk entri terbaru yang diperdagangkan untuk item itu - dalam hal ini dia menerima jeruk di keranjangnya -
dalam hal ini dia menerima jeruk di keranjangnya bukan, bukan cupcake
![](https://post.nghiatu.com/assets/images/s/2e1i4.png)
Saya telah mencoba berbagai kombinasi IF, AND, MATCH, dan INDEX dengan kolom kedua duplikat, atau terjebak dalam dependensi melingkar, dan saya tidak bisa sampai ke dasarnya.
Berikut tautan ke lembar kerja: https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing
Jika ada yang punya ide tentang bagaimana menyelesaikan ini, saya akan sangat berterima kasih!
Terima kasih Jimmy
Jawaban
:: Peringatan ::
Karena OP tidak membutuhkan solusi berbasis VBA MACRO, jadi dalam situasi ini kemungkinan yang saya temukan adalah menggunakan, beberapa rumus ARRAY (CSE) & Pemformatan Bersyarat. Mungkin, yang lain mungkin menyarankan yang lebih baik.
![](https://post.nghiatu.com/assets/images/s/I6m8n.png)
Bagaimana itu bekerja:
- Item yang Diperdagangkan dan Daftar Grocer adalah TABEL.
Segera setelah Anda menambah atau menghapus Nama atau Item dari TABEL, Excel akan secara otomatis memperbarui data terkait.
Selain Keranjang Belanja & Barang yang Diperdagangkan , Anda juga perlu membuat Daftar Grocer .
Segera setelah seseorang membeli Item apa pun, Anda perlu MEMPERBARUI Tabel Grocer .
Periksa daftar Item Lama, dibeli oleh GINA dan lainnya.
- Buat satu daftar drop-down di sel P17, saat ini hanya memiliki 3 nama, Anda dapat menambahkan lebih banyak.
Jika Anda memiliki daftar pembeli yang besar, untuk mendapatkan daftar Unik Anda dapat menggunakan salah satu rumus.
Rumus array (CSE) di sel P28:
{=IFERROR(INDEX($U$17:$U$30, MATCH(0, INDEX(COUNTIF($P$27:P27, $U$17:$U$30)+($U$17:$U$30=""), ), 0)), "")}
Untuk versi yang lebih tinggi, Anda dapat menggunakan yang ini di P28:
=UNIQUE(FILTER(U17:U30,U175:U30<>""))
Kemudian gunakan Daftar pembeli penggunaan unik untuk Drop down di sel P17.
Sekarang gunakan rumus array (CSE) ini di sel Q17:
{=IFERROR(INDEX($T$17:$T$30, SMALL(IF(COUNTIF($P$17, $U$17:$U$30)*COUNTIF($U$17:$U$30,"<>"), ROW($T$17:$U$30)-MIN(ROW($T$17:$U$30))+1), ROW(A1)), COLUMN(A1)),"")}
Rumus array (CSE) di sel R17:
{=IFERROR(INDEX($N$17:$N$22, SMALL(IF(COUNTIF($P$17, $L$17:$L$22)*COUNTIF($N$17:$N$22,"<>"), ROW($L$17:$N$22)-MIN(ROW($L$17:$N$22))+1), ROW(A1)), COLUMN(A1)),"")}
Terapkan pemformatan bersyarat, menggunakan rumus yang ditunjukkan di bawah ini sebagai Aturan Baru, pada P17: P25:
=COUNTIF($N$17:$N$22,Q17)=0
NB
Selesaikan rumus array (CSE) dengan Ctrl + Shift + Enter & isi ke arah yang diperlukan.
Daftar Item Baru menunjukkan item yang ditukar (Diperdagangkan).
Daftar lama memiliki item dalam warna MERAH, adalah item yang tidak ditukar, karena Anda dapat menemukan GINA membeli LIMA item (periksa daftar Pelanggan di Kolom U), dan hanya ditukar DUA, adalah Avocado & Cigars.
Terus ubah Nama di sel P17, Anda mendapatkan hasil.
Sesuaikan referensi sel dalam rumus sesuai kebutuhan.