fungsi logika perbedaan single if dan multi if pada aplikasi excel

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel fungsi excel, Artikel Rumus Excel, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : fungsi logika perbedaan single if dan multi if pada aplikasi excel
link : fungsi logika perbedaan single if dan multi if pada aplikasi excel

Baca juga


January 2017

fungsi logika perbedaan single if dan multi if pada aplikasi excel


dalam artikel sebelumnya yaitu Fungsi Logika IF digabung dengan And Or Not sudah dibahas sedikit tentang cara penggunaannya KLIK DISINI. akan tetapi fungsi logika if sebenarnya dibagi menjadi dua yaitu single if dan multi if.


1. SINGLE IF
single if berfungsi untuk menguji kebenaran suatu kondisi yang logikanya hanya satu.
belajarmicrosoftexceloffice.blogspot.co.id
Gambar contoh penggunaan single if
gambar diatas merupakan contoh penggunaan single if. dilihat dari penggunaan rumusnya jumlah if yang digunakan disini hanya satu. dengan ketentuan tooltips sebagai berikut :
  • logical_test = D3<60 (logika yang digunakan disini harus kurang dari 60)
  • values_if_true = "TIDAK LULUS" (jika logikanya sesuai dengan ketentuan maka akan diberikan hasil LULUS)
  • values_if_false = "LULUS" (jika logikanya tidak sesuai ketentuan maka akan diberikan hasil TIDAK LULUS)



2. MULTI IF
sedangkan multi if berfungsi untuk menguji kebenaran suatu kondisi yang logikanya lebih dari satu.
belajarmicrosoftexceloffice.blogspot.co.id
Gambar contoh penggunaan multi if
gambar diatas merupakan contoh penggunaan multi if. dilihat dari penggunaan rumusnya jumlah if yang digunakan disini lebih dari satu. dengan ketentuan tooltips sebagai berikut :
  • logical_test = ada tiga logical test mulai dari D3<60, D3<80, D3<90 yang artinya logika IF lebih dari satu.
  • values_if_true = ada tiga juga sesuai logical test yaitu hurup D, C, B (jika logikanya sesuai dengan ketentuan maka akan diberikan hurup sesuai ketentuan logikanya)
  • values_if_false = untuk falsenya hanya ada satu yaitu A (jika logikanya tidak sesuai ketentuan maka akan diberikan hasil A yang tidak mempunyai logika atau false)


sebetulnya multi if merupakan gabungan dari beberapa single if. dilihat dari penggunaan rumusnya, jumlah if yang digunakan lebih dari satu. artinya jika ada logika if yang tidak sesuai dengan ketentuan logika maka prosesnya akan berlanjut kepada fungsi if berikutnya sampai fungsi if terakhir. contoh gambar diatas merupakan contoh penggunaan multi if untuk menghasilkan NILAI HURUP sesuai RATA2 yang akan diproses sesuai logika if yang dibuat.

selamat mencoba penggunaan fungsi logika single if dan multi if, begitu mudah bukan belajar aplikasi excel.. :) jika ada hal-hal yang kurang dipahami atau kurang dimengerti, silahkan berikan komentar dalam artikel ini. begitu mudah belajar aplikasi microsoft excel. indahnya berbagi pengetahuan.

fungsi logika perbedaan single if dan multi if pada aplikasi excel


dalam artikel sebelumnya yaitu Fungsi Logika IF digabung dengan And Or Not sudah dibahas sedikit tentang cara penggunaannya KLIK DISINI. akan tetapi fungsi logika if sebenarnya dibagi menjadi dua yaitu single if dan multi if.


1. SINGLE IF
single if berfungsi untuk menguji kebenaran suatu kondisi yang logikanya hanya satu.
belajarmicrosoftexceloffice.blogspot.co.id
Gambar contoh penggunaan single if
gambar diatas merupakan contoh penggunaan single if. dilihat dari penggunaan rumusnya jumlah if yang digunakan disini hanya satu. dengan ketentuan tooltips sebagai berikut :
  • logical_test = D3<60 (logika yang digunakan disini harus kurang dari 60)
  • values_if_true = "TIDAK LULUS" (jika logikanya sesuai dengan ketentuan maka akan diberikan hasil LULUS)
  • values_if_false = "LULUS" (jika logikanya tidak sesuai ketentuan maka akan diberikan hasil TIDAK LULUS)



2. MULTI IF
sedangkan multi if berfungsi untuk menguji kebenaran suatu kondisi yang logikanya lebih dari satu.
belajarmicrosoftexceloffice.blogspot.co.id
Gambar contoh penggunaan multi if
gambar diatas merupakan contoh penggunaan multi if. dilihat dari penggunaan rumusnya jumlah if yang digunakan disini lebih dari satu. dengan ketentuan tooltips sebagai berikut :
  • logical_test = ada tiga logical test mulai dari D3<60, D3<80, D3<90 yang artinya logika IF lebih dari satu.
  • values_if_true = ada tiga juga sesuai logical test yaitu hurup D, C, B (jika logikanya sesuai dengan ketentuan maka akan diberikan hurup sesuai ketentuan logikanya)
  • values_if_false = untuk falsenya hanya ada satu yaitu A (jika logikanya tidak sesuai ketentuan maka akan diberikan hasil A yang tidak mempunyai logika atau false)


sebetulnya multi if merupakan gabungan dari beberapa single if. dilihat dari penggunaan rumusnya, jumlah if yang digunakan lebih dari satu. artinya jika ada logika if yang tidak sesuai dengan ketentuan logika maka prosesnya akan berlanjut kepada fungsi if berikutnya sampai fungsi if terakhir. contoh gambar diatas merupakan contoh penggunaan multi if untuk menghasilkan NILAI HURUP sesuai RATA2 yang akan diproses sesuai logika if yang dibuat.

selamat mencoba penggunaan fungsi logika single if dan multi if, begitu mudah bukan belajar aplikasi excel.. :) jika ada hal-hal yang kurang dipahami atau kurang dimengerti, silahkan berikan komentar dalam artikel ini. begitu mudah belajar aplikasi microsoft excel. indahnya berbagi pengetahuan.

Diposting oleh

Rumus Konversi Huruf Kolom Menjadi Angka

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel COLUMN, Artikel Formula, Artikel Fungsi, Artikel INDIRECT, Artikel Rumus, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : Rumus Konversi Huruf Kolom Menjadi Angka
link : Rumus Konversi Huruf Kolom Menjadi Angka

Baca juga


January 2017

Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet.

Formula Koversi Huruf Kolom Menjadi NomorContohnya :

  • A menjadi 1
  • B menjadi 2
  • C menjadi 3
  • Z menjadi 26
  • AA menjadi 27
  • … dan seterusnya.


Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT

Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka


Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya.

A
B
C
2
Contoh Rumus
Penjelasan
3
=COLUMN(INDIRECT("B1"))
Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2
4
=COLUMN(INDIRECT(B9&1))
Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27
5
=COLUMN(INDIRECT("XYZ1"))
Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
6
=COLUMN(INDIRECT(B10&1))
Mencoba mengkonversi huruf yang ada pada sel B10 yaitu ZZZ menjadi nomor. Menghasilkan error #REF! karena ZZZ melebihi batas maksimum kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
7


8
Test Ref:

9
AA

10
ZZZ



