Excel Formül Sorusu Sor

Bir Excel formülüne dair sorunuz varsa, bu alanı kullanarak bize iletin.

Excel Makro Sorusu Sor

Bir Excel VBA koduna dair sorunuz varsa, bu alanı kullanarak bize iletin.

Genel Excel Sorusu Sor

Excel'in ön sayfasındaki FORMÜL haricinde olan sorularınızı buradan sorabilirsiniz.
Sayfaları Birleştirme ve Tekrar Edenleri Bulma, sayfalardaki verileri bir sayfada toplayıp, kriter bazlı saymayı öğretmektedir.

Sayfaları Birleştirme ve Tekrar Edenleri Bulma


Tüm detaylarını tek tek ele alacağımız içeriğimiz, soru kapsamında hazırlanmış özel bir formülün detaylarını anlatmaktadır. Hazırladığımız formüle ilişkin açıklamalarımızı anlatalım.
  • Dosyada, 3 ayrı sayfa bulunmaktadır.
  • Bu sayfalar, A, B, C şeklinde harflerle isimlendirilmiştir.
  • Her sayfada çalışanın adı ve sicil numarası (özet bırakılan) yer almaktadır.
Yapılmak istenen ise, 3 sayfada:
  • Her sayfadaki çalışan ve sicil bilgilerini ayrı bir sayfada toplamak,
  • Aynı ad ve aynı sicile sahip olan kim varsa sadece o kişilerin ad ve sicil bilgisini listelemektir.
Normal şartlarda, Excel'in klasik fonksiyonları ile bu tür bir ihtiyacın üstesinden gelmek kolay olmamaktadır. Ancak, Dinamik Dizi Fonksiyonları ile oldukça kolay ve sadece bir hücreye yazılan formül ile işin üstesinden gelinmektedir. Öyleyse, tüm detaylarını anlatacağımız içeriğimizin adımlarına geçelim.

A, B, C isimli sayfalardaki veriler A:B sütunlarında konumlandırılmıştır. A sütununda AD, B sütununda ise SİCİL bilgisi yer almaktadır. İlk olarak, her 3 sayfada bulunan verilerin tek sayfada birleştirilmesine ihtiyaç vardır. Bunun için yeni nesil Excel fonksiyonlarından olan SÜTUNA ile başlayacağız.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Böylece, üstteki gibi yazacağımız formül, 3 ayrı sayfadaki AD ve SİCİL bilgilerini alt alta gelecek şekilde bir sütunda listeleyecektir. Ancak, sayfalardaki dizilimde AD ayrı bir sütunda, SİCİL ayrı bir sütunda bulunmaktadır. Görüldüğü üzere, SÜTUNA ile yazdığımız formül tek başına yeterli değildir. O zaman, yine yeni nesil Excel fonksiyonlarından olan SATIRSAR ile formülü genişletmemiz gerekmektedir. Sonraki adımdaki formülümüz şöyle oluşacaktır.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Eğer formülü uyguladıysanız, artık 3 sayfadaki verilerin tek sayfada alt alta ve iki sütunda listelendiğini göreceksiniz. SÜTUNA ile hazırladığımız formülde, fonksiyonun "dizi" öğesi için kullandığımız yönteme dikkat etmiş olabilirsiniz. Bunun nasıl yapıldığına dair detayları, Birden Fazla Sayfadaki Verileri Kolay Toplama linkinden izleyebilirsiniz. Böylece, ilk aşamadaki adımlarımızı tamamlamış olduk.

Aynı Ada ve Sicile Sahip Tekrar Edenleri Bulma

Dosyadaki ihtiyacın ikinci aşamasında, boş sayfada oluşturulan verilerde, sadece tekrar eden değerlerin listelenmesine ihtiyaç duyulmaktadır. Ancak, hem AD hem de SİCİL tekrar eden nitelikte olmalıdır. Dolayısıyla, burada yine kullanacağımız gelişmiş Excel fonksiyonları olacaktır. İhtiyacımız olan, BENZERSİZ fonksiyonudur. Ancak, bu fonksiyon en temel kullanım ile yeterli olmayacaktır. İlk olarak, üstte kullandığımız formülün dışına BENZERSİZ fonksiyonunu eklememiz gerekecektir.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Buradaki en kritik husus, BENZERSİZ fonksiyonunun "exactly_once" öğesinin seçeğini, "DOĞRU - Yalnızca bir kez görünen öğeleri döndür" olarak seçmektir. Böylece, 3 ayrı sayfadan elde ettiğimiz verilerde, hem AD hem de SİCİL tekrar ediyorsa, bu listenin dışında kalacaktır. Lakin, bu formül bizim için istenilen sonuca ulaşmak noktasında aslında sadece bir tanımdan ibarettir. Formülü, kriterin bir parçası olmak için kullanacağız.

