Mencari dan menghitung jumlah cell yang berisikan text/kata tertentu pada baris atau kolom excel

Menggunakan fungsi COUNTIF

Fungsi COUNTIF 

Menghitung jumlah cell yang memiliki kata/karakter sesuai dengan kriteria yang kita tentukan

Formula

=COUNTIF (range, criteria)

Argumen

• range - area baris dan kolom yang kita akan periksa
• criteria - Kriteria yang akan kita hitung (text/karakter).

Catatan

Kriteria yang bukan berupa angka harus di ikuti dengan tanda kutip dua, untuk  kriteria yang berupa angka tidak perlu:

=COUNTIF(A1:A10,100) // count cells equal to 100
=COUNTIF(A1:A10,">32") // count cells greater than 32
=COUNTIF(A1:A10,"jim") // count cells equal to "jim"
=COUNTIF(A1:A10,"<"&B1) // count cells less than value in B1

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.

Membandingkan data dengan menggunakan fungsi VLOOKUP Excel

Penggunaan VLOOKUP 

Melihat/membandingkan nilai yang ada pada sebuah tabel dan mencocokkan nya dengan pada nilai yang ada di kolom pertama

Hasil 

Nilai/isi yang sesuai dengan tabel utama.

Formula 

=VLOOKUP (value, table, col_index, [range_lookup])

Argumen

• value - nilai/data yang akan diperiksa pada kolom pertama.
• table - tabel yang berisikan data.
• col_index - kolom pada tabel yang digunakan sebagai acuan.

Catatan

VLOOKUP searches for a value in the first column of a table. At the match row, it retrieves a value from the specified column.

Use VLOOKUP when lookup values are located in the first column of a table with information organized vertically. Use HLOOKUP when lookup values are located in the first row of a table, and each "record" appears in a new column.

• Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
• Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
• If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
• When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
• If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
• If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.

Contoh lain Formula

VLOOKUP dari workbook/file yang berbeda

Generic formula 

=VLOOKUP(B5,[workbook]sheet!range,4,0)

Menghilangkan spasi pada awal dan akhir text pada cell excel

Seringkali saat kita melakukan copy dari data word atau test dari notepad dan paste ke Excel
ataupun saat menggunakan import data from text(csv to excel) kita mendapati text diawali dengan spasi atau di akhiri dengan spasi.

Mungkin hal ini adalah hal kecil yang tidak terlalu mengganggu user untuk melakukan olah data pada excel. tetapi akan menjadi masalah saat user akan menggunakan fungsi seperti lookup, exact, vlookup. dimana pembandingan data tidak dapat dilakukan karena adanya perbedaan spasi.
berikut cara mengatasi nya

Generic formula 

=TRIM(text)

Penjelasan 

untuk menghilangkan spasi pada awal dan akhir text, anda dapat menggunakan fungsi Trim. dimana fungsiini akan mencari spasi pada awal dan akhir text dan menghilangkannya, menyisakan text yang kita butuhkan.

seperti contoh pada gambar,formula pada kolom C3 adalah:

=TRIM(B3)

TRIM dengan CLEAN

jika anda juga ingin menghilangkan karakter line breaks pada cells, anda dapat menambahkan fungsi CLEAN seperti berikut:

=TRIM(CLEAN(text))

Fungsi CLEAN akan menghapus karatker-karakter yang tidak perlu atau yang tidak ingin kita cetak seperti line breaks, dan akan menyisakan "cleaned" text. 

Masalah dengan karakter lainnya

Fungsi Clean tersebut tidak benar-benar bisa menghilangkan semua karakter karakter yang tidak kita butuhkan, seperti karakter-karakter khusus lainnya. dengan menambahkan fungsi SUBSTITUTE pada formula, anda dapat menghilangkan karakter spesifik. seperti contoh, menghilangkan non-breaking space:

=TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160)," ")))

Selamat mencoba.

sumber

Script SQL pada query Ms. Excell

Mungkin belum banyak tahu atau yang mengekpose, bahwa di MS-Acess terdapat fitur advance yang jarang kita manfaatkan, yaitu kita dapat menggunakan query command untuk hal-hal lain diluar select, insert dan update yaitu seperti CREATE TABLE, CREATE FOREIGN KEY,LIST TABLE IN DATABASE… DLL seperti di SQL server. Berikut Caranya Mengeksecute query di MS-Accces 2007:

1. Create Query

2. Close Default Window Untuk Query Wizzard

3. Pilih Query Pada Mode : Design SQL View

4. Sekarang Kita Bebas Mengetik SQL command semau kita, dan click RUN untuk menjalankannya

HAPPY CODING & FUN !!!!

Beberapa Hidden SQL command di MS-ACCESS yang mungkin sangat berguna:

- Untuk melihat “daftar table di MS-ACCESS” menggunakan perintah query:
SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

- Untuk membuat table di ms-access:
CREATE TABLE A (KEY1 INTEGER,AAA VARCHAR(10),KEY2 INTEGER PRIMARY KEY(KEY1));
CREATE TABLE B (KEY2 INTEGER,BBB VARCHAR(10), PRIMARY KEY(KEY2));

- untuk membuat constrain dan relation:
ALTER TABLE A ADD FOREIGN KEY(KEY2) REFERENCES B (KEY1);

Atau Ada Cara lain yaitu dengan melihat hidden object dan system table, caranya adalah sbb:

Sumber

Menyisipkan tanda kutip (Apostrophe) untuk angka di Cell kolom Excel

Untuk setiap karakter numerik di exel, mungkin sewaktu-waktu anda akan menyisipkan "tanda kutip" atau apstrophe di cell atau kolom tertentu pada sheet excell yang berisikan angka untuk membuat angka tersebut dikenali sebagai Text.

berikut langkah-langkah untuk menyisipkan tanda kutip atau apostrophe dengan menggunakan excel macro


1. Buka dokumen excel anda dengan Cell-cell atau kolom yang berisikan angka dan akan kita sisipkan tanda kutip(apostrophe)

2. buka halaman macro dengan tombol (alt + F11)
akan terlihat halaman seperti berikut

3. Pilih sheet yang akan anda proses, kemudian paste-kan script berikut ini

Option Explicit

Sub addapostrophe()

        Dim cell As Range

        For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)

        cell.Select

If cell.Value <> "" Then

       cell.Value = "'" & cell.Value

       End If

    Next

End Sub

4. jalankan script nya denga tombol "F5"

5. kemudian tutup halaman macro dengan alt+Q

Lihat perubahan pada sheet excel anda

Selamat Mencoba

+Gema Saragih 

Lupa Password Sheet excel yang di Protect

Mungkin anda sudah terbiasa dengan fitur-fitur Ms. excel

sering kali  anda akan melakukan proteksi untuk file-file dokumen pekerjaan anda
dalam hal ini proteksi pada sebuah sheet excel.

berikut akan saya berikan tips jika sewaktu-waktu anda lupa Password yang anda gunakan dengan menggunakan fungsi macro



1. Buka File dengan Sheet yang ter-protect.
2. tekan tombol "alt + F11" pada keyboard anda.
3. akan muncul sebuah halaman seperti berikut

4. Klik Sheet yang akan anda proses dan paste kan script berikut

''Script Macro

Option Explicit

Sub bukapassword()

Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
        MsgBox "One usable password is " & Chr(i) & Chr(j) & _
            Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
            Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
         Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next

End Sub

5. kemudian tekan tombol "F5" pada keyboard anda.
6. Tutup halaman macro anda (alt+Q) dan kembali ke dokumen anda

Sheet yang protected akan terbuka.

Selamat Mencoba
+Gema Saragih