{"id":1592,"date":"2012-06-12T21:51:46","date_gmt":"2012-06-12T14:51:46","guid":{"rendered":"http:\/\/sinaulinux.martianuswb.com\/?p=1592"},"modified":"2012-06-12T21:51:46","modified_gmt":"2012-06-12T14:51:46","slug":"libreoffice-calc-sumproduct","status":"publish","type":"post","link":"https:\/\/martianuswb.com\/?p=1592","title":{"rendered":"LibreOffice Calc: sumproduct"},"content":{"rendered":"<p>Kalau di Microsoft Excel ada fungsi <span style=\"color: #ff0000;\"><strong>COUNTIFS<\/strong><\/span> untuk menghitung banyaknya data berdasarkan lebih dari satu kriteria, maka di LibreOffice Calc, tidak ada fungsi ini. Sebagai altenatif bisa digunakan fungsi <span style=\"color: #ff0000;\"><strong>SUMPRODUCT<\/strong><\/span> yang pada prinsipnya sama dengan COUNTIFS pada Microsoft Excel.<\/p>\n<p>Misal kita dapatkan data sebagai berikut:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1593\" title=\"Untitled 1 - LibreOffice Calc_002\" src=\"http:\/\/martianuswb.com\/wp-content\/uploads\/2012\/06\/Untitled-1-LibreOffice-Calc_0021.png\" alt=\"\" width=\"400\" height=\"291\" srcset=\"https:\/\/martianuswb.com\/wp-content\/uploads\/2012\/06\/Untitled-1-LibreOffice-Calc_0021.png 400w, https:\/\/martianuswb.com\/wp-content\/uploads\/2012\/06\/Untitled-1-LibreOffice-Calc_0021-300x218.png 300w, https:\/\/martianuswb.com\/wp-content\/uploads\/2012\/06\/Untitled-1-LibreOffice-Calc_0021-150x109.png 150w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/p>\n<p>Berdasarkan data pada tabel A1:D11 akan dibuat rekap pada F1:H4 berdasarkan kriteria jenis kelamin dan usia.<\/p>\n<ul>\n<li>Maka, pada sel G3 untuk melihat banyaknya laki-laki yang berusia di bawah 27 tahun, kita isikan formula <span style=\"color: #0000ff;\"><em>=SUMPRODUCT($C$2:$C$11=&#8221;L&#8221;,$D$2:$D$11&lt;27)<\/em><\/span><\/li>\n<li>Pada sel H3 untuk mengetahui banyaknya laki-laki yang berusia di atas 27 tahun, kita ketikkan formula\u00a0<span style=\"color: #0000ff;\"><em>=SUMPRODUCT($C$2:$C$11=&#8221;L&#8221;,$D$2:$D$11&gt;=27)<\/em><\/span><\/li>\n<li>Dengan prinsip yang sama, di sel G4 kita isikan\u00a0<span style=\"color: #0000ff;\"><em>=SUMPRODUCT($C$2:$C$11=&#8221;P&#8221;,$D$2:$D$11&lt;27)<\/em><\/span><\/li>\n<li>Dan di sel H4 bisa kita ketikkan\u00a0<span style=\"color: #0000ff;\"><em>=SUMPRODUCT($C$2:$C$11=&#8221;P&#8221;,$D$2:$D$11&gt;=27)<\/em><\/span><\/li>\n<\/ul>\n<p>Yang perlu diperhatikan adalah pemberian nilai mutlak (<em>absolute reference<\/em>) pada formula tersebut, yang ditunjukkan dengan karakter <strong><span style=\"color: #0000ff;\"><em>$<\/em><\/span><\/strong>. Untuk memberikan nilai mutlak bisa digunakan shortcut <strong><span style=\"color: #0000ff;\"><em>&lt;Shift&gt; + &lt;F4&gt;<\/em><\/span><\/strong>. Kemudian untuk karakter dengan format Text, harus diingat untuk menggunakan tanda kutip ganda <span style=\"color: #0000ff;\"><em><strong>&#8220;&#8230;&#8221;<\/strong><\/em><\/span>.<\/p>\n<p>Maka, tabel data rekap akan menjadi seperti berikut:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1594\" title=\"Untitled 1 - LibreOffice Calc_001\" src=\"http:\/\/martianuswb.com\/wp-content\/uploads\/2012\/06\/Untitled-1-LibreOffice-Calc_001.png\" alt=\"\" width=\"400\" height=\"291\" \/><\/p>\n<p>Sebenarnya ada cara yang lebih mudah untuk membuat rekap data, yaitu dengan menggunakan Data Filter. Lain waktu akan saya tuliskan.<\/p>\n<p>&nbsp;<\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>Kalau di Microsoft Excel ada fungsi COUNTIFS untuk menghitung banyaknya data berdasarkan lebih dari satu kriteria, maka di LibreOffice Calc, tidak ada fungsi ini. Sebagai altenatif bisa digunakan fungsi SUMPRODUCT&#8230; <a href=\"https:\/\/martianuswb.com\/?p=1592\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":4681,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[1403],"tags":[1516,1680,1901],"class_list":["post-1592","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-libreoffice","tag-countifs","tag-libreoffice-calc","tag-sumproduct"],"_links":{"self":[{"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/posts\/1592","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/martianuswb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1592"}],"version-history":[{"count":0,"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/posts\/1592\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/martianuswb.com\/index.php?rest_route=\/wp\/v2\/media\/4681"}],"wp:attachment":[{"href":"https:\/\/martianuswb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1592"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/martianuswb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1592"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/martianuswb.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1592"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}