Sayfaları Birleştirme ve Tekrar Edenleri Bulma işlemi için sonraki adımımız şöyle devam edecektir. Üstte verdiğimiz formüllerde, sayfaları birleştirdik ve sadece bir kez yazılı olanları listeledik. Böylece, elimizde hem "veri tablosu" hem de "kriter tablosu" oluşmuş bulunmaktadır. Bu iki formülü, sonuca ulaştığımız ana formülümüzde birer tanım olarak kullanmaktayız. Yani, LET ile yapılan ad tanımlarının ad değerleri niteliğindedirler. Görünüm aşaması şu şekildedir.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Böylece, asıl formülümüzün tanımlamaları bitirilmiş oldu. Artık son aşamada, işlevi yerine getirecek kısmının yazılması kaldı. Hedefe ulaşmamızı sağlayacak olan, BENZERSİZ ve FİLTRE fonksiyonlarıdır. BENZERSİZ fonksiyonun burada sadece "dizi" öğesi kullanılacaktır. Dolayısıyla, sonuca oluşmamızı sağlayan temel yapı, FİLTRE fonksiyonu üzerine kurulacaktır. FİLTRE fonksiyonun "dizi" öğesine, tanımladığımız rng değişkenini yazıyoruz. "ekle" öğesi için ise, yine işleri biraz zorlu hale getirecek formül kullanacağız.

Kriterlerini Tanımlama

Formülün temel yapısı, KAÇINCI üzerine kurulacaktır.Ancak, KAÇINCI tek başına çok fazla işlevsel olmadığından, muhteşem ekürisi olan İNDİS ile güçlendirilecektir. KAÇINCI fonksiyonunun "aranan_değer" öğesi için kullanımımız aşağıdaki gibidir.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Dikkat ederseniz, oluşturduğumuz veri tablosunun her iki sütunu, "aranan_değer" kısmında birleştirilmektedir. Örneğin, AHMET|123 şeklinde bir "aranan_değer" oluşturulmuştur. Hemen akabinde, KAÇINCI fonksiyonunun "aranan_dizi" öğesinde ise, sadece bir kez yazılanları bulduğumuz tablonun her iki sütunu kullanılmaktadır. Kullanımı şöyle olmaktadır.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Aslında her iki tanımda birbirinin aynısıdır. Ancak, tek farkları ikinci formülde oluşan veri tablosunda, birden fazla yazılmış olan AD ve SİCİL bilgisinin bulunmamasıdır. Dolayısıyla, yapılmak istenen her iki tablonunu değerlerinin karşılaştırılması ve sıralamadaki numaralarının bulunması şeklinde özetlenebilir. KAÇINCI fonksiyonunun opsiyonel olan "[eşleştir_tür]" öğesi içinse, "0 - Tam eşleşme" seçeneği tercih edilmelidir. Formülümüz, veri tablomuzdaki toplam sayı kadar oluşturmamızı sağlayacaktır. Ancak, tekrar eden AD ve SİCİL bilgilerinin olduğu numaralara geldiğinde, #YOK Hatası verecektir. İşte tam olarak istediğimiz, bu hatanın olduğu numaralardaki AD ve SİCİL bilgileridir. Yani #YOK Hatası veren satırlarda bulunan değerler, bizim bulmak istediğimiz değerlerdir. Toplam 4 tane #YOK Hatası alınmaktadır. Böylece, bizim en az 2 tane AD ve SİCİL bilgimizin "tekrar eden" nitelikte olduğunu tespit etmiş bulunmaktayız. Hataları devre dışı bırakmak için, EYOKSA fonksiyonu işimizi görecektir. EYOKSA fonksiyonu sıra numalarını, DOĞRU ya da YANLIŞ şeklinde boolean ifadelere döndürmektedir.

Sayfaları Birleştirme ve Tekrar Edenleri Bulma Sonuç

Artık, sonuç işleminin olduğu kısma gelmiş bulunmaktayız. Üstteki adımlar neticesinde, FİLTRE fonksiyonun tamamlandığı formül şöyle oluşmaktadır.
Kod:
Görüntülemek için giriş yapmalısınız.
(1 satır)
Böylece, ikişer tane olan 2 ayrı AD ve SİCİL bilgisi listenmektedir. Ancak, bizden istenilen, isimlerin ve sicil numaralarının yazılmasıdır. Dolayısıyla, son aşamada sadece üstteki formülün dışına BENZERSİZ fonksiyonu eklenecek ve nihai formül şekillendirilecektir. Eğer, yeni oluşan veri tablonusun başlıklarını da formüller ile yapmak isterseniz, DÜŞEYYIĞ ve YATAYYIĞ fonksiyonlarından faydalanabilirsiniz.

Tüm detayların hazır hali için, dosyamızı indirmeniz yeterli olacaktır.

Bu Dosyada Kullanılan Özellikler

Resul Kurt Danışmanlık

Son incelemeler

Gerçekten anlatım ve sonuç muazzam. Emeği geçenleri ayrı ayrı tebrik ederim.
Geri
Üst