{"id":3384,"date":"2023-07-17T18:55:20","date_gmt":"2023-07-17T18:55:20","guid":{"rendered":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/"},"modified":"2023-07-17T18:55:20","modified_gmt":"2023-07-17T18:55:20","slug":"excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/","title":{"rendered":"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\">Anda bisa menggunakan rumus dasar berikut untuk mengembalikan beberapa nilai di Excel berdasarkan satu kriteria:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <strong>=INDEX( <span style=\"color: #3366ff;\">$A$1:$A$14<\/span> , SMALL(IF( <span style=\"color: #ff0000;\">E$1<\/span> = <span style=\"color: #800080;\">$B$1:$B$14<\/span> , MATCH(ROW( <span style=\"color: #800080;\">$B$1:$B$14<\/span> ), ROW( <span style=\"color: #800080;\">$B$1:$B$14<\/span> )) , \"\"), ROWS( <span style=\"color: #008000;\">$A$1:A1<\/span> )))\n<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Rumus khusus ini mengembalikan semua nilai dalam rentang <strong>A1:A14<\/strong> dengan nilai terkait dalam rentang <strong>B1:B14<\/strong> sama dengan nilai di sel <strong>E1<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\">Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Contoh: Mengembalikan Banyak Nilai Berdasarkan Satu Kriteria di Excel<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Misalkan kita memiliki kumpulan data berikut yang menunjukkan pemenang Final NBA di tahun yang berbeda:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-28391 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple1.jpg\" alt=\"\" width=\"499\" height=\"467\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\"><span style=\"color: #000000;\">Kita bisa mengetikkan rumus berikut ke dalam sel <strong>E2<\/strong> untuk kembali ke setiap tahun kemenangan Warriors:<\/span><\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <strong>=INDEX( <span style=\"color: #3366ff;\">$A$1:$A$14<\/span> , SMALL(IF( <span style=\"color: #ff0000;\">E$1<\/span> = <span style=\"color: #800080;\">$B$1:$B$14<\/span> , MATCH(ROW( <span style=\"color: #800080;\">$B$1:$B$14<\/span> ), ROW( <span style=\"color: #800080;\">$B$1:$B$14<\/span> )) , \"\"), ROWS( <span style=\"color: #008000;\">$A$1:A1<\/span> )))<\/strong><\/pre>\n<p> <span style=\"color: #000000;\"><span style=\"color: #000000;\">Setelah kami menekan <strong>Enter<\/strong> , tahun pertama kemenangan Warriors akan ditampilkan:<\/span><\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-28392 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple2.jpg\" alt=\"\" width=\"570\" height=\"487\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\"><span style=\"color: #000000;\">Kita kemudian dapat menyeret dan mengisi rumus ini ke sel lain di kolom E hingga kita menemukan #NUM! nilai:<\/span><\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-28393 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple3.jpg\" alt=\"\" width=\"497\" height=\"510\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Kita bisa melihat Warriors memenangkan final di tahun-tahun berikutnya:<\/span><\/p>\n<ul>\n<li> <span style=\"color: #000000;\">2015<\/span><\/li>\n<li> <span style=\"color: #000000;\">2017<\/span><\/li>\n<li> <span style=\"color: #000000;\">2018<\/span><\/li>\n<li> <span style=\"color: #000000;\">2022<\/span><\/li>\n<\/ul>\n<p> <span style=\"color: #000000;\">Jika kita mengubah nama tim di sel <strong>E1<\/strong> , daftar tahun akan otomatis terupdate.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Misalnya, kita mengetik &#8220;Lakers&#8221; di sel <strong>E1<\/strong> :<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-28394 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple4.jpg\" alt=\"\" width=\"477\" height=\"448\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Kita bisa melihat Lakers memenangkan Final di tahun-tahun berikutnya:<\/span><\/p>\n<ul>\n<li> <span style=\"color: #000000;\">2010<\/span><\/li>\n<li> <span style=\"color: #000000;\">2020<\/span><\/li>\n<\/ul>\n<h2> <span style=\"color: #000000;\"><strong>Sumber daya tambahan<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di Excel:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/id\/nilai-unik-excel-berdasarkan-kriteria\/\" target=\"_blank\" rel=\"noopener\">Excel: cara mengekstrak nilai unik berdasarkan kriteria<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/seleksi-acak-excel-dengan-kriteria\/\" target=\"_blank\" rel=\"noopener\">Excel: cara memilih sel secara acak berdasarkan kriteria<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/excel-10-besar-berdasarkan-kriteria\/\" target=\"_blank\" rel=\"noopener\">Excel: cara mencari 10 nilai teratas berdasarkan kriteria<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Anda bisa menggunakan rumus dasar berikut untuk mengembalikan beberapa nilai di Excel berdasarkan satu kriteria: =INDEX( $A$1:$A$14 , SMALL(IF( E$1 = $B$1:$B$14 , MATCH(ROW( $B$1:$B$14 ), ROW( $B$1:$B$14 )) , &#8220;&#8221;), ROWS( $A$1:A1 ))) Rumus khusus ini mengembalikan semua nilai dalam rentang A1:A14 dengan nilai terkait dalam rentang B1:B14 sama dengan nilai di sel E1 [&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>Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan 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\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan sebuah contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-17T18:55:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple1.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\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/\",\"url\":\"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/\",\"name\":\"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-17T18:55:20+00:00\",\"dateModified\":\"2023-07-17T18:55:20+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan sebuah contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel: mengembalikan beberapa nilai berdasarkan satu 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":"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial","description":"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan 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\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/","og_locale":"id_ID","og_type":"article","og_title":"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial","og_description":"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan sebuah contoh.","og_url":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/","og_site_name":"Statorials","article_published_time":"2023-07-17T18:55:20+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/multiple1.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\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/","url":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/","name":"Excel: mengembalikan beberapa nilai berdasarkan satu kriteria - Statorial","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-17T18:55:20+00:00","dateModified":"2023-07-17T18:55:20+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara mengembalikan beberapa nilai berdasarkan satu kriteria di Excel, dengan sebuah contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/excel-mengembalikan-beberapa-nilai-berdasarkan-satu-kriteria\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Excel: mengembalikan beberapa nilai berdasarkan satu 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\/3384"}],"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=3384"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/3384\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=3384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=3384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=3384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}