[excell] Mencari Nilai Dari Baris Akhir

Anda mungkin pernah mengalami masalah, bagaimana cara menampilkan baris data terakhir yg selalu diperbarui?! saya akan memberikan sedikit tutorial untuk masalah yg Anda alami.

Jumlah Buah Harian

gambar di atas adalah contoh untuk data buah-buahan yang setiap hari harus diperbaharui. ada 4 kolom yang terdiri dari No, Tanggal, Mangga & Jeruk. tiap hari Anda harus memperbaharui data jumlah buah tersebut.

Data Yang Diinginkan

Data terakhir yang harus tampil adalah 10 desember 2011 (12/10/2011)  dengan jumlah Mangga & Jeruk masing-masing 75 & 83. dengan menggunakan fungsi VLOOKUP :

=VLOOKUP(B11,A1:D11,2)
=VLOOKUP(B11,A1:D11,2)
=VLOOKUP(B11,A1:D11,2)

bagaimana jika besoknya data berubah lagi?! misal tgl 11, 12, 13 desember dst. jika Anda mengupdate secara manual, tentu akan sangat merepotkan bukan?!

ada cara yg lebih efisien bisa kita gunakan. yg akan kita jadikan patokan adalah data tanggal pada kolom B. untuk mencari data yg paling baru, anda bisa memakai tanggal terbaru sebagai acuan dengan menggunakan fungsi MAX() . perhatikan rumus berikut :

=MAX(B:B)

formula di atas akan menghasilkan tgl 10 desember 2011 (berupa angka 40887).

untuk mendapatkan jumlah mangga dan jeruk kita kombinasikan fungsi MAX() dengan VLOOKUP() sbb:

=VLOOKUP(MAX(B:B),B:D,2)

=VLOOKUP(MAX(B:B),B:D,3)

jika anda menambahkan data pada baris berikutnya, misalkan tanggal 11 desember 2011, maka formula akan menyesuaikan dengan otomatis

contoh perubahan data

untuk lebih jelasnya, silahkan download attachment untuk demo tutorial ini:
001.zip

Iklan

Bikin Hari Otomatis, Berdasarkan Tgl di Excell

sering kali kita dihadapkan pada kondisi dimana kita harus menyesuaikan hari berdasarkan tanggal. contoh kasus ketika kita membuat form absensi manual, setiap kali ada pergantian tanggal hari yang kita inputkan secara manual, harus secara berkala kita update.

sheet absensi

klo data hari yang kita update hanya sedikit mungkin tidak akan masalah, lain halnya jika data yang kita rubah banyak. hal ini akan melelahkan dan membutuhkan waktu, walaupun cuma sekedar copy paste. apalagi jika jumlah data ratusan, akan lebih menguras waktu dan tenaga 🙂

ada tips yang bisa anda gunakan, agar pekerjaan yang melelahkan dan membosan serta menguras tenaga mengganti hari tersebut dipermudah. salah satu caranya adalah menggunakan fungsi yang telah disediakan oleh excell yaitu fungsi : TEXT(cell; <format>). misal =TEXT(B5; “dddd”)

fungsi TEXT untuk menampilkan hari

fungsi yang digunakan diatas akan menampilkan nama hari pada cell A5, sesuai dengan tanggal di cell B5. namun perlu diingat bahwa fungsi =TEXT(B5; “dddd”) akan menampilkan format default dari cell. misal, jika format defaulnya adalah nama hari dalam bahasa indonesia, maka akan ditampilkan “Sabtu”. sedangkan jika format inggris, maka akan ditampilkan “Saturday”.

jika anda ingin agar format nama hari tidak bisa diubah, anda bisa menggunakan function LOOKUP() dan WEEKDAY(). fomula pada cell A5 : =LOOKUP(WEEKDAY(B5;2);{1;2;3;4;5;6;7};{“Senin”;”Selasa”;”Rabu”;”Kamis”;”Jumat”;”Sabtu”;”Minggu”})

formula hari fix