Penjelasan Cara Kerja Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka (Nomor Urut Kolom)


Pertama :

Fungsi INDIRECT digunakan untuk mendapatkan referensi sel sesuai text referensi yang diberikan.

Misalnya jika text yang diumpankan adalah "B1", maka INDIRECT akan mengarah ke sel referensi B1.

Contoh:

  • Rumus INDIRECT("C2") akan mengarahkan ke referensi C2 dan karena sel C2 berisi text "Penjelasan" maka rumus tersebut akan menghasilkan text "Penjelasan"
  • Rumus INDIRECT("B2") akan mengarahkan ke referensi B2 dan karena sel B2 berisi text "Contoh Rumus" maka rumus tersebut akan menghasilkan text "Contoh Rumus"


Kedua:

Fungsi COLUMN berguna untuk mendapatkan nomor urut kolom dari referensi yang diberikan.

Contoh:

  • Rumus COLUMN(B1) akan mendapatkan angka nomor kolom dari sel B1 yaitu 2
  • Rumus COLUMN(A1) akan mendapatkan angka nomor kolom dari sel A1 yaitu 1


Dari penjelasan cara kerja masing-masing fungsi di atas, kemudian dapat dijelaskan alur kerja formula excel untuk mengkonversi huruf kolom menjadi nomor sesuai contoh berikut :

  • Rumus masih lengkap
    • =COLUMN(INDIRECT("B1"))
  • Referensi Text “B1” sudah dirubah menjadi referensi B1 oleh fungsi INDIRECT
    • =COLUMN(B1)
  • Fungsi COLUMN menghasilkan angka 2 yang merupakan nomor urut kolom B 


Hal yang sama berlaku juga jika kita menggunakan referensi sel untuk menempatkan text referensi (perhatikan contoh pada tabel di atas baris ke-4
  • Rumus masih lengkap
    • =COLUMN(INDIRECT(B9&1))
  • Sel B9 berisi text "AA", dan jika digabung dengan angka 1 (rumus B9&1), maka hasilnya "AA1"
    • =COLUMN(INDIRECT("AA1"))
  • Referensi Text “AA1” kemudian dirubah menjadi referensi AA1 oleh fungsi INDIRECT, yang selanjutnya diolah oleh fungsi COLUMN
    • =COLUMN(AA1)
  • Fungsi COLUMN menghasilkan angka 27 yang merupakan nomor urut kolom AA dalam lembar kerja excel.

Perlu diperhatikan: Dalam contoh – contoh formula diatas kita menggunakan index baris 1, misalnya B1, XYZ1. Sebenarnya angka tersebut bisa diubah dengan angka berapa saja sepanjang masih dalam lingkup baris yang dibatasi dalam lembar kerja excel.

Kenapa Error:

Error terjadi karena penggunaan text kolom yang diluar batas jumlah kolom yang disediakan oleh excel.

Dalam contoh diatas jika kita menggunakan kolom XYZ atau ZZZ maka rumus akan menghasilkan nilai error #REF! karena XYZ dan ZZZ tidak tersedia di excel.

Kolom maksimal pada excel versi 2007. 1010, 2013 dan 2016 adalah XFD atau kolom ke-16384. Sedangkan untuk excel versi 2003, kolom maksimalnya adalah kolom IV atau kolom ke-256

Demikian tips singkat mengenai cara membuat rumus excel untuk mengkonversi huruf kolom menjadi angka nomor urut kolom.

Salam..

Baca juga, artikel tutorial belajar excel lainnya:


Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet.

Formula Koversi Huruf Kolom Menjadi NomorContohnya :

  • A menjadi 1
  • B menjadi 2
  • C menjadi 3
  • Z menjadi 26
  • AA menjadi 27
  • … dan seterusnya.


Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT

Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka


Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya.

A
B
C
2
Contoh Rumus
Penjelasan
3
=COLUMN(INDIRECT("B1"))
Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2
4
=COLUMN(INDIRECT(B9&1))
Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27
5
=COLUMN(INDIRECT("XYZ1"))
Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
6
=COLUMN(INDIRECT(B10&1))
Mencoba mengkonversi huruf yang ada pada sel B10 yaitu ZZZ menjadi nomor. Menghasilkan error #REF! karena ZZZ melebihi batas maksimum kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
7


8
Test Ref:

9
AA

10
ZZZ



Penjelasan Cara Kerja Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka (Nomor Urut Kolom)


Pertama :

Fungsi INDIRECT digunakan untuk mendapatkan referensi sel sesuai text referensi yang diberikan.

Misalnya jika text yang diumpankan adalah "B1", maka INDIRECT akan mengarah ke sel referensi B1.

Contoh:

  • Rumus INDIRECT("C2") akan mengarahkan ke referensi C2 dan karena sel C2 berisi text "Penjelasan" maka rumus tersebut akan menghasilkan text "Penjelasan"
  • Rumus INDIRECT("B2") akan mengarahkan ke referensi B2 dan karena sel B2 berisi text "Contoh Rumus" maka rumus tersebut akan menghasilkan text "Contoh Rumus"


Kedua:

Fungsi COLUMN berguna untuk mendapatkan nomor urut kolom dari referensi yang diberikan.

Contoh:

  • Rumus COLUMN(B1) akan mendapatkan angka nomor kolom dari sel B1 yaitu 2
  • Rumus COLUMN(A1) akan mendapatkan angka nomor kolom dari sel A1 yaitu 1


Dari penjelasan cara kerja masing-masing fungsi di atas, kemudian dapat dijelaskan alur kerja formula excel untuk mengkonversi huruf kolom menjadi nomor sesuai contoh berikut :

  • Rumus masih lengkap
    • =COLUMN(INDIRECT("B1"))
  • Referensi Text “B1” sudah dirubah menjadi referensi B1 oleh fungsi INDIRECT
    • =COLUMN(B1)
  • Fungsi COLUMN menghasilkan angka 2 yang merupakan nomor urut kolom B 


Hal yang sama berlaku juga jika kita menggunakan referensi sel untuk menempatkan text referensi (perhatikan contoh pada tabel di atas baris ke-4
  • Rumus masih lengkap
    • =COLUMN(INDIRECT(B9&1))
  • Sel B9 berisi text "AA", dan jika digabung dengan angka 1 (rumus B9&1), maka hasilnya "AA1"
    • =COLUMN(INDIRECT("AA1"))
  • Referensi Text “AA1” kemudian dirubah menjadi referensi AA1 oleh fungsi INDIRECT, yang selanjutnya diolah oleh fungsi COLUMN
    • =COLUMN(AA1)
  • Fungsi COLUMN menghasilkan angka 27 yang merupakan nomor urut kolom AA dalam lembar kerja excel.

Perlu diperhatikan: Dalam contoh – contoh formula diatas kita menggunakan index baris 1, misalnya B1, XYZ1. Sebenarnya angka tersebut bisa diubah dengan angka berapa saja sepanjang masih dalam lingkup baris yang dibatasi dalam lembar kerja excel.

Kenapa Error:

Error terjadi karena penggunaan text kolom yang diluar batas jumlah kolom yang disediakan oleh excel.

Dalam contoh diatas jika kita menggunakan kolom XYZ atau ZZZ maka rumus akan menghasilkan nilai error #REF! karena XYZ dan ZZZ tidak tersedia di excel.

Kolom maksimal pada excel versi 2007. 1010, 2013 dan 2016 adalah XFD atau kolom ke-16384. Sedangkan untuk excel versi 2003, kolom maksimalnya adalah kolom IV atau kolom ke-256

Demikian tips singkat mengenai cara membuat rumus excel untuk mengkonversi huruf kolom menjadi angka nomor urut kolom.

Salam..

Baca juga, artikel tutorial belajar excel lainnya:


Diposting oleh

Belajar Lain-nya

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : Belajar Lain-nya
link : Belajar Lain-nya

Baca juga


January 2017

Berikut link blog saya lainnya dalam membahas beberapa materi yang telah saya pelajari sendiri.

Belajar Dasar Photoshop
http://barubelajar-photoshop.blogspot.co.id/

Berikut link blog saya lainnya dalam membahas beberapa materi yang telah saya pelajari sendiri.

Belajar Dasar Photoshop
http://barubelajar-photoshop.blogspot.co.id/

Diposting oleh

3 Alternatif Rumus Cari Data Banyak Kriteria

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel Array, Artikel Cari Data, Artikel Formula, Artikel Fungsi, Artikel Rumus, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : 3 Alternatif Rumus Cari Data Banyak Kriteria
link : 3 Alternatif Rumus Cari Data Banyak Kriteria

Baca juga


January 2017

rumus cari data banyak kriteria
Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

  1. INDEX MATCH
  2. OFFSET MATCH
  3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel


Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

  • Kolom A = No
  • Kolom B = Buah
  • Kolom C = Supplier
  • Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:


  • Kriteria 1 = nama buah
  • Kriteria 2 = nama suplier


Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

apa rumus untuk cari data dengan banyak kriteria

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.


Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH


Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Rumus INDEX MATCH cari data


Cara Kerja Rumus INDEX MATCH

  • Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
  • Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
  • Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
  • Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
  • Sehingga hasil akhir adalah 8000.



Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.
Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}


Rumus OFFSET MATCH cari data banyak kriteria


Cara Kerja Rumus OFFSET-MATCH

  • Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
  • Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
  • Dan sel dimaksud dengan kriteria tersebut adalah sel D4
  • Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN


Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Contoh Rumus INDIRECT ADDRESS Cari data


Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

  • Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
  • Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
  • Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
  • ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
  • Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
  • Dan hasilnya adalah 8.000
  
Ringkasan.
Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.
Demikian semoga bermanfaat.
Salam

Baca juga artikel Belajar Excel Lainnya:

rumus cari data banyak kriteria
Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

  1. INDEX MATCH
  2. OFFSET MATCH
  3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel


Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

  • Kolom A = No
  • Kolom B = Buah
  • Kolom C = Supplier
  • Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:


  • Kriteria 1 = nama buah
  • Kriteria 2 = nama suplier


Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

apa rumus untuk cari data dengan banyak kriteria

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.


Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH


Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Rumus INDEX MATCH cari data


Cara Kerja Rumus INDEX MATCH

  • Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
  • Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
  • Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
  • Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
  • Sehingga hasil akhir adalah 8000.



Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.
Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}


