{"id":4195,"date":"2023-07-12T22:54:36","date_gmt":"2023-07-12T22:54:36","guid":{"rendered":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/"},"modified":"2023-07-12T22:54:36","modified_gmt":"2023-07-12T22:54:36","slug":"google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/","title":{"rendered":"Google spreadsheet: ekstrak data dari sheet lain berdasarkan kriteria"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\">Anda dapat menggunakan fungsi <strong>QUERY<\/strong> di Google Spreadsheet untuk mengambil data dari sheet lain yang memenuhi kriteria tertentu.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Anda dapat menggunakan sintaks berikut untuk melakukan ini:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=query( <span style=\"color: #ff6600;\">Sheet1!A1:C11<\/span> , \" <span style=\"color: #008000;\">select * where A='Mavs'<\/span> \", <span style=\"color: #3366ff;\">1<\/span> )\n<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Contoh khusus ini mengekstrak data dari rentang <strong>A1:C11<\/strong> ke <strong>Sheet1<\/strong> hanya untuk baris yang nilai kolomnya A sama dengan <strong>Mavs<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\"><strong>Catatan<\/strong> : Argumen terakhir dari <strong>1<\/strong> menetapkan bahwa rentang <strong>A1:C11<\/strong> berisi baris header.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Contoh berikut menunjukkan cara menggunakan sintaksis ini dalam praktiknya.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Contoh: Ekstrak data dari sheet lain berdasarkan kriteria Google Sheets<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Mari kita mulai dengan memasukkan kumpulan data berikut ke dalam lembar pertama spreadsheet Google Spreadsheet:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-33205 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pb11.jpg\" alt=\"\" width=\"531\" height=\"437\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Sekarang katakanlah kita ingin mengekstrak setiap baris yang berisi &#8220;Mavs&#8221; di kolom Team di <strong>Sheet2<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\">Untuk melakukannya, kita bisa mengetikkan rumus berikut di sel <strong>A1<\/strong> pada <strong>Sheet2<\/strong> :<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=query( <span style=\"color: #ff6600;\">Sheet1!A1:C11<\/span> , \" <span style=\"color: #008000;\">select * where A='Mavs'<\/span> \", <span style=\"color: #3366ff;\">1<\/span> )<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Setelah kita menekan ENTER, semua baris yang kolom Timnya sama dengan <strong>Mavs<\/strong> akan diekstraksi ke dalam Sheet2:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-33206\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pb12.jpg\" alt=\"Google Spreadsheet mengambil data dari sheet lain berdasarkan kriteria\" width=\"555\" height=\"447\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Perhatikan bahwa tiga baris di mana kolom Team sama dengan <strong>Mavs<\/strong> diekstraksi dari <strong>Sheet1<\/strong> ke <strong>Sheet2<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\">Perhatikan juga bahwa Anda dapat menggunakan operator dan dalam fungsi <strong>QUERY<\/strong> untuk mengambil data berdasarkan beberapa kriteria.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Misalnya, kita dapat menggunakan sintaks berikut untuk mengekstrak data dari <strong>Sheet1<\/strong> dimana nilai pada kolom Team sama dengan <strong>Mavs<\/strong> <em>dan<\/em> nilai pada kolom Points lebih besar dari <strong>20<\/strong> :<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=query( <span style=\"color: #ff6600;\">Sheet1!A1:C11<\/span> , \" <span style=\"color: #008000;\">select * where A='Mavs' and B&gt;20<\/span> \", <span style=\"color: #3366ff;\">1<\/span> )<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Tangkapan layar berikut menunjukkan cara menggunakan rumus ini dalam praktik:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-33207 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pb13.jpg\" alt=\"\" width=\"558\" height=\"432\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Perhatikan bahwa dua baris di mana kolom Team sama dengan <strong>Mavs<\/strong> <em>dan<\/em> kolom Points lebih besar dari <strong>20<\/strong> diekstraksi dari <strong>Sheet1<\/strong> ke <strong>Sheet2<\/strong> .<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Sumber daya tambahan<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Tutorial berikut menjelaskan cara melakukan hal umum lainnya di Google Spreadsheet:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/id\/filter-muka-excel-berisi\/\" target=\"_blank\" rel=\"noopener\">Filter lanjutan Excel: cara menggunakan &#8220;Berisi&#8221;<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/filter-muka-excel-tidak-mengandung\/\" target=\"_blank\" rel=\"noopener\">Filter Tingkat Lanjut Excel: Cara Menggunakan \u201cTidak Berisi\u201d<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/muka-filter-excel-tidak-kosong\/\" target=\"_blank\" rel=\"noopener\">Filter lanjutan Excel: menampilkan baris dengan nilai yang tidak kosong<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Anda dapat menggunakan fungsi QUERY di Google Spreadsheet untuk mengambil data dari sheet lain yang memenuhi kriteria tertentu. Anda dapat menggunakan sintaks berikut untuk melakukan ini: =query( Sheet1!A1:C11 , &#8221; select * where A=&#8217;Mavs&#8217; &#8220;, 1 ) Contoh khusus ini mengekstrak data dari rentang A1:C11 ke Sheet1 hanya untuk baris yang nilai kolomnya A sama [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-12T22:54:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pb11.jpg\" \/>\n<meta name=\"author\" content=\"Benjamin anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Ditulis oleh\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin anderson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/\",\"url\":\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/\",\"name\":\"Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-12T22:54:36+00:00\",\"dateModified\":\"2023-07-12T22:54:36+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Google spreadsheet: ekstrak data dari sheet lain berdasarkan kriteria\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/statorials.org\/id\/#website\",\"url\":\"https:\/\/statorials.org\/id\/\",\"name\":\"Statorials\",\"description\":\"Panduan anda untuk kompetensi statistik!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/statorials.org\/id\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"id\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\",\"name\":\"Benjamin anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"contentUrl\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"caption\":\"Benjamin anderson\"},\"description\":\"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya\",\"sameAs\":[\"http:\/\/statorials.org\/id\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial","description":"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/","og_locale":"id_ID","og_type":"article","og_title":"Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial","og_description":"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.","og_url":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/","og_site_name":"Statorials","article_published_time":"2023-07-12T22:54:36+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pb11.jpg"}],"author":"Benjamin anderson","twitter_card":"summary_large_image","twitter_misc":{"Ditulis oleh":"Benjamin anderson","Estimasi waktu membaca":"1 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/","url":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/","name":"Google Spreadsheet: Ekstrak data dari sheet lain berdasarkan kriteria - Statorial","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-12T22:54:36+00:00","dateModified":"2023-07-12T22:54:36+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara mengekstrak data dari sheet lain di Google Sheets berdasarkan kriteria, beserta sebuah contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/google-sheet-mengekstrak-data-dari-sheet-lain-berdasarkan-kriteria\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Google spreadsheet: ekstrak data dari sheet lain berdasarkan kriteria"}]},{"@type":"WebSite","@id":"https:\/\/statorials.org\/id\/#website","url":"https:\/\/statorials.org\/id\/","name":"Statorials","description":"Panduan anda untuk kompetensi statistik!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/statorials.org\/id\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"id"},{"@type":"Person","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81","name":"Benjamin anderson","image":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/","url":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","contentUrl":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","caption":"Benjamin anderson"},"description":"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya","sameAs":["http:\/\/statorials.org\/id"]}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4195"}],"collection":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/comments?post=4195"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4195\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=4195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=4195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=4195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}