fungsi LOOKUP di atas akan menampilkan hari Senin – Minggu, berdasarkan WEEKDAY. untuk lebih jelasnya, silahkan lihat penggunaan yang saya copy paste dr help excell dibawah ini:

Syntax

WEEKDAY(serial_number,return_type)

Serial_number is a sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines the type of return value.

Return_type	Number returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).

selanjutnya untuk cell A6 dst, anda tinggal copy paste ajah.

Ripping DVD dengan AutoGK

AutoGK adalah software yang bisa anda gunakan untuk meripping kepingan dvd menjadi sebuah file avi. hanya dengan 4 langkah, anda sudah  dapat sudah menghasilkan film avi berkualitas. untuk input file, anda dapat memilih file vob yang pertama. misalkan pada DVD terdapat file *_1.vob, *_2.vob dan seterusnya, maka input yang harus anda pilih adalah file *_1.vob.

selain meripping kepingan dvd, AutoGK juga dapat digunakan untuk mengecilkan file avi. ada 11 opsi ukuran sudah ditentukan. diantaranya adalah: 175 Mb, 233 Mb, 350 Mb, 700 Mb (1 CD) dst. yang terbesar adalah 2 Gb. biasanya digunakan untuk kepingan DVD. selain itu, anda bisa menentukan sendiri size yang diinginkan atau anda juga bs berdasarkan persentase kualitas yang diinginkan.

software ini bisa di Anda download secara gratis di www.autogk.me.uk

[Excell] analisa data secara sederhana

minggu kemaren ketika bermain ke sebuah forum, chip forum lebih tepatnya, ane dptin pertanyaan dari seorang TS (trhead starter), lebih kurang demikian (copy paste dr forum)

Mohon bantuannya untuk yang biasa berkutat dengan excel. Itu seperti gambar diatas, gw lagi bikin analisa data pendapatan secara sederhana saja. Salah satu data yang gw perlukan adalah persentase kenaikan/penurunan omset bulan ini dibanding bulan sebelumnya.
Untuk lebih jelasnya kalau lihat hasil pada “C36” yg sebesar 4.78% itu adalah selisih dari income tgl 1-16 Juli dengan tgl 1-16 Juni. Rumusnya sih sederhana banget, bisa lihat di gambar diatas.
Permasalahannya adalah kalau gw mau hasil pada “C36″ itu bisa otomatis berubah dengan benar kalau gw nambah inputan data tanggal berikutnya, karena otomatis data pembandingnya juga berubah (pengurang dan pembaginya).

Gimana nih rumusnya ?? Udah cari” di internet tutorial Excel, nggak nemu

cukup jelas bukan? pertanyaanya adalah gimana caranya dia bisa ngitung otomatis, di bulan Juni, berdasarkan tanggal yang terisi di bulan Juli.

lebih jelasnya demikian, jika ada nilai di bulan juli pada range C2:C17, maka dia menginginkan agar range B2:B17 dijumlahkan. demikian juga jika ada nilai di range C2:C32, maka range B2:B32 juga ikut ditotal, demikian seterusnya. artinya jika ada nilai di range bulan juli, maka range bulan juni ditotal.

dapat diambil kesimpulan bahwa, nilai yang diharapkan adalah:

total = sum(B2:Bx), dimana C2:Cx harus > 0 (lebih besar dari nol, ada nilai 1 ke atas)

klo kita menggunakan fungsi sum, maka kita kudu mengotak-atik rumus secara manual. dengan menggunakana fungsi SUMIF(range; criteria; sum_val), permasalahan di atas dapat diselesaikan.

formula untuk menyelesaikan permasalahan diatas adalah sbb:

= SUMIF(C2:C32;”> 0″;B2:B32)

fungsi sumif diatas, akan menjumlahkan angka di range B2:B32, klo ada nilai di range C2:C32 (inget, B2:Bx dijumlahkan klo ada nilai di C2:Cx). dengan menggunakan criteria “> 0” (lebih dari nol, alias ada nilai), maka kita dapat menghitung pada range di C yang punya nilai saja. kenapa menggunakan range C2:C32? karena ada bulan yang memliki jumlah hari 31, sehingga jika hanya dibuat sampai range C31, jika ada tanggal 31 tidak akan ikut dihitung 🙂