Rumus OFFSET MATCH cari data banyak kriteria


Cara Kerja Rumus OFFSET-MATCH

  • Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
  • Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
  • Dan sel dimaksud dengan kriteria tersebut adalah sel D4
  • Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN


Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Contoh Rumus INDIRECT ADDRESS Cari data


Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

  • Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
  • Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
  • Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
  • ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
  • Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
  • Dan hasilnya adalah 8.000
  
Ringkasan.
Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.
Demikian semoga bermanfaat.
Salam

Baca juga artikel Belajar Excel Lainnya:

Diposting oleh

5 Alternative Rumus VLOOKUP Ke Kiri

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel Belajar Excel, Artikel Formula, Artikel Fungsi, Artikel Rumus, Artikel VLOOKUP, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : 5 Alternative Rumus VLOOKUP Ke Kiri
link : 5 Alternative Rumus VLOOKUP Ke Kiri

Baca juga


January 2017

Formula VLOOKUP ke Kiri
Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup, terutama jika kita ingin cari data ke sebelah kiri.

Kenapa?... Karena ternyata fungsi ini masih memiliki kelemahan, yaitu hanya dapat mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya, struktur atau susunan kolom dalam tabel tidak selalu bisa diprediksi dan tidak selalu cocok dengan kondisi ideal penggunaan formula VLOOKUP.

Namun demikian tidak perlu khawatir, karena excel sudah menyediakan berbagai alternatif solusi pencarian data. Dan atas dasar hal tersebut juga, maka catatan pelajaran excel  ini dipersiapkan untuk membahas 5 alternative rumus vlookup data di sebelah kiri.

Adapun kelima alternative rumus cari data tersebut adalah:

1. Rumus VLOOKUP CHOOSE
2. Rumus HLOOKUP MATCH
3. Rumus INDEX MATCH
4. Rumus OFFSET MATCH
5. Rumus INDIRECT ADDRESS MATCH  COLUMN

Perlu dicatat, meskipun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga dapat digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri karena memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang baru mengenal atau menggunakan rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri


Anggaplah kita memiliki sebuah tabel berisi data karyawan pada kolom A s.d E, dengan susunan label kolom dari kiri ke kanan: "No", "Nama", "Umur", "ID", dan "Alamat".

Tugas selanjutnya adalah bagaimana mencari nama karyawan berdasarkan nomor ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Studi kasus formula vlookup data di kiri


Dari gambar diatas, dapat dilihat bahwa data yang diketahui (ID) terletak di kolom D, sedangkan data yang di diinginkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?

Mari kita bahas satu persatu...

Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE


Dengan fungsi CHOOSE, ternyata kita bisa mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP. 

Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk menjadikan kolom "ID" sebagai kolom pertama , dan kolom "Nama" sebagai kolom kedua.

Adapun formula pencarian data di sebeah kiri dapat dituliskan sebagai berikut:

=VLOOKUP(H1,CHOOSE({1,2},D:D,B:B),2,0)

Rumus tersebut dapat menghasilkan nama yang dicari seperti ditunjukan dalam gambar  berikut:

VLOOKUP CHOOSE cari data di kiri


Perhatikan kembali fragmen rumus CHOOSE({1,2},D:D,B:B)

  • Dalam hal ini fungsi CHOOSE digunakan untuk menjadikan kolom D (kolom berisi data ID) sebagai kolom pertama dan kolom B (kolom berisi data Nama) sebagai kolom kedua. 
  • Kemudian setelah digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan memberikan hasil data nama  dari kolom ke-2, pada baris yang sama dengan baris sel berisi ID yang diketahui.


Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH


Dengan membalik orientasi vertikal menjadi horizontal, sebenarnya  kita bisa melakukan pencarian data di sebelah kiri. 

=HLOOKUP("Nama",A:E,MATCH(H1,D:D,0),0)


HLOOKUP MATCH cari data di kiri


  • Rumus MATCH(H1,D:D,0) berguna untuk mendapatkan angka baris sebuah sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menghasilkan angka 3 yaitu nomor baris sel yang berisi nilai ID=13043
  • Kemudian rumus HLOOKUP akan mencari posisi kolom "Nama" pada baris pertama  dalam range “A:E”. Selanjutnya memberikan informasi dari baris 3 pada kolom "Nama". Dan hasilnya adalah "Dulah"


Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH


Rumus ini tampaknya merupakan rumus terpopuler alternative VLOOKUP ke  kiri. Terbukti dengan banyaknya tutorial yang membahas hal ini. Coba saja cari kata kunci 'vlookup ke kiri' di Google. Maka kebanyakan link di halaman pertama google akan mengarah ke halaman web/blog yang menjelaskan INDEX MATCH, bukannya menjelaskan VLOOKUP.

Terkecuali jika halaman yang sedang anda baca ini ini bisa muncul di halaman pertama Google (penuh harap… 😇😇 ),  maka mungkin akan menambah warna sendiri mengenai cara pencarian data di sebelah kiri…. 

Baiklah, selanjutnya rumus INDEX MATCH dalam contoh kasus ini dapat dituliskan sebagai berikut:

=INDEX(B:B,MATCH(H1,D:D,0))


INDEX MATCH cara vlookup ke kiri


Fungsi INDEX digunakan untuk mendapatkan nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan. 

Misalnya:

=INDEX(range,x,y)

Maka rumus tersebut akan menghasilkan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga dapat ditiadakan seperti ini:

=INDEX(range,x)

Rumus tersebut akan menghasilkan nilai dari baris ke-x ,kolom ke-1 dalam range.

Konsep terakhir inilah yang digunakan dalam fungsi INDEX-MATCH untuk mencari data di sebelah kiri. Sebenarnya tidak mesti di sebelah kiri sich, mencari data ke kanan pun boleh.

Kembali ke contoh rumus INDEX MATCH untuk mendapatkan nama karyawan sesuai ID yang diketahui.

=INDEX(B:B,MATCH(H1,D:D,0))

  • Fungsi MATCH digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang diketahui (sesuai yang diisi pada sel H1). Dan hasilnya adalah angka 3 (nomor baris sel berisi ID=13043)
  • Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B
  • Hasilnya adalah "Dulah" yaitu data dalam sel yang terletak pada baris 3 kolom B.



Cari Data Ke Kiri Menggunakan Rumus OFFSET  MATCH


Cara kerja rumus ini adalah dengan menggunakan posisi relative baris data yang dicari dari sel acuan dalam fungsi OFFSET. Perhatikan gambar berikut:

OFFSET MATCH cara vlookup ke kiri

  • Fungsi MATCH digunakan untuk mendapatkan angka baris sel yang berisi nilai ID yang diketahui pada range D:D. Hasil dari rumus MATCH ini dikurangi angka 1 untuk menyesuaikan angka posisi relative dari baris pertama (sel B1). Fungsi MATCH dalam contoh menghasilkan bilangan 3, kemudian 3 dikurangi 1 menjadi 2. 
  • Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapatkan nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.
  • Dan hasilnya adalah "Dulah" yaitu data dalam sel yang jaraknya 2 baris ke arah bawah dari sel B1.



Cari Data Ke Kiri Menggunakan Rumus INDIRECT - ADDRESS - MATCH - COLUMN


Nah, kalau rumus ini bekerja dengan cara menentukan alamat sel yang berisi data yang dicari, kemudian mendapatkan nilainya.

INDIRECT ADDRESS cari data kiri


  • Rumus MATCH(H1,D:D,0) menghasilkan angka 3 yaitu posisi baris sel yang berisi ID 13043
  • Rumus COLUMN(B:B) menghasilkan angka 2  yaitu nomur urut kolom B dalam lembar kerja excel
  • Fungsi ADDRESS digunakan untuk mendapatkan alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN.  Sehingga jika diringkas menjadi  =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
  • Fungsi INDIRECT digunakan untuk mendapatkan nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 adalah “Dulah”


Ringkasan:
Ada banyak cara untuk menyelesaikan tugas Excel. Termasuk untuk melakukan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita bisa menggunakan beberapa alternative seperti 5 contoh rumus yang sudah dijelaskan yaitu: kombinasi VLOOKUP – CHOOSE, kombinasi HLOOKUP-MATCH, kombinasi INDEX-MATCH, kombinasi OFFSET-MATCH dan kombinasi INDIRECT-ADDRESS-MATCH-COLUMN.

Demikian semoga bermanfaat.
Salam..


Baca juga tutorial Belajar Excel Lainnya:




Formula VLOOKUP ke Kiri
Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup, terutama jika kita ingin cari data ke sebelah kiri.

Kenapa?... Karena ternyata fungsi ini masih memiliki kelemahan, yaitu hanya dapat mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya, struktur atau susunan kolom dalam tabel tidak selalu bisa diprediksi dan tidak selalu cocok dengan kondisi ideal penggunaan formula VLOOKUP.

Namun demikian tidak perlu khawatir, karena excel sudah menyediakan berbagai alternatif solusi pencarian data. Dan atas dasar hal tersebut juga, maka catatan pelajaran excel  ini dipersiapkan untuk membahas 5 alternative rumus vlookup data di sebelah kiri.

Adapun kelima alternative rumus cari data tersebut adalah:

1. Rumus VLOOKUP CHOOSE
2. Rumus HLOOKUP MATCH
3. Rumus INDEX MATCH
4. Rumus OFFSET MATCH
5. Rumus INDIRECT ADDRESS MATCH  COLUMN

Perlu dicatat, meskipun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga dapat digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri karena memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang baru mengenal atau menggunakan rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri


Anggaplah kita memiliki sebuah tabel berisi data karyawan pada kolom A s.d E, dengan susunan label kolom dari kiri ke kanan: "No", "Nama", "Umur", "ID", dan "Alamat".

Tugas selanjutnya adalah bagaimana mencari nama karyawan berdasarkan nomor ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Studi kasus formula vlookup data di kiri


Dari gambar diatas, dapat dilihat bahwa data yang diketahui (ID) terletak di kolom D, sedangkan data yang di diinginkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?

Mari kita bahas satu persatu...

Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE


Dengan fungsi CHOOSE, ternyata kita bisa mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP. 

Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk menjadikan kolom "ID" sebagai kolom pertama , dan kolom "Nama" sebagai kolom kedua.

Adapun formula pencarian data di sebeah kiri dapat dituliskan sebagai berikut:

=VLOOKUP(H1,CHOOSE({1,2},D:D,B:B),2,0)

Rumus tersebut dapat menghasilkan nama yang dicari seperti ditunjukan dalam gambar  berikut:

VLOOKUP CHOOSE cari data di kiri


Perhatikan kembali fragmen rumus CHOOSE({1,2},D:D,B:B)

  • Dalam hal ini fungsi CHOOSE digunakan untuk menjadikan kolom D (kolom berisi data ID) sebagai kolom pertama dan kolom B (kolom berisi data Nama) sebagai kolom kedua. 
  • Kemudian setelah digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan memberikan hasil data nama  dari kolom ke-2, pada baris yang sama dengan baris sel berisi ID yang diketahui.


Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH


Dengan membalik orientasi vertikal menjadi horizontal, sebenarnya  kita bisa melakukan pencarian data di sebelah kiri. 

=HLOOKUP("Nama",A:E,MATCH(H1,D:D,0),0)


