XFD adalah kolom terakhir pada excel. jadi ini adalah upaya antisipasi supaya formula tersebut tetap bisa berfungsi dinamis sesuai dengan penambahan data yang akan terjadi . adapun counta adalah untuk menghitung jumlah sel berisi data baik angka ataupun karakter pada range yang ingin dihitung jumlah datanya. lain lain bisa ditanyakan lebih lanjut ,insya Allah kalau memang bisa dan sempat dengan senang hati saya akan membantu.
wassalam Ghozi Alkatiri -------------------------------------------- Pada Jum, 3/2/17, Modby Matsnawabi [email protected] [belajar-excel] <[email protected]> menulis: Judul: Re: [belajar-excel] Rumus Transpose Otomatis Kepada: "belajar-excel" <[email protected]> Tanggal: Jumat, 3 Februari, 2017, 3:13 PM Terima kasih Pak Ghozi for quick respon & solusinya. mau tanya untuk rumus ini maksudnya apa yah? COUNTA(Contoh!$E$3:$XFD$3))+1) Kenapa pake XFD3? padahal nga ada cell kesana? On Fri, Feb 3, 2017 at 1:03 PM, ghozi alkatiri [email protected] [belajar-excel] <[email protected]> wrote: di cell A2 =INDEX(Contoh!B$5:B$15;TRUNC( ROW(1:1)/(COUNTA(Contoh!$E$3:$ XFD$3)-1))+1) copy sampai C2 copy ke bawah sampai yang diinginkan di cell D2 =OFFSET(Contoh!$D$2;COLUMN(A: A);MOD(ROW(1:1)-1;COUNTA( Contoh!$E$3:$XFD$3))+1) copy sampai F2 copy ke bawah sampai yang diinginkan selanjutnya lihat di attachment sheet solusi wassalam Ghozi Alkatiri ------------------------------ -------------- Pada Kam, 2/2/17, Modby Matsnawabi [email protected] [belajar-excel] <[email protected] > menulis: Judul: [belajar-excel] Rumus Transpose Otomatis [1 Attachment] Kepada: "belajar-excel" <[email protected] > Tanggal: Kamis, 2 Februari, 2017, 5:09 PM Selamat Sore, Para expert Excel, saya ada case mau transpose 500 Row data yang memiliki 150 colom data. klo row nya cuma sedikit masih sangup manual, tapi ini 500 lumayan gempor..contoh case saya lampirkan. Untuk member lain yang sudah pernah sharing dan sudah ada pemecahannya tolong di share solusinya. Terima kasih, salam.. #yiv6900962383 #yiv6900962383 -- #yiv6900962383ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial; margin:10px 0;padding:0 10px;} #yiv6900962383 #yiv6900962383ygrp-mkp hr { border:1px solid #d8d8d8;} #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd { color:#628c2a;font-size:85%; font-weight:700;line-height: 122%;margin:10px 0;} #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383ads { margin-bottom:10px;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad { padding:0 0;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad p { margin:0;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad a { color:#0000ff;text-decoration: none;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc { font-family:Arial;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc #yiv6900962383hd { margin:10px 0px;font-weight:700;font-size: 78%;line-height:122%;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc .yiv6900962383ad { margin-bottom:10px;padding:0 0;} #yiv6900962383 #yiv6900962383actions { font-family:Verdana;font-size: 11px;padding:10px 0;} #yiv6900962383 #yiv6900962383activity { background-color:#e0ecee; float:left;font-family: Verdana;font-size:10px; padding:10px;} #yiv6900962383 #yiv6900962383activity span { font-weight:700;} #yiv6900962383 #yiv6900962383activity span:first-child { text-transform:uppercase;} #yiv6900962383 #yiv6900962383activity span a { color:#5085b6;text-decoration: none;} #yiv6900962383 #yiv6900962383activity span span { color:#ff7900;} #yiv6900962383 #yiv6900962383activity span .yiv6900962383underline { text-decoration:underline;} #yiv6900962383 .yiv6900962383attach { clear:both;display:table;font- family:Arial;font-size:12px; padding:10px 0;width:400px;} #yiv6900962383 .yiv6900962383attach div a { text-decoration:none;} #yiv6900962383 .yiv6900962383attach img { border:none;padding-right:5px; } #yiv6900962383 .yiv6900962383attach label { display:block;margin-bottom: 5px;} #yiv6900962383 .yiv6900962383attach label a { text-decoration:none;} #yiv6900962383 blockquote { margin:0 0 0 4px;} #yiv6900962383 .yiv6900962383bold { font-family:Arial;font-size: 13px;font-weight:700;} #yiv6900962383 .yiv6900962383bold a { text-decoration:none;} #yiv6900962383 dd.yiv6900962383last p a { font-family:Verdana;font- weight:700;} #yiv6900962383 dd.yiv6900962383last p span { margin-right:10px;font-family: Verdana;font-weight:700;} #yiv6900962383 dd.yiv6900962383last p span.yiv6900962383yshortcuts { margin-right:0;} #yiv6900962383 div.yiv6900962383attach-table div div a { text-decoration:none;} #yiv6900962383 div.yiv6900962383attach-table { width:400px;} #yiv6900962383 div.yiv6900962383file-title a, #yiv6900962383 div.yiv6900962383file-title a:active, #yiv6900962383 div.yiv6900962383file-title a:hover, #yiv6900962383 div.yiv6900962383file-title a:visited { text-decoration:none;} #yiv6900962383 div.yiv6900962383photo-title a, #yiv6900962383 div.yiv6900962383photo-title a:active, #yiv6900962383 div.yiv6900962383photo-title a:hover, #yiv6900962383 div.yiv6900962383photo-title a:visited { text-decoration:none;} #yiv6900962383 div#yiv6900962383ygrp-mlmsg #yiv6900962383ygrp-msg p a span.yiv6900962383yshortcuts { font-family:Verdana;font-size: 10px;font-weight:normal;} #yiv6900962383 .yiv6900962383green { color:#628c2a;} #yiv6900962383 .yiv6900962383MsoNormal { margin:0 0 0 0;} #yiv6900962383 o { font-size:0;} #yiv6900962383 #yiv6900962383photos div { float:left;width:72px;} #yiv6900962383 #yiv6900962383photos div div { border:1px solid #666666;height:62px;overflow: hidden;width:62px;} #yiv6900962383 #yiv6900962383photos div label { color:#666666;font-size:10px; overflow:hidden;text-align: center;white-space:nowrap; width:64px;} #yiv6900962383 #yiv6900962383reco-category { font-size:77%;} #yiv6900962383 #yiv6900962383reco-desc { font-size:77%;} #yiv6900962383 .yiv6900962383replbq { margin:4px;} #yiv6900962383 #yiv6900962383ygrp-actbar div a:first-child { margin-right:2px;padding- right:5px;} #yiv6900962383 #yiv6900962383ygrp-mlmsg { font-size:13px;font-family: Arial, helvetica, clean, sans-serif;} #yiv6900962383 #yiv6900962383ygrp-mlmsg table { font-size:inherit;font:100%;} #yiv6900962383 #yiv6900962383ygrp-mlmsg select, #yiv6900962383 input, #yiv6900962383 textarea { font:99% Arial, Helvetica, clean, sans-serif;} #yiv6900962383 #yiv6900962383ygrp-mlmsg pre, #yiv6900962383 code { font:115% monospace;} #yiv6900962383 #yiv6900962383ygrp-mlmsg * { line-height:1.22em;} #yiv6900962383 #yiv6900962383ygrp-mlmsg #yiv6900962383logo { padding-bottom:10px;} #yiv6900962383 #yiv6900962383ygrp-msg p a { font-family:Verdana;} #yiv6900962383 #yiv6900962383ygrp-msg p#yiv6900962383attach-count span { color:#1E66AE;font-weight:700; } #yiv6900962383 #yiv6900962383ygrp-reco #yiv6900962383reco-head { color:#ff7900;font-weight:700; } #yiv6900962383 #yiv6900962383ygrp-reco { margin-bottom:20px;padding: 0px;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov li a { font-size:130%;text- decoration:none;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov li { font-size:77%;list-style-type: square;padding:6px 0;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov ul { margin:0;padding:0 0 0 8px;} #yiv6900962383 #yiv6900962383ygrp-text { font-family:Georgia;} #yiv6900962383 #yiv6900962383ygrp-text p { margin:0 0 1em 0;} #yiv6900962383 #yiv6900962383ygrp-text tt { font-size:120%;} #yiv6900962383 #yiv6900962383ygrp-vital ul li:last-child { border-right:none !important; } #yiv6900962383 #yiv1964091577 #yiv1964091577 -- #yiv1964091577ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;} #yiv1964091577 #yiv1964091577ygrp-mkp hr { border:1px solid #d8d8d8;} #yiv1964091577 #yiv1964091577ygrp-mkp #yiv1964091577hd { color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;} #yiv1964091577 #yiv1964091577ygrp-mkp #yiv1964091577ads { margin-bottom:10px;} #yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad { padding:0 0;} #yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad p { margin:0;} #yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad a { color:#0000ff;text-decoration:none;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ygrp-lc { font-family:Arial;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ygrp-lc #yiv1964091577hd { margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ygrp-lc .yiv1964091577ad { margin-bottom:10px;padding:0 0;} #yiv1964091577 #yiv1964091577actions { font-family:Verdana;font-size:11px;padding:10px 0;} #yiv1964091577 #yiv1964091577activity { background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;} #yiv1964091577 #yiv1964091577activity span { font-weight:700;} #yiv1964091577 #yiv1964091577activity span:first-child { text-transform:uppercase;} #yiv1964091577 #yiv1964091577activity span a { color:#5085b6;text-decoration:none;} #yiv1964091577 #yiv1964091577activity span span { color:#ff7900;} #yiv1964091577 #yiv1964091577activity span .yiv1964091577underline { text-decoration:underline;} #yiv1964091577 .yiv1964091577attach { clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;} #yiv1964091577 .yiv1964091577attach div a { text-decoration:none;} #yiv1964091577 .yiv1964091577attach img { border:none;padding-right:5px;} #yiv1964091577 .yiv1964091577attach label { display:block;margin-bottom:5px;} #yiv1964091577 .yiv1964091577attach label a { text-decoration:none;} #yiv1964091577 blockquote { margin:0 0 0 4px;} #yiv1964091577 .yiv1964091577bold { font-family:Arial;font-size:13px;font-weight:700;} #yiv1964091577 .yiv1964091577bold a { text-decoration:none;} #yiv1964091577 dd.yiv1964091577last p a { font-family:Verdana;font-weight:700;} #yiv1964091577 dd.yiv1964091577last p span { margin-right:10px;font-family:Verdana;font-weight:700;} #yiv1964091577 dd.yiv1964091577last p span.yiv1964091577yshortcuts { margin-right:0;} #yiv1964091577 div.yiv1964091577attach-table div div a { text-decoration:none;} #yiv1964091577 div.yiv1964091577attach-table { width:400px;} #yiv1964091577 div.yiv1964091577file-title a, #yiv1964091577 div.yiv1964091577file-title a:active, #yiv1964091577 div.yiv1964091577file-title a:hover, #yiv1964091577 div.yiv1964091577file-title a:visited { text-decoration:none;} #yiv1964091577 div.yiv1964091577photo-title a, #yiv1964091577 div.yiv1964091577photo-title a:active, #yiv1964091577 div.yiv1964091577photo-title a:hover, #yiv1964091577 div.yiv1964091577photo-title a:visited { text-decoration:none;} #yiv1964091577 div#yiv1964091577ygrp-mlmsg #yiv1964091577ygrp-msg p a span.yiv1964091577yshortcuts { font-family:Verdana;font-size:10px;font-weight:normal;} #yiv1964091577 .yiv1964091577green { color:#628c2a;} #yiv1964091577 .yiv1964091577MsoNormal { margin:0 0 0 0;} #yiv1964091577 o { font-size:0;} #yiv1964091577 #yiv1964091577photos div { float:left;width:72px;} #yiv1964091577 #yiv1964091577photos div div { border:1px solid #666666;height:62px;overflow:hidden;width:62px;} #yiv1964091577 #yiv1964091577photos div label { color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;} #yiv1964091577 #yiv1964091577reco-category { font-size:77%;} #yiv1964091577 #yiv1964091577reco-desc { font-size:77%;} #yiv1964091577 .yiv1964091577replbq { margin:4px;} #yiv1964091577 #yiv1964091577ygrp-actbar div a:first-child { margin-right:2px;padding-right:5px;} #yiv1964091577 #yiv1964091577ygrp-mlmsg { font-size:13px;font-family:Arial, helvetica, clean, sans-serif;} #yiv1964091577 #yiv1964091577ygrp-mlmsg table { font-size:inherit;font:100%;} #yiv1964091577 #yiv1964091577ygrp-mlmsg select, #yiv1964091577 input, #yiv1964091577 textarea { font:99% Arial, Helvetica, clean, sans-serif;} #yiv1964091577 #yiv1964091577ygrp-mlmsg pre, #yiv1964091577 code { font:115% monospace;} #yiv1964091577 #yiv1964091577ygrp-mlmsg * { line-height:1.22em;} #yiv1964091577 #yiv1964091577ygrp-mlmsg #yiv1964091577logo { padding-bottom:10px;} #yiv1964091577 #yiv1964091577ygrp-msg p a { font-family:Verdana;} #yiv1964091577 #yiv1964091577ygrp-msg p#yiv1964091577attach-count span { color:#1E66AE;font-weight:700;} #yiv1964091577 #yiv1964091577ygrp-reco #yiv1964091577reco-head { color:#ff7900;font-weight:700;} #yiv1964091577 #yiv1964091577ygrp-reco { margin-bottom:20px;padding:0px;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov li a { font-size:130%;text-decoration:none;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov li { font-size:77%;list-style-type:square;padding:6px 0;} #yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov ul { margin:0;padding:0 0 0 8px;} #yiv1964091577 #yiv1964091577ygrp-text { font-family:Georgia;} #yiv1964091577 #yiv1964091577ygrp-text p { margin:0 0 1em 0;} #yiv1964091577 #yiv1964091577ygrp-text tt { font-size:120%;} #yiv1964091577 #yiv1964091577ygrp-vital ul li:last-child { border-right:none !important; } #yiv1964091577
