Wa'alaikum salam warohmatullohi wabarokatuhu..
Mungkin ini maksudnya yang mau di sum yang tanggal transaksinya bulan januari
saja ya...
coba formulanya ditambahkan kriteria pada tgl transaksi juga, menjadi spt ini:
=SUMIFS($E:$E,$D:$D,">="&$H7,$D:$D,"<="&$I7,$C:$C,"<="&$C$17)
copas kebawah
Hai 007;
Coba pakai Remove Duplicates
Caranya,
- Block seluruh data
- Pada menu data -> grup data tools -> click Remove Duplicates
- Pada dialog yang muncul, beri contreng hanya pada tiga parameter tsb
- lalu OK
Wassalam
~ Bagus ~
- Original Message -
From: James Bond myname...
Pak indra;
Coba untuk formula yang bagian countif diganti dengan sumproduct spt ini:
=CONCATENATE(YEAR(C2);"/";MONTH(C2);"/";6;"/";COUNTIF($A$2:A2,A2))
menjadi:
=CONCATENATE(YEAR(C2);"/";MONTH(C2);"/";6;"/";SUMPRODUCT((MONTH(C2)=MONTH($C$2:C2))*(YEAR(C2)=YEAR($C$2:C2
Copas kebawah
Wassala
pp..! sejauh ini berjalan lancar.. thanks suhu.. saya akan kembali jika
sewaktu-waktu ada koreksi kembali..
semoga hari mu menyenangkan karena membantu sesama.
amin.
Pada Jumat, 28 November 2014 8:15, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
ubject: Re: [belajar-excel] Rumus gabungan Vlookup
hehehe...
terus munculnya si nol kapan ya?
Wassalam,
Kid.
2014-11-29 11:43 GMT+11:00 'Bagus' ba...@kingjim.co.id [belajar-excel]
:
Oom Kid;
Ini Oom File-nya..
Wassalam
~ Bagus ~
GMT+07:00 'Bagus' ba...@kingjim.co.id [belajar-excel]
:
Oom Kid;
Ini Oom File-nya..
Wassalam
~ Bagus ~
- Original Message -
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
To: BeExcel
Sent: Saturday
da Jumat, 28 November 2014 7:46, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
Mas Fahmi..
Xl saya juga versi 2007..
Setelah file lampiran saya buka memang hasilnya 0..
coba select range F43:F73 lalu tekan tombol F2 dan Enter maka akan muncu
sudah ada formulanya dan menghasilkan nol tersebut
mas?
;)
Wassalam,
Kid
2014-11-28 11:49 GMT+11:00 'Bagus' ba...@kingjim.co.id [belajar-excel]
:
Mas Fahmi..
Xl saya juga versi 2007..
Setelah file lampiran saya buka memang hasilnya 0..
c
Pak Pukka;
Coba ditambahin fungsi round(),
misal:
=IF($H7>=70,VLOOKUP($H7,Sheet1!$A$2:$C$32,2),0)
menjadi:
=IF($H7>=70,VLOOKUP(Round($H7,0),Sheet1!$A$2:$C$32,2),0)
Wassalam
~ Bagus ~
- Original Message -
From: Pukka Hamonangan Pardede angan_l...@yahoo.co.id [belajar-excel]
November 2014 21:00, Fahmi Syaifullah
menulis:
Yang terhormat, pak Agus
terima kasih atas bantuannya, sangat berguna pak.
wassalam.
Fahmi
Pada Selasa, 25 November 2014 19:47, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
Mas
Pak Fathir;
Coba pakai Conditional Formatting;
Langkahnya:
1. Click pada Sheet2, Block data pada C2:C5
2. masuk pada name manager, lalu kasih nama. misal Sdh_Byr
3. Block data pada sheet1, B3:D3
4. Masuk pada Conditional Formatting, pilih Use a formula bla..bla..
5. Masukkan formula berikut: =MAT
Vlookup
yang terhormat pak bagus.
saya sudah coba formulanya, tapi kok hasinya nol yaa?
mohon bantuannya .
wassalam
fahmi syaifullah
Pada Selasa, 25 November 2014 13:13, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
Mas F
Bu Emi;
Coba begini:
=IF(ISNUMBER(A3),IF(AND(A3<>"",F3<>""),F3,G2),"")
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Emi emilestarik...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, November 25, 2014 1:55 PM
Subject: [belajar-ex
Mas Fahmi;
Coba begini:
=IFERROR(E43*INDEX($C$4:$G$15,MATCH(RIGHT(D43,6),$A$4:$A$15,0),MATCH(VLOOKUP(LEFT(D43,1),$I$6:$J$10,2,0),$C$3:$G$3,0)),0)
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Fahmi Syaifullah fahmisyaifulla...@yahoo.com [belajar-excel]
To: bel
Bu Emi;
Coba begini:
=SUBSTITUTE(A2,"PT ABC ","")
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Emi emilestarik...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, November 25, 2014 8:42 AM
Subject: [belajar-excel] Memisahkan nama
Pak Indra;
Coba Begini:
=C2&"/"&TEXT(A2,"yy/")&COUNTIF($C$2:C2,C2)
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Indra Sanjaya indradjaja8...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Sunday, November 23, 2014 6:35 PM
Subject: [bel
Wa'alaykum salam warohmatullohi wabarokatuhu..
Iya benar pak, coba ditambahkan untuk CHP pada name manager.
Utk Refers to: nya sila disesuaikan range-nya dar A.. sampai D..
Wassalam
~ Bagus ~
- Original Message -
From: Samsudin samsu...@bayan.com.sg [belajar-excel]
To: belaj
Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Samsudin;
File terlampir ada penambahan helper pada A1
Sila dilihat juga pada Name Manager
Wassalam
~ Bagus ~
- Original Message -
From: Samsudin samsu...@bayan.com.sg [belajar-excel]
To: belajar-excel@yahoogroups.com
Pak AP;
Coba begini:
=MIN(1,SUMPRODUCT(($A4=Distribusi!$B$3:$B$161)*(E$3=Distribusi!$D$1:$F$1)*(Distribusi!$D$3:$F$161)))
Copas kenanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: purwadi_a...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent:
Coba begini Pak;
Misal data ada di A2.
=DateValue(A2)
Wassalam
~ Bagus ~
- Original Message -
From: 'Subroto, Matzen' matzen.subr...@akzonobel.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 12:41 PM
Subject: RE: [belajar-excel] Meru
Wa'alaykum salam warohmatullohi wabarkatuhu..
Pak Samsudin;
Coba begini:
=A1-day(A1)+1
Wassalam
~ Bagus ~
- Original Message -
From: Samsudin samsu...@bayan.com.sg [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 12:19 PM
Subject: RE: [b
Pak Broto;
Misal Data ada di A2, Coba begini:
=Text(A2,"m/d/")
Wassalam
~ Bagus ~
- Original Message -
From: 'Subroto, Matzen' matzen.subr...@akzonobel.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 10:28 AM
Subject: [belajar-e
Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Samsudin;
Sila lihat file terlampir, Helper ada dikolom E
Wassalam
~ Bagus ~
- Original Message -
From: Samsudin samsu...@bayan.com.sg [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 9:1
Oom Kid;
Wow...
Kereenn...
Wassalam
~ Bagus ~
- Original Message -
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
To: BeExcel
Sent: Thursday, November 13, 2014 4:00 PM
Subject: Re: [belajar-excel] Membuat summary item
Hai Suyono,
Coba array formula :
Coba begini, Array formula ya..:
{=INDEX(B$3:B$10,SMALL(IF(($C$3:$C$10>=1)*($C$3:$C$10<=5),ROW($B$3:$B$10)-ROW($B$2)),ROW(1:1)))}
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Ferdinan Imers ferdina...@gmail.com [belajar-excel]
To: belajar-excel@yah
Pak Suyono;
File terlampir ada 3 Helper.
Sila diutak-atik utk menyederhanakannya lagi...
Wassalam
~ Bagus ~
- Original Message -
From: SUYONO reza.suy...@lge.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, November 12, 2014 5:47 PM
Subject: [belaj
Pak Suhendar;
Coba begini:
Soal no.1 (pake if-if-an)
=IF(F18<70,"non",IF(F18<=100,"A",IF(F18<=150,"B",IF(F18<=200,"C",IF(F18<=300,"D","E")
Copas kebawah
Soal no.2
=COUNTIF($G$18:$G$27,C6)
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Suhendra St susu_bel
Pak ASL;
Boleh ikutan ya...
Mungkin pada Array formula "pencarian" yang berbunyi:
=SUM(IF(ISERROR(SEARCH(E4,OFFSET(data!A2,1,F4,MAX(data!A3:A21),1),1)),0,1))
perlu disesuaikan jumlah datanya menjadi:
=SUM(IF(ISERROR(SEARCH(E4,OFFSET(data!A2,1,F4,MAX(data!A3:A64),1),1)),0,1))
Wassalam
~ Bagu
Maaf pak, kebalik minusnya...
ini yang benar
Wassalam
~ Bagus ~
- Original Message -
From: 'Bagus' ba...@kingjim.co.id [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, November 05, 2014 1:42 PM
Subject: Re: [belajar-excel] conditional forma
Pak Kausar;
Sila lihat file terlampir..
Semoga sesuai harapan..
Wassalam
~ Bagus ~
- Original Message -
From: Abid Kausar muha...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, November 05, 2014 1:06 PM
Subject: [belajar-excel] conditional f
biasa
On Wednesday, November 5, 2014 8:07 AM, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" wrote:
Pak Haris;
Sila lihat file terlampir...
Semoga sesuai harapan
Wassalam
~ Bagus ~
- Original Message -
From: ris ha ha
Pak Haris;
Sila lihat file terlampir...
Semoga sesuai harapan
Wassalam
~ Bagus ~
- Original Message -
From: ris ha har1sn.ex...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, November 04, 2014 2:13 PM
Subject: [***SPAM*** Score/Req: 05.20/5.0]
Pak Haris;
Coba pada Hasil 1, menjadi begini:
=IFERROR(INDEX(LEFT($A$2:$A$21,1),MATCH(0,COUNTIF($B$1:B1,LEFT($A$2:$A$211)),0)),"")
=> Array formula ya.. lalu copas kebawah
dan jml menjadi begini:
=IF(LEN(B2),COUNTIF($A$2:$A$21,B2&"*"),"")
Copas kebawah
Wassalam
~ Bagus ~
- Original Me
Pak Bams;
Coba begini pada F7:
=IF(C7="Saldo
Awal",VLOOKUP($C$3,DaftarBarang,5),IF(TRANSAKSI!Q12>0,TRANSAKSI!Q12,IF(E7<>"",TRANSAKSI!AE12,0)))
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: AKBAR Latif bams_99...@yahoo.co.id [belajar-excel]
To: belajar-excel@ya
Pak depi;
Coba begini:
=LEFT(C4,FIND(" ",C4,1)-1)+1&" -
"&MID(C4,FIND("-",C4,1)+2,LEN(C4)-FIND("-",C4,1)+1)+1
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: depi Hariyanto depi.hari_ya...@yahoo.co.id [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Th
dear mr bagus,
saya sudah coba tapi tidak bisa.
tks.
Pada Selasa, 28 Oktober 2014 15:46, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
Pak Depi;
Coba begini, Array formula ya:
=IFERROR(INDEX(C$6:C$12,SMALL(IF($E$6:$E$12<>$Q$
Pak Gunadi;
Coba begini:
=MID(K7,FIND(1,K7,1),FIND("_",K7,1)-FIND(1,K7,1))
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: gunadi mardiono gunad...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, October 28, 2014 3:53 PM
Subject: [be
Pak Depi;
Coba begini, Array formula ya:
=IFERROR(INDEX(C$6:C$12,SMALL(IF($E$6:$E$12<>$Q$6,ROW($C$6:$C$12)-ROW($C$5)),ROW(1:1))),"")
Akhiri dengan menekan tiga tombol (Ctrl+Shift+Enter)
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: depi Hariyanto depi
Pak Abudi;
Coba begini:
=IF(OR(E5>D5,F5>D5,E5>F5),E5,F5)
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Abudi Doank abudi.do...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, October 27, 2014 12:11 PM
Subject: [belajar-excel] Co
Pak Haidar;
Sila lihat file terlampir..
Wassalam
~ Bagus ~
- Original Message -
From: Haidar Zen haidarlove...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, October 27, 2014 11:31 AM
Subject: [belajar-excel] Sortir Data 1 Kolom ke [1 Attachmen
Mbak Shenly;
Coba begini:
=IF(AND(C2>=10,COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)=1),15,0)
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Shenly shenly_excelma...@yahoo.co.id [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Sunday, October 26, 2014 9:
Mbak Emi;
Sila lihat file terlampir...
oia, Helpernya saya pindah dikolom A.
Wassalam
~ Bagus ~
- Original Message -
From: Emi emilestarik...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, October 24, 2014 4:50 PM
Subject: [belajar-excel] Menam
Mbak Tien;
Jika maunya sama persis seperti yang dicontohkan:
A3 = D1
B3 = D2
.
.
.
G3 = D7
selain itu g boleh..
Coba begini, Array formula ya..
=IF(ISERROR(SUM(1/INDEX(LEFT(A3:G3,2)="D"&COLUMN(A3:G3),1,))),"","H")
Akhiri dengan menekan 3 tombol (CSE)
Wassalam
~ Bagus ~
- Original Me
sudah dicoba dengan =B1&C1
atau =Concatenate(B1,Ci)
Wassalam
~ Bagus ~
- Original Message -
From: ari05aug...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, October 21, 2014 4:16 PM
Subject: [belajar-excel] Bls: Mgabungkan kolom
Mhn maa
:"'Bagus' ba...@kingjim.co.id [belajar-excel]"
Tanggal:Sen, 20 Okt 2014 pada 18:21
Judul:Re: [belajar-excel] Mengolah data vertikal menjadi
horizontal [1 Attachment]
Mbak Tiny;
P
Mbak Tiny;
Pakai helper boleh ya...
Sila lihat file terlampir..
Wassalam
~ Bagus ~
- Original Message -
From: hartini dewi hartini...@yahoo.co.id [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, October 20, 2014 4:13 PM
Subject: [belajar-excel] Mengolah
Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak matzen;
Misal Header mulai A1,
Coba ini pada B2:
=IF(MID(B$1,3,2)*100<=$A2,MID($B$1,3,2)*100,0)
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: 'Subroto, Matzen' matzen.subr...@akzonobel.com [belajar-ex
Wa'alaykum salam warohmatullohi wabarokatuhu.
Pak Samsudin;
Utk soal no. 1, coba begini:
=INDEX($D$3:$AH$12,MATCH($B$14,$B$3:$B$12,0),MATCH(DAY(B15),$D$2:$AH$2,0))
Copas kebawah
Utk soal no. 2, misal data mulai A2
=SUBSTITUTE(A2,"-"," ")
Copas kebawah
Wassalam
~ Bagus ~
- Original Me
Pak Samsudin;
Untuk yang no. 1
Coba begini:
=Text(A6,"")&"/"&Roman(Month(C6))&"/"&Mid(J6,Find("
",J6,1)+1,99)&"-"&F6&"/"&Year(C6)
Copas kebawah
Untuk yana no. 2
Mohon maaf, saya belum paham maksudnya...
mungkin bisa sertakan contoh hasil kerja manualnya (spt kata Oom Kid)
Wassalam
~ Bag
Pak Faiz;
Coba begini:
=COUNTA(B2:B100)
Wassalam
~ Bagus ~
- Original Message -
From: Husnul Faizin husnulfai...@yahoo.co.id [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Saturday, October 18, 2014 1:20 PM
Subject: [belajar-excel] formula hitung jumlah siswa
Pak Samsudin;
Ini adalah Array formula..
jadi setelah rumusnya dirubah jangan hanya ditekan Enter saja, tekan 3 tombol
(Ctrl+Shift+Enter)
Coba pada pada M5, tekan tombol F2.
lalu tekan tombol Ctrl dan tombol Shift, jangan dilepas dulu 2 tombol tsb.
(Ctrl+Shift)
Lalu tekan tombol Enter.
maka for
Coba untuk rujukan rangenya dijadikan absolute dengan diberi tanda dolar "$"
=OR(AND(COUNTA($L6:$N6)<3,SUM($L6:$N6)<=100),AND(COUNTA($L6:$N6)=3,SUM($L6:$N6)=100))
Wassalam
~ Bagus ~
- Original Message -
From: heru mulyono hmulyon...@gmail.com [belajar-excel]
To: belajar-exce
Wa'alaykum salam warohmatullohi wabarokatuhu
Pak Samsudin;
Sila lihat file terlampir
semoga sesuai harapan
Wassalam
~ Bagus ~
- Original Message -
From: Samsudin samsu...@bayan.com.sg [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Thursday, October 16, 2014 9:14
Coba begini:
=IF(COUNT(B8:Y8)=0,"E",IF(OR(COUNT(B8:M8)=0,COUNT(N8:Y8)=0),"D",IF(AA8>=10,"A",IF(AA8>=8,"B","C"
Wassalam
~ Bagus ~
- Original Message -
From: Nang Agus nanga...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, October 10, 2014
Wa'alaykum salam warohmatullohi wabarokatuhu..
Mas Agus R;
Coba formulanya dirubah sedikit, menjadi:
=IF(H$4<$E7,0,IF(H$4>$F7,0,IF((($F7-DAY($F7))-($E7-DAY($E7)))=0,$G7/1,$G7/(ROUNDUP((($F7-DAY($F7))-($E7-DAY($E7)))/7,0)
lalu copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Origina
Hai Topenk;
Coba begini, Array formula ya...
=IFERROR(INDEX($C$3:$C$22,SMALL(IF($D$3:$D$22=K$1,ROW($C$3:$C$22)-ROW($C$2)),ROW(1:1))),"")
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Topenk Baday topenk_ba...@yahoo.com [belajar-excel]
To: belajar-ex
Fak Firman;
Coba begini:
=LOOKUP(F2,C2:C49,B2:B49)
Atau begono:
=INDEX(B2:B49,MATCH(F2,C2:C49,1))
Wassalam
~ Bagus ~
- Original Message -
From: Firman Setiawan fsetiawan_r...@yahoo.co.uk [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, October 03, 2014 9:2
Mas Topenk;
Ini yang mau dimasukkan apanya ya..??
Wassalam
~ Bagus ~
- Original Message -
From: Topenk Baday topenk_ba...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Thursday, October 02, 2014 11:56 AM
Subject: [belajar-excel] Masuk ke kolom yang di
Pak Cep;
Coba begini, Array formula ya..
{=INDEX($B$4:$J$4,MATCH(0,COUNTIF($M$3:M3,$B$4:$J$4),0))}
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: 'cepwahyu' cepwa...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, September 30, 2014
beberapa cell
Bisa juga di-split dengan spasi langsung menjadi :
=TRIM(MID(SUBSTITUTE($D3,",",REPT(" ",99)),(COLUMNS($A:A)-1)*99+1,99))
Wassalam,
Kid.
2014-09-29 15:54 GMT+10:00 'Bagus' ba...@kingjim.co.id [belajar-excel]
:
=TRIM(SUBST
Hai MIB;
Coba begini:
=TRIM(SUBSTITUTE(MID(SUBSTITUTE($D3,",",REPT(",",99)),(COLUMNS($A:A)-1)*99+1,99),",",""))
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: ivan budiman ivanst...@yahoo.com [belajar-excel]
To: belajar excel
Sent: Monday, Septembe
Pak Syam;
Coba begini:
=IF(SUM(D5:E5)=0,"",IF(E5>=D5,"ok","no"))
atau:
=IF(OR(D5="",E5=""),"",IF(E5>=D5,"ok","no"))
Wassalam
~ Bagus ~
- Original Message -
From: Sam Mun samkaw...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Thursday, September 25, 20
Coba begini, Array formula ya..
=INDEX(LEFT($A$3:$A$10,5),MATCH(0,COUNTIF($E$2:E2,LEFT($A$3:$A$10,5)),0))
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: 'cepwahyu' cepwa...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, September 24
CW;
Coba begini:
=REPT("A ",MIN(1,COUNTIF($A$3:$A$10,E3&" A")))&REPT("G
",MIN(1,COUNTIF($A$3:$A$10,E3&" G")))&REPT("N ",MIN(1,COUNTIF($A$3:$A$10,E3&"
N")))&REPT("P ",MIN(1,COUNTIF($A$3:$A$10,E3&" P")))&REPT("Q
",MIN(1,COUNTIF($A$3:$A$10,E3&" Q*")))
Copas kebawah
Wassalam
~ Bagus ~
-
Anisa;
Coba begini:
=if(and(h16="pesan", or(g16<=c5, g16<=c6)), jika true, jika false)
Wassalam
~ Bagus ~
- Original Message -
From: Anisa Silado anisa_sil...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, September 22, 2014 10:13 AM
Subject: [
Pak haris;
coba begini, misal data ada di cell A1
=IF(--LEFT(A1,1)=0,"00"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),RIGHT(SUBSTITUTE(A1,"
",""),1),""),0,""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),RIGHT(SUBSTITUTE(A1," ",""),1),""),0,""))
Wassalam
~ Bagus ~
- Original Message
Pak Haris;
waktu menyimpan workbooknya pastikan extensinya bertype xlsm / xlsb
Wassalam
~ Bagus ~
- Original Message -
From: ris ha har1sn.ex...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, September 19, 2014 1:42 PM
Subject: [belajar-excel]
Benar pak, Char 10 adalah Enter, untuk membuat baris baru..
Coba begini:
Misal pada cell A1.
tekan tombol F2, lalu tekan tombol Alt+Enter.
lepas tombol Alt lalu Enter.
pada B1 tulis rumus =code(A1) lalu enter..
lihat hasilnya..
Wassalam
~ Bagus ~
- Original Message -
From: SUYO
Bagaimana kalo pakai kolom pak..?
sila lihat file terlampir..
Wassalam
~ Bagus ~
- Original Message -
From: 'cepwahyu' cepwa...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, September 17, 2014 3:32 PM
Subject: RE: [belajar-excel] Mengambil
CW (awas kebalik lho..)
Coba begini:
=INDEX(LEFT(Data2!$B$4:$B$7,LEN(Data2!$B$4:$B$7)-(FIND("/",Data2!$B$4:$B$7,1)-1)),MATCH(Data1!A12,Data2!$A$4:$A$7,0))
Copas kekanan
Khusus utk cell D10, tambahkan 1:
=INDEX(LEFT(Data2!$B$4:$B$7,LEN(Data2!$B$4:$B$7)-(FIND("/",Data2!$B$4:$B$7,1)-1)),MATCH(Data1
Pak Andreas;
mungkin pada baris ke-6 rumusnya harus pakai sum() semua kali ya..
coba rumus pada E6 diganti:
dari =B6/C6
menjadi =SUM(E4:E5)
Wassalam
~ Bagus ~
- Original Message -
From: andreas andreas andreas_...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Yono;
Pencipta rumus tsb adalah Oom Kid,
ini saya copas dari penjelasan Oom Kid ya..
Formula LookUp memiliki syntax :
=LookUp ( nilai_yang_di_lookup , array_data_lookup , [array_data_yang_diambil] )
[array_data_yang_diambil] jika dikosongkan ak
Mbak Joelinda;
Biasanya kalau read-only itu berhubungan dengan permissionnya..
Coba begini:
1. Click kanan pada folder tempat menyimpan share workbook tsb. lalu pilih
properties
2. Pada dialog yang muncul, click pada tab security
3. Click nama orang tsb pada jendela "group or user names:"
4. Li
Alhamdulillah,..
Sila lihat file lampiran..
Wassalam
~ Bagus ~
- Original Message -
From: fiant.on.msex...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, September 15, 2014 10:09 AM
Subject: [belajar-excel] lookup dengan output banyak [1 Attach
Warehouse;
Coba begini:
=INDEX(Base!$B$2:$B$4,MATCH(MID(A2,15,1),Base!$A$2:$A$4,0))
atau begini:
=VLOOKUP(MID(A2,15,1),Base!$A$2:$B$4,2)
atau begini:
=LOOKUP(2,1/(MID(A2,15,1)=Base!$A$2:$A$4),Base!$B$2:$B$4)
lalu copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Wewi
dan kolom lainnya tidak ada yang duplikat. namun
saya menemui kendala, mohon bimbingannya lagi. file terlampir
On Friday, September 12, 2014 1:57 AM, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" wrote:
Wa'alaykum salam warohmatullohi wabarokatu
Pak Sony;
File terlampir hanya bisa mengcover utk max 2 bulan tunggakan.
Wassalam
~ Bagus ~
- Original Message -
From: sony nurcahyo sony_...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, September 12, 2014 2:32 PM
Subject: Re: [belajar-excel]
Wa'alaykum salam warohmatullohi wabarokatuhu..
Mbak Fat;
Coba begini, Array formula ya:
=IFERROR(INDEX(master!C$2:C$428,SMALL(IF(TEXT(master!$B$2:$B$428,"[$-421]")=$C$3,ROW(master!$B$2:$B$428)-ROW(master!$B$1)),ROW(1:1))),"")
Copas kekanan dan kebawah.
Nb.
Pada kolom total, ganti rujukan In
Rumus Edate() agar bisa dikenali pada excel 2003, maka aktifkan dulu Analysis
ToolPak.
Caranya click menu Tools, lalu pilih Add-Ins, maka akan muncul dialog baru...
contreng pada Analysis ToolPak
lalu tekan OK
Wassalam
~ Bagus ~
- Original Message -
From: Nang Agus nanga...
Mbak Emi;
Coba begini:
=COUNTIFS($B$4:$B$16,$G4,C$4:C$16,">0")
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: Emi emilestarik...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, September 09, 2014 11:17 AM
Subject: [be
Pak Dicky;
Coba begini:
=If(Year(Edate($b4,$d4*Count($e4:e4)))=f$3,$e4,"")
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
- Original Message -
From: dicky zulkifli dicky.zulki...@yahoo.co.id [belajar-excel]
To: Belajar Excell
Sent: Tuesday, September 09, 2014 10:26 AM
Subje
Oooo...
Oom Kid;
baru tau kalo, Nilai data yang bertipe TEXT selalu lebih dari nilai data
bertipe NUMERIC
hehe.. makasih ilmunya Oom
btw kalo "GeJe" apaan ya...??
hehehe..
Wassalam
~ Bagus ~
- Original Message -
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
To: BeExcel
Mas Agung;
Coba pakai CF dengan 2 Rules;
Rule yang pertama dengan formula:
=AND(COUNTA($C$6:$C$100)<=COUNTA($I$6:$I$100),$I6=MAX($I$6:$I$100))
Rule yang kedua dengan formula:
=AND(COUNTA($C$6:$C$100)>=COUNTA($I$6:$I$100),$C6=MAX($C$6:$C$100))
File terlampir
Wassalam
~ Bagus ~
- Or
Mas Odong;
untuk yang ini: =sumproduct( 1/countifs ( a1:a10,a1:a10,b1:b10,b1:b10 ))
apa bisa dilampirkan contoh datanya..?
Wassalam
~ Bagus ~
- Original Message -
From: odong nando odongna...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Friday, Septemb
Risma;
Yang salah adalah define Namenya belum sesuai..
coba begini:
- buka sheet1 lalu click Name Manager
- Click Pilih Barang lalu click Edit
- Click pada refers to:
- Block data mulai A5:D16
Wassalam
~ Bagus ~
- Original Message -
From: 'Rismayanti Darwis
Risma;
Wa'alaykum salam warohmatullohi wabarokatuhu.
Dari file yg anda kirim:
Seharusnya validasinya ada di kolom C (kode barang) bukan dikolom D (nama
barang)
pada define name untuk barang, coba ganti "refers to" menjadi =Sheet1!$A$5:$B$16
sila lihat file terlampir
Wassalam
~ Bagus ~
Wa'alaykum salam warohmatullohi wabarokatuhu..
Coba begini pada B2, (Array formula ya):
=IFERROR(INDEX($A$2:$A$12,MATCH(SUM(COUNTIF($A$2:$A$12,B$1:B1)),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12),0)),"")
akhiri dengan CSE
Copas kebawah
Wassalam
~ Bagus ~
- Original Message -
From: ris
YR;
Seperti ini kah..??
Wassalam
~ Bagus ~
- Original Message -
From: Yusril Ramadani yusrilramad...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, September 03, 2014 9:50 AM
Subject: [belajar-excel] Validasi angka input [1 Attachment]
Pak Fendi;
Coba begini (array formula ya):
=Index(b5:g5, mod(max(frequency(match(b5:g5,b5:g5,0),
row(1:1))*100+row(1:1)),100))
akhiri dengan CSE
Wassalam
~ Bagus ~
- Original Message -
From: Fendi S fen...@rocketmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
S
Hai 007;
Sila lihat file terlampir
Wassalam
~ Bagus ~
- Original Message -
From: James Bond myname...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, September 02, 2014 10:50 PM
Subject: [belajar-excel] menjumlahkan data per kolom berdasarkan a
survey nya sama maka juga
akan berwarna...
Mohon pak Bagus bisa melihat langsung datanya...
terima kasih
salam
supri
On 9/2/14, 'Bagus' ba...@kingjim.co.id [belajar-excel]
wrote:
> Pak Supri;
>
> Coba formula CF nya diganti spt ini:
> =CountifS
Pak Supri;
Coba formula CF nya diganti spt ini:
=CountifS ( $c:$c,c6, $f:$f, f6 ) > 1
Wassalam
~ Bagus ~
- Original Message -
From: supri anto supriantohdl07ex...@gmail.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Tuesday, September 02, 2014 1:08 PM
Subjec
Hai Yudha;
Pada menu view, pilih toolbar, pilih Drawing:
akan muncul spt ini:
lalu pilih select multiple object (gambar anak panah) paling kanan.
akan muncul dialog spt ini:
Click select all.. lalu OK.
maka semua object dlm worksheet sudah ter-select.
Lalu tekan tombol Delete..
semoga berhasi
kendala disisi mananya yaa
terima kasih
Terlampir data yang saya coba:
Pada Rabu, 27 Agustus 2014 5:38, "'Bagus' ba...@kingjim.co.id
[belajar-excel]" menulis:
Subhanalloh...
Oom Kid..
Mantabb Penjelasannya..
Alhamdulillah.
Subhanalloh...
Oom Kid..
Mantabb Penjelasannya..
Alhamdulillah..
dapat ilmu lagi...
Wassalam
~ Bagus ~
- Original Message -
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
To: BeExcel
Sent: Wednesday, August 27, 2014 7:27 PM
Subject: Re: [belajar-excel] Cara Memisa
Hai Emi;
Pakai helper boleh ya..
Sila lihat file terlampir
Wassalam
~ Bagus ~
- Original Message -
From: Emi emilestarik...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Wednesday, August 27, 2014 3:32 PM
Subject: Re: [belajar-excel] Transpose data
Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Aziz;
Misal data ada di cell A1;
Untuk mengambil data,
tanggal: =date(year(a1),month(a1),day(a1))
Jam:=time(hour(a1),minute(a1),second(a1))
Wassalam
~ Bagus ~
- Original Message -
From: abd aziz aziez_...@yahoo.com
GMT+10:00 'Bagus' ba...@kingjim.co.id [belajar-excel]
:
WEEKDAY(C11)
Mas Agung;
coba begini pada E11:
=IF(H11="cuti
tahunan",NETWORKDAYS(B11,C11)+IF(OR(WEEKDAY(C11)
Mas Agung;
Bisa mas..
Sila lihat file terlampir
Wassalam
~ Bagus ~
- Original Message -
From: agungdotco...@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Thursday, August 21, 2014 11:35 AM
Subject: Re: Bls: [belajar-excel] Rumus Hitung Cuti Tahunan
1 - 100 dari 159 matches
Mail list logo