HLOOKUP MATCH cari data di kiri


  • Rumus MATCH(H1,D:D,0) berguna untuk mendapatkan angka baris sebuah sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menghasilkan angka 3 yaitu nomor baris sel yang berisi nilai ID=13043
  • Kemudian rumus HLOOKUP akan mencari posisi kolom "Nama" pada baris pertama  dalam range “A:E”. Selanjutnya memberikan informasi dari baris 3 pada kolom "Nama". Dan hasilnya adalah "Dulah"


Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH


Rumus ini tampaknya merupakan rumus terpopuler alternative VLOOKUP ke  kiri. Terbukti dengan banyaknya tutorial yang membahas hal ini. Coba saja cari kata kunci 'vlookup ke kiri' di Google. Maka kebanyakan link di halaman pertama google akan mengarah ke halaman web/blog yang menjelaskan INDEX MATCH, bukannya menjelaskan VLOOKUP.

Terkecuali jika halaman yang sedang anda baca ini ini bisa muncul di halaman pertama Google (penuh harap… 😇😇 ),  maka mungkin akan menambah warna sendiri mengenai cara pencarian data di sebelah kiri…. 

Baiklah, selanjutnya rumus INDEX MATCH dalam contoh kasus ini dapat dituliskan sebagai berikut:

=INDEX(B:B,MATCH(H1,D:D,0))


INDEX MATCH cara vlookup ke kiri


Fungsi INDEX digunakan untuk mendapatkan nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan. 

Misalnya:

=INDEX(range,x,y)

Maka rumus tersebut akan menghasilkan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga dapat ditiadakan seperti ini:

=INDEX(range,x)

Rumus tersebut akan menghasilkan nilai dari baris ke-x ,kolom ke-1 dalam range.

Konsep terakhir inilah yang digunakan dalam fungsi INDEX-MATCH untuk mencari data di sebelah kiri. Sebenarnya tidak mesti di sebelah kiri sich, mencari data ke kanan pun boleh.

Kembali ke contoh rumus INDEX MATCH untuk mendapatkan nama karyawan sesuai ID yang diketahui.

=INDEX(B:B,MATCH(H1,D:D,0))

  • Fungsi MATCH digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang diketahui (sesuai yang diisi pada sel H1). Dan hasilnya adalah angka 3 (nomor baris sel berisi ID=13043)
  • Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B
  • Hasilnya adalah "Dulah" yaitu data dalam sel yang terletak pada baris 3 kolom B.



Cari Data Ke Kiri Menggunakan Rumus OFFSET  MATCH


Cara kerja rumus ini adalah dengan menggunakan posisi relative baris data yang dicari dari sel acuan dalam fungsi OFFSET. Perhatikan gambar berikut:

OFFSET MATCH cara vlookup ke kiri

  • Fungsi MATCH digunakan untuk mendapatkan angka baris sel yang berisi nilai ID yang diketahui pada range D:D. Hasil dari rumus MATCH ini dikurangi angka 1 untuk menyesuaikan angka posisi relative dari baris pertama (sel B1). Fungsi MATCH dalam contoh menghasilkan bilangan 3, kemudian 3 dikurangi 1 menjadi 2. 
  • Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapatkan nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.
  • Dan hasilnya adalah "Dulah" yaitu data dalam sel yang jaraknya 2 baris ke arah bawah dari sel B1.



Cari Data Ke Kiri Menggunakan Rumus INDIRECT - ADDRESS - MATCH - COLUMN


Nah, kalau rumus ini bekerja dengan cara menentukan alamat sel yang berisi data yang dicari, kemudian mendapatkan nilainya.

INDIRECT ADDRESS cari data kiri


  • Rumus MATCH(H1,D:D,0) menghasilkan angka 3 yaitu posisi baris sel yang berisi ID 13043
  • Rumus COLUMN(B:B) menghasilkan angka 2  yaitu nomur urut kolom B dalam lembar kerja excel
  • Fungsi ADDRESS digunakan untuk mendapatkan alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN.  Sehingga jika diringkas menjadi  =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
  • Fungsi INDIRECT digunakan untuk mendapatkan nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 adalah “Dulah”


Ringkasan:
Ada banyak cara untuk menyelesaikan tugas Excel. Termasuk untuk melakukan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita bisa menggunakan beberapa alternative seperti 5 contoh rumus yang sudah dijelaskan yaitu: kombinasi VLOOKUP – CHOOSE, kombinasi HLOOKUP-MATCH, kombinasi INDEX-MATCH, kombinasi OFFSET-MATCH dan kombinasi INDIRECT-ADDRESS-MATCH-COLUMN.

Demikian semoga bermanfaat.
Salam..


Baca juga tutorial Belajar Excel Lainnya:




Diposting oleh

pemahaman mendasar penggunaan aplikasi microsoft excel bagi pemula-part5

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel Intro, Artikel Tips dan Trik, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : pemahaman mendasar penggunaan aplikasi microsoft excel bagi pemula-part5
link : pemahaman mendasar penggunaan aplikasi microsoft excel bagi pemula-part5

Baca juga


January 2017

pemahaman mendasar penggunaan aplikasi microsoft excel bagi pemula-part5 (memahami penggunaan istilah-istilah pada lembar kerja excel atau aplikasi office)

Sebelumnya sudah dibahas langkah-langkah belajar aplikasi excel bagi pemula mulai dari :
  • belajar aplikasi bagi pemula-part1 membedakan istilah CELL dan RANGE 
  • belajar aplikasi bagi pemula-part2 menggunakan mouse 
  • belajar aplikasi bagi pemula-part3 menggunakan keybord
  • belajar aplikasi bagi pemula-part4 struktur pengetikan rumus pada aplikasi excel
Bisa di cek di Sitemap

postingan artikel kali ini adalah belajar aplikasi excel bagi pemula-part5 yaitu tentang memahami penggunaan istilah mulai dari pojok kiri atas aplikasi office sampai pojok kanan bawah aplikasi office. kenapa kita harus memahami, karena banyak diantara pemula yang belajar aplikasi office hanya tahu istilah tombol aplikasi dari segi visual saja tanpa tahu apa nama istilah tombol yang digunakan.

misalkan dalam aplikasi office excel ketika ditanya bagaimana cara memberikan background warna pada cell atau range ? mungkin bagi sebagian orang akan bilang " klik saja tombol yang seperti kaleng mau tumpah ", itu adalah jawaban yang tidak relevan walaupun tujuannya sama. berbeda dengan sebagian orang yang sudah menggunakan istilah aplikasi office untuk menunjukan fungsi tombol tersebut, mungkin akan bilang " di Menu Home cari Group Ribbon Font dan klik Command Button atau Tombol Perintah Fill Color ", hemmzz sangat relevan sekali jawabannya.. wkwk :D ILUSTRASI SAJA..
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar tampilan aplikasi excel

langsung saja kenalan dengan istilah-istilah tersebut :

  • Access Toolbar / Quick Access Toolbar
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Access Toolbar biasanya ada disebelah pojok atas kiri. dan pengguna excel bisa menambahkan objek toolbar baru dengan cara mengklik optionnya

  • Tittle / Judul
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar objek title biasa nya untuk mengetahui judul dari excel yang dibuka

  • Minimize , Maximize / Restore , Close
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini biasanya ada disebelah pojok atas kanan. dan setiap aplikasi dalam komputer tersedia tombol standar seperti gambar diatas yaitu minimize, maximize/restore dan close

  • Menu Utama
