{"id":4740,"date":"2023-07-08T10:31:14","date_gmt":"2023-07-08T10:31:14","guid":{"rendered":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/"},"modified":"2023-07-08T10:31:14","modified_gmt":"2023-07-08T10:31:14","slug":"pencocokan-offset-excel","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/","title":{"rendered":"Cara menggunakan offset dan match secara bersamaan di excel"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\"><span style=\"color: #000000;\">Anda bisa menggunakan sintaks berikut dengan fungsi <strong>OFFSET<\/strong> dan <strong>MATCH<\/strong> di Excel untuk menemukan nilai tertentu dalam rentang dan mengembalikan nilai terkait di kolom lain:<\/span><\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=OFFSET( <span style=\"color: #3366ff;\">B1<\/span> , MATCH( <span style=\"color: #ff0000;\">F1<\/span> , <span style=\"color: #800080;\">A1:A11<\/span> , 0)-1, 0, 1, 1)\n<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Rumus khusus ini akan mencari nilai di sel <strong>F1<\/strong> dalam rentang <strong>A1:A11<\/strong> dan kemudian mengembalikan nilai yang sesuai dari kolom <strong>B.<\/strong><\/span><\/p>\n<p> <span style=\"color: #000000;\">Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.<\/span><\/p>\n<h2> <strong>Contoh: Cara menggunakan OFFSET dan MATCH di Excel<\/strong><\/h2>\n<p> <span style=\"color: #000000;\">Katakanlah kita memiliki kumpulan data berikut di Excel yang berisi informasi tentang berbagai pemain bola basket:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-37013 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/hors-match1.png\" alt=\"\" width=\"493\" height=\"391\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Sekarang misalkan kita ingin mencari nama tim \u201cThunder\u201d di dataset dan mengembalikan nilai poinnya.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Untuk melakukannya, kita bisa mengetikkan rumus berikut di sel <strong>F2<\/strong> :<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=OFFSET( <span style=\"color: #3366ff;\">B1<\/span> , MATCH( <span style=\"color: #ff0000;\">F1<\/span> , <span style=\"color: #800080;\">A1:A11<\/span> , 0)-1, 0, 1, 1)<\/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=\"aligncenter wp-image-37014\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/hors-match2.png\" alt=\"Excel OFFSET dan MATCH bersama-sama\" width=\"558\" height=\"394\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Rumusnya mengembalikan nilai <strong>25<\/strong> , yang merupakan nilai poin yang sesuai dengan nama tim &#8220;Thunder&#8221;.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Perhatikan bahwa jika Anda ingin mengembalikan nilai kolom lain, cukup ganti <strong>B1<\/strong> dengan kolom lain.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Misalnya, Anda bisa menggunakan <strong>C1<\/strong> untuk mengembalikan nilai di kolom bantuan yang sesuai dengan nama tim &#8220;Thunder&#8221;:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <strong>=OFFSET( <span style=\"color: #3366ff;\">C1<\/span> , MATCH( <span style=\"color: #ff0000;\">F1<\/span> , <span style=\"color: #800080;\">A1:A11<\/span> , 0)-1, 0, 1, 1)<\/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-37015 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/hors-match3.png\" alt=\"\" width=\"537\" height=\"385\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Rumusnya mengembalikan nilai <strong>10<\/strong> , yang merupakan nilai assist yang sesuai dengan nama tim &#8220;Thunder&#8221;.<\/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 tugas umum lainnya di Excel:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/id\/kecocokan-indeks-mengembalikan-beberapa-nilai-secara-vertikal\/\" target=\"_blank\" rel=\"noopener\">Excel: Cara Menggunakan INDEX dan MATCH untuk Mengembalikan Banyak Nilai Secara Vertikal<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/excel-vlookup-mengembalikan-beberapa-kolom\/\" target=\"_blank\" rel=\"noopener\">Excel: Cara Menggunakan VLOOKUP untuk Mengembalikan Banyak Kolom<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/excel-vlookup-mengembalikan-semua-kecocokan\/\" target=\"_blank\" rel=\"noopener\">Excel: Cara Menggunakan VLOOKUP untuk Mengembalikan Semua Kecocokan<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Anda bisa menggunakan sintaks berikut dengan fungsi OFFSET dan MATCH di Excel untuk menemukan nilai tertentu dalam rentang dan mengembalikan nilai terkait di kolom lain: =OFFSET( B1 , MATCH( F1 , A1:A11 , 0)-1, 0, 1, 1) Rumus khusus ini akan mencari nilai di sel F1 dalam rentang A1:A11 dan kemudian mengembalikan nilai yang sesuai [&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>Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, 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\/pencocokan-offset-excel\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, beserta sebuah contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-08T10:31:14+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/hors-match1.png\" \/>\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\/pencocokan-offset-excel\/\",\"url\":\"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/\",\"name\":\"Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-08T10:31:14+00:00\",\"dateModified\":\"2023-07-08T10:31:14+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, beserta sebuah contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cara menggunakan offset dan match secara bersamaan di excel\"}]},{\"@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":"Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials","description":"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, 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\/pencocokan-offset-excel\/","og_locale":"id_ID","og_type":"article","og_title":"Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials","og_description":"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, beserta sebuah contoh.","og_url":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/","og_site_name":"Statorials","article_published_time":"2023-07-08T10:31:14+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/hors-match1.png"}],"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\/pencocokan-offset-excel\/","url":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/","name":"Cara menggunakan OFFSET dan MATCH secara bersamaan di Excel \u2013 Statorials","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-08T10:31:14+00:00","dateModified":"2023-07-08T10:31:14+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara menggunakan OFFSET dan MATCH di Excel, beserta sebuah contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/pencocokan-offset-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/pencocokan-offset-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Cara menggunakan offset dan match secara bersamaan di excel"}]},{"@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\/4740"}],"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=4740"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4740\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=4740"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=4740"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=4740"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}