thats all, mudah bukan?!

[EXCELL] nampilin hari berdasarkan tanggal

selama beberapa hari ane nyari fungsi untuk menampilkan hari berdasarkan tanggal di kompas, akhirnya ketemu juga. beberapa bln ini emg ane bikin report mingguan dan bulanan untuk jadwal shift dan absensi. semua dilakukan secara manual, menggunakan fungsi copy paste excell.

memang mudah, namun kurang praktis, apalagi bwt ane yang males ngetik or sekadar copy paste. pengennya semua serba instant. nyari2 fungsi untuk mendapatkan hari di excell lumayan susah (bagi saia). ane pikir nama fungsinya Day, DayOfWeek or yg laen. setelah ketik sana, ketik sini fungsi yang di cari g ketemu.

hari ini kebetulan lagi buat fungsi di excell bwat nampilin LAC dan CI dari CDR (call data record pelanggan three). secara kebetulan ane dapetin fungsi bwat nampilin hari pada tanggal tertentu. ternyata fungsi tersebut sembunyi di fungsi TEXT. completenya spt dbwh ini:

TEXT(data; format)

contoh kasus.
untuk menampilkan hari pada tanggal 17/03/2008, u bisa menuliskan:

=TEXT(17/03/2008; “dddd”)

format “dddd” diatas akan menampilkan nama hari berdasarkan setting local, misal jika menggunakan setting indonesia, akan menampilkan hari sabtu. jika setting region dirubah ke inggris, maka yang ditampilkan adalah saturday

demikan dari ane, semoga bermanfaat

psst:
fungsi text jg bs digunakan untuk memformat hari penjumlahan tanggal, misalkan untuk mencari durasi pemakaian telp dsb 😀

bikin folder, via cmd prompt windows

daripada cape cape, klik kanan new folder – ketik nama folder, apalagi klo bikin ratusan folder dengan nama sama, cuma beda di urutan, mendingan pake cara ini deh. simple but powerfull.

format: FOR /L %var IN (START, STEP, FINISH) DO [command prompt]

contoh: for /L %i in (1,1,100) do mkdir %i

ket:
%i merupakan variabel yang akan di urutkan nilainya
1,1,100 -> 1 merupakan nilai awal, 1 berikutnya merupakan step (bisa ajah ente loncat 3, 5 dst), 100 merupakan nilai akhir
do mkdir %i -> merupakan command yang akan membuat folder nilai 1 sampai 100 (nama folder 1 s/d 100)

thats all 😀

OGM to AVI Converter

sesuai dengan namanya, OGM to AVI converter merupakan tools yang dapat digunakan untuk mengkonversi file dalam format ogm ke format avi. tools ini hanya tersedia untuk sistem operasi windows. ukuran installer cukup kecil, sekitar 8.5 Mb. tools ini masih dalam versi beta 6 dan bisa di download di sourceforge.net.

dengan interface yang simple, tools ini cukup mudah untuk digunakan. kita tinggal mendrag dan drop file ogm ke dalam dialog box, yang tersedia. kita juga dapat memilih bitrate suara yang dihasilkan dengan menset ke: 32, 48, 64 128 dst. untuk merubah ke file avi, kita tinggal menekan tombol convert file. secara default, nama dan direktori file avi yang dihasilkan, akan sama dengan file yang diconvert. misalnya: file aseli inuyasha-01.ogm, maka didirektori yang sama akan ada file inuyasha-01.avi.

satu-satunya kelemahan, ketika mencoba tools ini adalah, tidak adanya setting untuk memilih audio yang ada. kebetulan file yang saya gunakan untuk konversi menggunakan 2 audio stream, yaitu english dan japanese. kedua stream audio tersebut masih ada dan dapat didengarkan, sama seperti file aselinya. terlepas dari itu semua, tools ini layak untuk dicoba, karena kemudahan dan lisensi yang free. (ag)