http://belajarmicrosoftexceloffice.blogspot.co.id/
Setiap aplikasi pasti ada menu utama yang berfungsi untuk membuka fitur-fitur yang ditawarkan oleh setiap aplikasi, excel mempunyai menu standar yang aktif mulai dari File sampai dengan  Inquire.

  • Ribbon ( objek dalam menu utama )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Ribbon untuk menu utama HOME. intinya ribbon itu fitur-fitur yang ada dalam sebuah menu

  • Group Ribbon ( bagian kecil dari ribon )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Group Ribbon FONT. untuk group ribbon ini adalah sub bagian dari ribbon.

  • Name Box
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini untuk menunjukan posisi dari cell yang sedang aktif

  • Formula Bar
http://belajarmicrosoftexceloffice.blogspot.co.id/
Formula bar adalah objek dalam aplikasi excel untuk mengetahui format penulisan rumus excel yang sedang dikerjakan atau melihat rumus dari cell yang aktif

  • Worksheet / Lembar Kerja
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek worksheet pada aplikasi excel atau lembar kerja aplikasi excel

  • Scroll Bar ( vertikal atau horizontal )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek untuk menggeser tampilan layar worksheet, ada dua jenis scroll bar yaitu jenis vertikan dan horizontal

  • View Worksheet
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini digunakan untuk melihat tampilan worksheet yang berbeda

  • Zoom Out dan Zoom In
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini untuk melihat tampilan layar worksheet lebih besar atau lebih kecil
gambar-gambar diatas hanya sebagain kecil dari istilah-istilah objek yang harus diketahui. cara simple ketika ingin mengetahui istilah dari objek yang digunakan, cukup dengan geserkan kursor ke arah objek maka nanti akan muncul sebuah tooltips yang menunjukan nama objek dan fungsinya. seperti gambar dibawah ini :
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar kursor dan tooltips dengan tujuan objek BOLD
nantikan artikel lanjutannya di PART-6. jika ada hal-hal yang kurang dipahami atau kurang dimengerti, silahkan berikan komentar dalam artikel ini. begitu mudah belajar aplikasi microsoft excel. indahnya berbagi pengetahuan.

pemahaman mendasar penggunaan aplikasi microsoft excel bagi pemula-part5 (memahami penggunaan istilah-istilah pada lembar kerja excel atau aplikasi office)

Sebelumnya sudah dibahas langkah-langkah belajar aplikasi excel bagi pemula mulai dari :
  • belajar aplikasi bagi pemula-part1 membedakan istilah CELL dan RANGE 
  • belajar aplikasi bagi pemula-part2 menggunakan mouse 
  • belajar aplikasi bagi pemula-part3 menggunakan keybord
  • belajar aplikasi bagi pemula-part4 struktur pengetikan rumus pada aplikasi excel
Bisa di cek di Sitemap

postingan artikel kali ini adalah belajar aplikasi excel bagi pemula-part5 yaitu tentang memahami penggunaan istilah mulai dari pojok kiri atas aplikasi office sampai pojok kanan bawah aplikasi office. kenapa kita harus memahami, karena banyak diantara pemula yang belajar aplikasi office hanya tahu istilah tombol aplikasi dari segi visual saja tanpa tahu apa nama istilah tombol yang digunakan.

misalkan dalam aplikasi office excel ketika ditanya bagaimana cara memberikan background warna pada cell atau range ? mungkin bagi sebagian orang akan bilang " klik saja tombol yang seperti kaleng mau tumpah ", itu adalah jawaban yang tidak relevan walaupun tujuannya sama. berbeda dengan sebagian orang yang sudah menggunakan istilah aplikasi office untuk menunjukan fungsi tombol tersebut, mungkin akan bilang " di Menu Home cari Group Ribbon Font dan klik Command Button atau Tombol Perintah Fill Color ", hemmzz sangat relevan sekali jawabannya.. wkwk :D ILUSTRASI SAJA..
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar tampilan aplikasi excel

langsung saja kenalan dengan istilah-istilah tersebut :

  • Access Toolbar / Quick Access Toolbar
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Access Toolbar biasanya ada disebelah pojok atas kiri. dan pengguna excel bisa menambahkan objek toolbar baru dengan cara mengklik optionnya

  • Tittle / Judul
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar objek title biasa nya untuk mengetahui judul dari excel yang dibuka

  • Minimize , Maximize / Restore , Close
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini biasanya ada disebelah pojok atas kanan. dan setiap aplikasi dalam komputer tersedia tombol standar seperti gambar diatas yaitu minimize, maximize/restore dan close

  • Menu Utama
http://belajarmicrosoftexceloffice.blogspot.co.id/
Setiap aplikasi pasti ada menu utama yang berfungsi untuk membuka fitur-fitur yang ditawarkan oleh setiap aplikasi, excel mempunyai menu standar yang aktif mulai dari File sampai dengan  Inquire.

  • Ribbon ( objek dalam menu utama )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Ribbon untuk menu utama HOME. intinya ribbon itu fitur-fitur yang ada dalam sebuah menu

  • Group Ribbon ( bagian kecil dari ribon )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar Group Ribbon FONT. untuk group ribbon ini adalah sub bagian dari ribbon.

  • Name Box
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini untuk menunjukan posisi dari cell yang sedang aktif

  • Formula Bar
http://belajarmicrosoftexceloffice.blogspot.co.id/
Formula bar adalah objek dalam aplikasi excel untuk mengetahui format penulisan rumus excel yang sedang dikerjakan atau melihat rumus dari cell yang aktif

  • Worksheet / Lembar Kerja
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek worksheet pada aplikasi excel atau lembar kerja aplikasi excel

  • Scroll Bar ( vertikal atau horizontal )
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek untuk menggeser tampilan layar worksheet, ada dua jenis scroll bar yaitu jenis vertikan dan horizontal

  • View Worksheet
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini digunakan untuk melihat tampilan worksheet yang berbeda

  • Zoom Out dan Zoom In
http://belajarmicrosoftexceloffice.blogspot.co.id/
Objek ini untuk melihat tampilan layar worksheet lebih besar atau lebih kecil
gambar-gambar diatas hanya sebagain kecil dari istilah-istilah objek yang harus diketahui. cara simple ketika ingin mengetahui istilah dari objek yang digunakan, cukup dengan geserkan kursor ke arah objek maka nanti akan muncul sebuah tooltips yang menunjukan nama objek dan fungsinya. seperti gambar dibawah ini :
http://belajarmicrosoftexceloffice.blogspot.co.id/
Gambar kursor dan tooltips dengan tujuan objek BOLD
nantikan artikel lanjutannya di PART-6. jika ada hal-hal yang kurang dipahami atau kurang dimengerti, silahkan berikan komentar dalam artikel ini. begitu mudah belajar aplikasi microsoft excel. indahnya berbagi pengetahuan.

Diposting oleh

Cara Menghitung Jumlah Huruf dan Kata

January 2017 - Hallo sahabat Belajar Excel Terbaru, Pada Artikel yang anda baca kali ini dengan judul January 2017, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel Belajar Excel, Artikel Fungsi, Artikel Rumus, Artikel Text, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : Cara Menghitung Jumlah Huruf dan Kata
link : Cara Menghitung Jumlah Huruf dan Kata

Baca juga


January 2017

