Tip Excel – Salin Rumus VLOOKUP Di Beberapa Kolom

Ini dapat benar-benar membuat frustrasi jika Anda telah menyiapkan rumus VLOOKUP di Excel, rumus Anda bekerja seperti yang diharapkan, mencari kolom nilai pertama, lalu Anda harus menyesuaikan rumus secara manual jika Anda ingin menyalinnya di beberapa kolom.

Saya ingin mencari Penjualan bulanan dari dua jenis topi Beanie dalam total data penjualan saya. Jadi, saya telah menyiapkan VLOOKUP untuk mencari volume bulanan Beanie topi Beanie_JL dan Style Beanie_JP.

VLOOKUP biasa bekerja dengan sangat baik mulai dari kolom pertama saya yang mengembalikan nilainya dari Januari 2015, tetapi jika saya menyeret formula yang tepat untuk melanjutkan dengan bulan berikutnya dari Februari 2015 dan seterusnya maka saya tidak mendapatkan hasil yang diinginkan.

Bahkan dengan menggunakan referensi absolut dalam rumus saya, COLUMN INDEX NUMBER tidak bergerak ketika saya menyeret formula. Ini adalah tempat sebagian besar pengguna akan secara manual menyesuaikan ini untuk mendapatkan hasil yang mereka butuhkan. Namun tidak perlu melakukan ini karena kami dapat meminta bantuan formula lain bersama dengan VLOOKUP. Mari gunakan rumus COLUMN untuk membantu kami.

Fungsi COLUMN sangat mudah. Ini menerjemahkan nomor kolom menjadi alamat sel. Misalnya C1 akan mengembalikan 3 karena C adalah kolom ketiga di lembar kerja kami, jadi Anda mungkin bisa melihat bagaimana kami dapat menggunakan ini dalam rumus VLOOKUP.

Mari kita bahas sintaks rumus VLOOKUP

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Nilai lookup. Nilai yang ingin Anda cari. Nilai yang ingin Anda cari harus berada di kolom pertama rentang sel yang Anda tentukantable_array

The table_array. Ini adalah rentang sel di mana VLOOKUP akan mencari nilai lookup dan nilai kembali.

Col_index_num. Ini adalah nomor kolom (dimulai dengan 1 untuk kolom paling kiri dari tabel-array) yang berisi nilai kembalian.

Range_Lookup.

Nilai logis yang menentukan apakah Anda mau VLOOKUP untuk menemukan kecocokan tepat atau kecocokan perkiraan:

  • BENAR mengasumsikan kolom pertama dalam tabel diurutkan secara numerik atau alfabet, dan kemudian mencari nilai terdekat. Ini adalah metode standar jika Anda tidak menentukannya.
  • SALAH mencari nilai yang tepat di kolom pertama

NOMOR INDEKS KOLOM pertama di VLOOKUP yang ingin saya kembalikan adalah di kolom KEDUA dari kumpulan data kami, jadi daripada menggunakan 2 sebagai NOMOR INDEKS KOLOM maka saya dapat menggantinya dengan B1. Rumusnya terlihat seperti ini

= VLOOKUP ($ L $ 7, $ B $ 3: $ J $ 25, COLUMN (B1), FALSE)

Setelah saya melakukan ini, formula akan secara otomatis diperbarui saat diseret.

Tip Rumus Excel – Hitung Berapa Banyak Hari Sampai Akhir Kampanye Penjualan Menggunakan IF dan DATEDIF

Ini adalah pertanyaan yang dikirimkan kepada saya oleh salah satu pengunjung situs web saya. Bagaimana saya bisa menghitung berapa hari sampai akhir kampanye?

Cukup sederhana untuk memulai karena kita dapat menghitung perbedaan antara tanggal hari ini dan akhir dari kampanye.

Tetapi bagaimana jika kampanye belum dimulai … ah hah, saya merasa formula IF mendekat … dan mari kita mencampurnya dengan rumus DATEDIF.

Logikanya secara singkat adalah-

Jika tanggal mulai kampanye setelah hari ini maka hitung jumlah hari antara awal dan tanggal akhir tetapi jika tanggal mulai kampanye sebelum tanggal hari ini, maka hitung jumlah hari antara hari ini dan akhir kampanye.

Jadi, mari kita gunakan Excel untuk mengatasinya menggunakan IF dan DATEDIF menggunakan contoh.

Bulan Biru

Tanggal Mulai 27/02/2015

Tanggal Akhir 01/04/2015

Hari yang tersisa dalam Kampanye 31

Karpet Kuning

Mulai Tanggal 01/04/2015

Tanggal Akhir 01/08/2015

Hari yang tersisa dalam Kampanye 122

Saya memiliki beberapa kampanye contoh saya di atas, dengan menggunakan fungsi IF Saya menguji dua kondisi yang benar dan salah. Dalam contoh ini saya ingin menguji apakah awal dari kampanye adalah setelah hari ini

= IF (START DATE> TODAY () – ini adalah bagian pertama dari rumus

Bagian kedua menampilkan perbedaan hari antara tanggal awal dan akhir kampanye. JIKA kampanye belum dimulai I.e jika kondisi di bagian pertama dari rumus adalah TRUE.

DATEDIF (START DATE, END DATE ″ D ")

Bagian ketiga dari rumus akan menampilkan perbedaan dalam hari antara hari ini dan akhir dari kampanye jika tanggal mulai kampanye tidak setelah hari ini I.e jika kondisi ini SALAH. Bagian DATEDIF dari rumus hanya menghitung jumlah hari menggunakan satuan waktu "D" dari satu tanggal ke tanggal lainnya.

DATEDIF (HARI INI (), TANGGAL AKHIR, "D"))

Jadi dalam kampanye saya di atas

  • Blue Moon dimulai hari ini sehingga perbedaan antara tanggal mulai dan akhir adalah 33 hari

  • Karpet Kuning tidak dimulai sampai 01/04/2015 dan berakhir pada 01/08/2015 sehingga akan berjalan selama 122 hari

  • Rumusnya sama sekali terlihat seperti ini

= IF (MULAI DATE> HARI INI (DATEDIF (MULAI TANGGAL, AKHIR TANGGAL, "D"), DATEDIF) HARI INI (), AKHIR TANGGAL, "D"))

Fungsi DATEDIF tidak ada ketika Anda menggunakan Fungsi Insert di Excel, atau Anda mencarinya secara manual dengan mengetik DATED secara langsung ke dalam sel. Excel tidak terlihat seperti itu mendukungnya tetapi tidak. Ini benar-benar harus ada di toolkit Excel Anda.