Menghitung Jumlah Huruf dan Kata
Masih perihal rumus yang bekerja pada data text. Kali ini kita akan membahas bagaimana cara menghitung jumlah huruf , karakter atau kata tertentu dalam sebuah text atau string. Menghitung jumlah bagian text tertentu bisa dilakukan dengan satu formula saja, baik itu menghitung text dalam satu sel saja , maupun menghitung text yang terdapat dalam range atau kumpulan sel .


Secara garis besar,  struktur pembahasan belajar excel kali ini mencakup:

Cara Menghitung Jumlah Huruf dan Kata Pada Excel


  • Menghitung jumlah karakter atau kata dalam satu sel.
    • Menghitung huruf atau karakter tunggal
      • Dengan membedakan huruf besar/kecil
      • Tanpa Membedakan huruf besar/kecil
    • Menghitung jumlah kata 
      • Dengan membedakan huruf besar/kecil
      • Tanpa membedakan huruf besar/kecil
  • Menghitung jumlah karakter atau kata dalam kumpulan sel.
    • Menghitung huruf atau karakter tunggal
      • Dengan membedakan huruf besar/kecil
      • Tanpa Membedakan huruf besar/kecil
    • Menghitung jumlah kata 
      • Dengan membedakan huruf besar/kecil
      • Tanpa membedakan huruf besar/kecil


Menghitung jumlah karakter atau kata dalam satu sel.

Anggaplah kita memiliki text yang akan dihitung jumlah huruf, karakter atau kata dalam text tersebut.

Text     :
Saya anak yang baik. Anak-anak lain suka saya
Lokasi :
Sel B2

Maka rumus yang bisa digunakan dapat dilihat dalam tabel contoh di bawah ini.

No
Menghitung
Hasil
Formula
1
Semua Karakter
45
=LEN(B2)
2
Huruf "a"
13
=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))
3
Huruf "A"
1
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
4
Huruf "a" dan "A"
14
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
5
Kata "anak"
2
=(LEN(B2)-LEN(SUBSTITUTE(B2,"anak","")))/LEN("anak")
6
Kata "Anak"
1
=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")
7
Kata "anak" dan "Anak"
3
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

Bisa diperhatikan kembali pada contoh diatas, bahwa secara umum fungsi yang dibutuhkan untuk keperluan perhitungan karakter atau kata adalah LEN dan SUBSTITUTE  serta UPPER atau LOWER
  • Fungsi LEN berguna untuk menghitung jumlah karakter dalam text.
  • Fungsi SUBSTITUTE berguna untuk mengganti bagian text  atau karakter tertentu dengan text atau karakter lainnya. Fungsi ini bersifat Case Sensitive, sehingga menganggap beda antara huruf kecil dengan kapital (contoh : huruf “a” dan huruf “A”). 
  • Fungsi UPPER berguna untuk merubah semua huruf dalam text menjadi huruf kapital semuanya
  • Fungsi LOWER berguna untuk merubah semua huruf dalam text menjadi huruf kecil semuanya


Cara Kerja Rumus Excel Untuk Menghitung Jumlah Huruf Dan Karakter Tertentu.


Menghitung karakter atau huruf tunggal dengan membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

  • Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisih itulah yang merupakan jumlah karakter yang dicari.

Menghitung karakter atau huruf tunggal tanpa membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dan "A" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

  • Fungsi UPPER akan merubah semua huruf dalam text menjadi huruf besar semua
  • Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung (A) dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisih itulah yang merupakan jumlah karakter yang dicari.
  • Dengan konsep yang serupa, kita juga bisa mengganti fungsi UPPER menjadi LOWER
    • =LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"a",""))

Menghitung jumlah kata dengan membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "Anak" dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")

  • Fungsi SUBSTITUTE akan menukar kata "Anak" dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisihnya kemudian dibagi dengan banyaknya karakter kata "Anak"
  • Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak"

Menghitung jumlah kata Tanpa membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "Anak" dan "Anak"  dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

  • Fungsi LOWER akan merubah semua huruf dalam text menjadi huruf kecil semua
  • Fungsi SUBSTITUTE akan menukar kata "anak" dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisihnya kemudian dibagi dengan banyaknya karakter kata "anak"
  • Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak" dan "anak"
  • Dengan konsep yang serupa, kita juga bisa mengganti fungsi LOWER menjadi UPPER
    • =(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"ANAK","")))/LEN("ANAK")


Menghitung jumlah karakter atau kata dalam kumpulan sel.


Anggaplah text yang akan kita hitung adalah seperti berikut:

Text    :
Nama saya Saleh
Saya anak yang baik
Anak-anak lain suka saya
Apalagi anak komplek
Lokasi :
Sel B2:B5

Maka rumus untuk menghitung kehadiran karakter atau kata dalam kumpulan sel B2:B5 dapat diformulasikan seperti contoh dalam tabel di bawah ini:

No
Menghitung
Jumlah
Formula
1
Semua Karakter
78
=SUMPRODUCT(LEN(B2:B5))
2
Huruf "a"
22
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))
3
Huruf "A"
2
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))
4
Huruf "a" dan "A"
24
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))
5
Kata "anak"
3
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))
6
Kata "Anak"
1
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))
7
Kata "anak" dan "Anak"
4
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))

Dapat kita perhatikan dari contoh diatas, bahwa perbedaan mendasar dari rumus yang digunakan untuk menghitung banyaknya huruf atau kata dalam kumpulan sel dibandingkan dalam satu sel saja adalah adanya penggunaan fungsi SUMPRODUCT.  

Cara kerja rumus untuk menghitung huruf dan kata pada kumpulan sel sebenarnya hampir sama saja dengan cara kerja rumus untuk menghitung huruf dan kata pada satu sel.

Yang membedakannya adalah karena bekerja pada array atau kumpulan sel maka perlu ditambahkan fungsi untuk menjumlahkan hasil dari masing-masing sel. Tambahan fungsi yang digunakan di sini adalah SUMPRODUCT.

Sebagai alternative lainnya, kita juga bisa menggunakakan rumus array CSE. Cukup ganti SUMPRODUCT menjadi SUM, dan jangan lupa untuk tekan CTR+SHIFT+ENTER setelah mengetikan rumusnya.


No
Menghitung
Jumlah
Formula
1
Semua Karakter
78
{=SUM(LEN(B2:B5))}
2
Huruf "a"
22
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))}
3
Huruf "A"
2
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))}
4
Huruf "a" dan "A"
24
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))}
5
Kata "anak"
3
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))}
6
Kata "Anak"
1
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))}
7
Kata "anak" dan "Anak"
4
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))}


Ringkasan:
Cara menghitung banyaknya huruf atau kata tertentu pada data excel dapat dilakukan menggunakan rumus kombinasi fungsi LEN,SUBSTITUTE,UPPER/LOWER serta operasi matematika pengurangan dan pembagian. Jika text yang dihitung terdapat pada kumpulan sel, maka cukup tambahkan fungsi SUMPRODUCT pada rumus kombinasi tadi. Sebagai alternative, fungsi SUMPRODUCT juga bisa diganti fungsi SUM dalam bentuk rumus array.

Demikian semoga bermanfaat.
Salam.. 😉

Baca juga tutorial belajar excel lainnya:

Menghitung Jumlah Huruf dan Kata
Masih perihal rumus yang bekerja pada data text. Kali ini kita akan membahas bagaimana cara menghitung jumlah huruf , karakter atau kata tertentu dalam sebuah text atau string. Menghitung jumlah bagian text tertentu bisa dilakukan dengan satu formula saja, baik itu menghitung text dalam satu sel saja , maupun menghitung text yang terdapat dalam range atau kumpulan sel .


Secara garis besar,  struktur pembahasan belajar excel kali ini mencakup:

Cara Menghitung Jumlah Huruf dan Kata Pada Excel


  • Menghitung jumlah karakter atau kata dalam satu sel.
    • Menghitung huruf atau karakter tunggal
      • Dengan membedakan huruf besar/kecil
      • Tanpa Membedakan huruf besar/kecil
    • Menghitung jumlah kata 
      • Dengan membedakan huruf besar/kecil
      • Tanpa membedakan huruf besar/kecil
  • Menghitung jumlah karakter atau kata dalam kumpulan sel.
    • Menghitung huruf atau karakter tunggal
      • Dengan membedakan huruf besar/kecil
      • Tanpa Membedakan huruf besar/kecil
    • Menghitung jumlah kata 
      • Dengan membedakan huruf besar/kecil
      • Tanpa membedakan huruf besar/kecil


Menghitung jumlah karakter atau kata dalam satu sel.

Anggaplah kita memiliki text yang akan dihitung jumlah huruf, karakter atau kata dalam text tersebut.

Text     :
Saya anak yang baik. Anak-anak lain suka saya
Lokasi :
Sel B2

Maka rumus yang bisa digunakan dapat dilihat dalam tabel contoh di bawah ini.

No
Menghitung
Hasil
Formula
1
Semua Karakter
45
=LEN(B2)
2
Huruf "a"
13
=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))
3
Huruf "A"
1
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
4
Huruf "a" dan "A"
14
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
5
Kata "anak"
2
=(LEN(B2)-LEN(SUBSTITUTE(B2,"anak","")))/LEN("anak")
6
Kata "Anak"
1
=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")
7
Kata "anak" dan "Anak"
3
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

Bisa diperhatikan kembali pada contoh diatas, bahwa secara umum fungsi yang dibutuhkan untuk keperluan perhitungan karakter atau kata adalah LEN dan SUBSTITUTE  serta UPPER atau LOWER
  • Fungsi LEN berguna untuk menghitung jumlah karakter dalam text.
  • Fungsi SUBSTITUTE berguna untuk mengganti bagian text  atau karakter tertentu dengan text atau karakter lainnya. Fungsi ini bersifat Case Sensitive, sehingga menganggap beda antara huruf kecil dengan kapital (contoh : huruf “a” dan huruf “A”). 
  • Fungsi UPPER berguna untuk merubah semua huruf dalam text menjadi huruf kapital semuanya
  • Fungsi LOWER berguna untuk merubah semua huruf dalam text menjadi huruf kecil semuanya


Cara Kerja Rumus Excel Untuk Menghitung Jumlah Huruf Dan Karakter Tertentu.


Menghitung karakter atau huruf tunggal dengan membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

  • Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisih itulah yang merupakan jumlah karakter yang dicari.

Menghitung karakter atau huruf tunggal tanpa membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dan "A" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

  • Fungsi UPPER akan merubah semua huruf dalam text menjadi huruf besar semua
  • Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung (A) dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisih itulah yang merupakan jumlah karakter yang dicari.
  • Dengan konsep yang serupa, kita juga bisa mengganti fungsi UPPER menjadi LOWER
    • =LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"a",""))

Menghitung jumlah kata dengan membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "Anak" dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")

  • Fungsi SUBSTITUTE akan menukar kata "Anak" dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisihnya kemudian dibagi dengan banyaknya karakter kata "Anak"
  • Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak"

Menghitung jumlah kata Tanpa membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "Anak" dan "Anak"  dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

  • Fungsi LOWER akan merubah semua huruf dalam text menjadi huruf kecil semua
  • Fungsi SUBSTITUTE akan menukar kata "anak" dengan text kosong 
  • Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
  • Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
  • Selisihnya kemudian dibagi dengan banyaknya karakter kata "anak"
  • Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak" dan "anak"
  • Dengan konsep yang serupa, kita juga bisa mengganti fungsi LOWER menjadi UPPER
    • =(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"ANAK","")))/LEN("ANAK")


Menghitung jumlah karakter atau kata dalam kumpulan sel.


Anggaplah text yang akan kita hitung adalah seperti berikut:

Text    :
Nama saya Saleh
Saya anak yang baik
Anak-anak lain suka saya
Apalagi anak komplek
Lokasi :
Sel B2:B5

Maka rumus untuk menghitung kehadiran karakter atau kata dalam kumpulan sel B2:B5 dapat diformulasikan seperti contoh dalam tabel di bawah ini:

No
Menghitung
Jumlah
Formula
1
Semua Karakter
78
=SUMPRODUCT(LEN(B2:B5))
2
Huruf "a"
22
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))
3
Huruf "A"
2
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))
4
Huruf "a" dan "A"
24
=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))
5
Kata "anak"
3
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))
6
Kata "Anak"
1
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))
7
Kata "anak" dan "Anak"
4
=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))

Dapat kita perhatikan dari contoh diatas, bahwa perbedaan mendasar dari rumus yang digunakan untuk menghitung banyaknya huruf atau kata dalam kumpulan sel dibandingkan dalam satu sel saja adalah adanya penggunaan fungsi SUMPRODUCT.  

Cara kerja rumus untuk menghitung huruf dan kata pada kumpulan sel sebenarnya hampir sama saja dengan cara kerja rumus untuk menghitung huruf dan kata pada satu sel.

Yang membedakannya adalah karena bekerja pada array atau kumpulan sel maka perlu ditambahkan fungsi untuk menjumlahkan hasil dari masing-masing sel. Tambahan fungsi yang digunakan di sini adalah SUMPRODUCT.

Sebagai alternative lainnya, kita juga bisa menggunakakan rumus array CSE. Cukup ganti SUMPRODUCT menjadi SUM, dan jangan lupa untuk tekan CTR+SHIFT+ENTER setelah mengetikan rumusnya.


No
Menghitung
Jumlah
Formula
1
Semua Karakter
78
{=SUM(LEN(B2:B5))}
2
Huruf "a"
22
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))}
3
Huruf "A"
2
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))}
4
Huruf "a" dan "A"
24
{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))}
5
Kata "anak"
3
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))}
6
Kata "Anak"
1
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))}
7
Kata "anak" dan "Anak"
4
{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))}


Ringkasan:
Cara menghitung banyaknya huruf atau kata tertentu pada data excel dapat dilakukan menggunakan rumus kombinasi fungsi LEN,SUBSTITUTE,UPPER/LOWER serta operasi matematika pengurangan dan pembagian. Jika text yang dihitung terdapat pada kumpulan sel, maka cukup tambahkan fungsi SUMPRODUCT pada rumus kombinasi tadi. Sebagai alternative, fungsi SUMPRODUCT juga bisa diganti fungsi SUM dalam bentuk rumus array.

Demikian semoga bermanfaat.
Salam.. 😉

Baca juga tutorial belajar excel lainnya:

Diposting oleh
Subscribe to: Posts (Atom)

Popular Posts