Özel Git penceresinin yeteneklerini keşfederek işlerinizi kolaylaştırın!

Excel listenizdeki açıklama, formül, boşluk, veri doğrulama gibi özel veriler içeren hücreleri tek seferde topluca tespit ederek seçilmesini sağlayan işlev Özel Git ’tir.

Bu işlev ile formüllü hücreleri tek seferde seçerek sonrasında formülle şifreleyebilir, boş hücreleri tek seferde seçerek sonrasında renklendirebilir, sayısal, metinsel veri içeren ya da koşullu biçimlendirme içeren hücreleri topluca seçerek gerekli çalışmaları yapabilirsiniz.

Bu işlev Giriş sekmesinde Düzenleme grubunda Bul ve Seç butonunun içinde yer almaktadır.

F5 kısa yolu ile (fn tuşuna basmanız gerekebilir) açılan pencereden Özel git seçilerek de açılabilir.

Özel Git penceresinin yeteneklerini keşfederek işlerinizi kolaylaştırın!

Yukarıdaki listede gösterildiği gibi Satış Bölgesi kolonundaki boşlukları doğru verilerle dolduracağımız bir senaryo düşünelim.

Öncelikle boş hücreleri seçmek gerekir, sonrasında ise boş hücreleri en Hızlı şekilde doğru veri ile tamamlanmak!

Bu hücrelere tek tek yazı yazmadan, kolayca nasıl doldurabiliriz?

İşlemlerimiz iki özelliği birlikte kullanarak gerçekleşecek.

İşlem Adımları

  1. Öncelikle boş hücrelerin bulunduğu kolon seçilir.
  2. F5 kısayolu (fn tuşuna basmanız gerekebilir) –>Özel Git (Special Go)
  3. Açılan pencereden Boşluklar (Blanks) seçilerek Tamam’a tıklanır.

Sonrasında aşağıdaki görüntüyü elde ederiz

Seçilen hücrelerin gri olduğunu ve en üstte yer alan hücrenin ise beyaz olduğunu fark edeceğiz. Bu hücreye doğrudan yazı yazabilirsiniz. Yani hücreye yazı yazabilmek için hücreye tıklamanız gerekmiyor. Bu durumu vurgulama sebebimiz ise işlem yapmayı istediğiniz alanlar zaten otomatik olarak seçilmiş durumda, bu nedenle de hiç bir fare tıklaması yapılmasına gerek yok. Fare ile tıkladığımızda ise seçili alanlar iptal ediliyor ve bu nedenle de dikkat edilmeli.

  1. Doğrudan klavyenizden =G2 (bir üstünde yer alan hücrenin adresi) yazacağız. Bu sayede bir üstündeki veriyi kullanacağımızı  Excel’e söylemiş oluyoruz.En önemli nokta: Bir üstündeki veriyi yazacağız tamam ama bu formül nasıl tüm kolonda çalışacak?

    Bu işlem için ikinci ve son adıma geçiyoruz..
  2. =G2 işleminin tüm kolonun seçili alanlarında çalışması için CTRL ve ENTER tuşlarına aynı anda basmalıyız.İşlem  adımlarının takibinde Satış Bölgesi kolonu tamamen doldurulmuş olacak.

    Diğer Özel Git Makaleleri

    Formüllü hücreleri Özel Git penceresini kullanarak tespit etmek ve sonrasında şifrelemek için makalemize link üzerinden göz atın.Boş Hücreleri sabit bir metinle değiştirmek için gerekli işlemleri öğrenmek için makalemize link üzerinden göz atın.

Metin Birleştirme İşlemleri

Birleştir fonksiyonu, farklı hücrelerdeki metinleri birleştirerek bir hücrede yazmayı sağlar. Birleştir formülü ile metinler çok esnek şekilde düzenlenebilir.

Bu bağlamda formülde istediğiniz herhangi bir alanda özgürce değişiklik yaparak istediğiniz sonucu alabilirsiniz.

 

Formülün argümanları ardışık metinsel ifadelerden oluşur.

İngilizce:

=Concatenate(text1, text2,…)

Türkçe:

=Birleştir(metin1, metin2,…)

Birleştirilecek metinleri, ilgili hücrelerden teker teker seçerek birleştirebilirsiniz.

Aşağıdaki örnekte Mahalle, Cadde ve No bilgisi birleştirilerek adres bilgisi oluşturulmaktadır. A2, B2 ve C2 hücreleri doğrudan birleştirildiğinde MerkezAyazma37 sonucunu üretiyor.

 

Merkez mahallesi Ayazma caddesi no:37 şeklinde daha aşina olduğumuz bir sonuç görebilmek için neler yapılabilir?

Uzun yöntemle formül içerisine “ mahellesi ” ,   “ caddesi: “ gibi metinsel ifadeler yazılabilir.


*Formüller içinde metinsel ifadeyi klavyeden yazmak gerektiğinde çift tırnak işaretleri (“ ”) arasında yazılmalıdır. Excel’de metinlerin “ ” içerisinde kullanılması konusu katıdır.

Daha verimli bir yöntem ise; daha sonra kullanılabilir, düzenlenebilir ve merkezi şekilde yönetilebilir olması adına mahallesi, caddesi, no: gibi bilgileri herhangi bir hücre içerisine yazarak o hücreyi referans alabiliriz.

 

Metin Birleştirme formülünün detaylarını incelemek için linke tıklayınız.

Yukarıdaki bilgilere ek olarak fonksiyon kullanmadan da birleştirme yapmak mümkün. Birleştirme operatörü olarak & simgesi kullanılabilir. Birleştir fonksiyonuyla benzer çalışır ve ve metinleri doğrudan birleştirmemizi sağlar.

Yukarıda, metinleri birleştirmeyi gördük. Buna ek olarak birleştirilmiş metinleri ayırmayı da isteyebiliriz.

Metni Sütunlara Dönüştür işlevi ile yan yana tek hücreye yazılmış metinleri, ayrı ayrı hücrelere yazabilirsiniz. Yani Birleştir işleminin tam tersi şekilde çalışır. Metni Sütunlara Dönüştür makalemizi okumak için linke tıklaynız.

TOPLA.ÇARPIM / SUMPRODUCT Sihirli Fonksiyon

Bu yazıda sizlere, Excel‘de temel ve orta düzey kullanıcıların pek kullanmadığı ve/veya neler yapabildiğini bilmediği bir fonksiyondan, hem de sihirli bir fonksiyon olarak nitelendirebileceğim TOPLA.ÇARPIM / SUMPRODUCT fonksiyonundan ve onun neler yapabileceğinden bahsedeceğim.

 

Excel‘deki fonksiyonlara/formüllere baktığımızda, her fonksiyonun bir yazım kalıbı ve yaptığı bir işi vardır.  Örneğin; DÜŞEYARA/VLOOKUP fonksiyonunun yaptığı iş belli. Kabaca tarif etmek gerekirse; bunu ara, bu tabloda ara, bulursan 2. sütundaki veriyi bana getir şeklinde kullanırız. Farklı kullanımları olmakla beraber yaptığı tek iş bu, başka bir iş yapmaz. Aynı şekilde ETOPLA/SUMIF fonksiyonu da, kabaca şöyle çalışır; bu aralıkta, bundan varsa, burayı topla gibi.. başka bir iş yapmaz.

Öyle bir fonksiyon düşünün ki; birçok fonksiyonun yapabildiği işi tek başına yapabiliyor. ETOPLA, ÇOKETOPLA, EĞERSAY, ÇOKEĞERSAY ve daha birçok fonksiyonun yerine sadece TOPLA.ÇARPIM fonksiyonu kullanabilirsiniz.

Çalışma mantığını şöyle düşünelim; verilen aralık ya da dizilerde birbirine karşılık gelen sayısal bileşenleri çarpar ve bu çarpımların toplamını verir. Fakat sadece çarpmaz, isterseniz toplar, çıkarır, böler.

Bu fonksiyonu bu kadar çok iş yapabilmesi tamamen Dizi mantığı ile çalışıyor olmasından kaynaklıdır. Dizi mantığıyla, seçilen tüm hücre aralıkları içindeki verilerle işlemler yapabilmesini sağlar. Dizi mantığında da, belirtilen birden fazla Dizi varsa her bir dizinin birbirine karşılık gelen elemanlarıyla ilgili işlemler yapar. Herhangi bir karşılaştırma sonucunda bize DOĞRU veya YANLIŞ yanıtını döndürür. DOĞRU‘nun karşılığı 1‘dir, YANLIŞ‘ın karşılığı da 0 (sıfır)’dır. Burada önemli olan da, karşılaştırma sonucunda bize geri dönen DOĞRU yanıtıdır. Her bir DOĞRU yanıtının karşılığı 1 olduğu için, toplamı alınmak istenen hücre aralığını seçtiğimizde, DOĞRU‘ların karşılığındaki toplam rakamını birbirleriyle çarpıp sonrasında toplar ve bize sonucu döndürür.

Aşağıdaki görselde şu formülü görüyorsunuz; =(A1:A10=G6)*(B1:B10) bu formülü seçip F9‘a bastığınızda şöyle bir dizi verecektir. TOPLA.ÇARPIM fonksiyonu da bu dizi içerisindeki sayıları toplar. Hangileri eşitse onlar 1‘olur ve B sütunundaki sayıları 1‘le çarpar ve toplar. Fonksiyon adına baktığınızda, içten dışa doğru gidersek, TOPLA.ÇARPIM önce çarp sonra da topla mantığı olduğunu da kolayca görebilirsiniz.

 

Genel Olarak Neler Yapılabilir?

  • Yukarıda belirttiğim fonksiyonların yaptığı tüm işleri yapabilir.
  • Tek bir kritere göre toplam alabilir veya sayma işlemi yapabilir.
  • Birden fazla kritere göre toplam alabilir veya sayma işlemi yapabilir.
  • Metin olarak saklanan sayıların toplanmasını sağlayabilir.
  • İki sütundaki sayıları birbirleriyle çarpıp toplamını alabilir.
  • Belirtilen alanda kaç karakter veri olduğu sayılabilir.
  • TOPLA(EĞER( ile başlayan Dizi formülü ile şarta göre toplam alınmak istenen durumların yerine kullanılabilir.
  • Sütundaki en yüksek 5 sayının toplamını alabilir.
  • Sütundaki en düşük 5 sayının toplamını alabilir.
  • Belirtilen alandaki Benzersiz verilerin sayısını bulabilir.
  • Sütundaki sayılardan benzersiz olanların toplamını bulabilir.
  • Belirlenen alanda tek veya çift sayıların toplamını alabilir.
  • Belirli bir veri içeren hücrelerin toplamını alabilir.
  • Tek hane koşuluna göre toplam alabilir.

ve daha birçok özel işlem..

Şimdi bu fonksiyonunu temel kullanımına bir örnek verelim. İki farklı sütundaki sayıları birbirleriyle çarpıp sonrasında toplamlarını almak istiyoruz. Basitçe iki hücreyi birbiriyle çarpıp aşağıya doğru çektikten sonra alt hücreye de TOPLA formülü ile alt toplamını aldırarak bu işlemi yapabiliriz. Bu basit işlemi 2-3 adımda yapabildik, oysa TOPLA.ÇARPIM fonksiyonu ile hem daha hızlı, hem de hücrelere değer aktarmaya gerek kalmadan çözebiliriz.

Örnek için bu gif resme bakabilirsiniz.

TOPLA.ÇARPIM fonksiyonunu eğer ETOPLA veya ÇOKETOPLA gibi bir veya birden fazla kritere göre toplam almak istediğiniz yapıda kullanmak isterseniz o zaman birçok seçeneğiniz olacaktır. Aşağıda göreceğiniz tüm kullanımlar, sorgulama sonucunda gelen değeri sayıya dönüştürmek içindir. Hangi syntaxı kullanırsanız kullanın sonucun aynı olduğunu göreceksiniz. Bu tarz kullanımlarda, dikkat ederseniz her bir koşulu kendi parantezi içerisinde yazıyoruz.

Bu konuda birkaç örnek daha vermek istiyorum. Meselâ başka ne işe yarar?

Bir sütunda metin olarak saklanan sayıların toplamını alalım, o sayıların altına gelip =TOPLA(A2:A5) dediğimizde sonuç olarak bize 0 (sıfır) değerini döndürecektir.

Ayni işlemi bir de TOPLA.ÇARPIM fonksiyonu ile yapmayı denediğinizde sonucun 100 olduğunu ve toplayabildiğini göreceksiniz.

Bu fonksiyonun kullanımı yeri oldukça geniştir, birçok fonksiyonla iç içe yazılabilir. Belirttiğiniz alanda kaç karakter olduğunu öğrenmek için de yine bu fonksiyondan faydalanabiliriz.

Biliyorsunuz Metin Fonksiyonları listesinde UZUNLUK adında bir fonksiyon var. Bu fonksiyon; seçtiğiniz metnin kaç karakter uzunluğunda olduğunu verir. Normalde içerisine tek bir hücre veya bir metin yazılabilir, birden fazla hücre aralığı belirtilemez. Fakat bu fonksiyonu TOPLA.ÇARPIM fonksiyonu içinde kullanırsanız, seçmiş olduğunuz hücre aralığındaki tüm hücrelerin karakter sayılarının toplamını verir.

Bu fonksiyonla ilgili yapılabilecek daha birçok işlem var, daha detaylı bilgi almak için Excel Eğitimlerimize katılabilirsiniz.

Faydalı olması dileğiyle. 😊

 

ARA Fonksiyonunda 9,99999999999999E+307 Mucizesi

Bu yazımızda sizlere, Excel formüllerinde çoğu kimsenin bilmediği ve kullanmadığı bir ipucunu aktarıyor olacağız. Excel‘de genellikle Arama ve Başvuru Fonksiyonlarında yer alan ARA fonksiyonu, içerisinde kullanılan 9,99999999999999E+307 ifadesi ile bir dizi veya metin içerisindeki en son sayısal veriye ulaşmamızı sağlar.

 

Nedir bu 9,99999999999999E+307?

Bu sayı; Excel’de yazılabilecek en yüksek pozitif sayısal değerdir.
Bildiğiniz üzere Excel’de sayı duyarlılığı 15 basamaktır, daha büyük bir sayı belirtildiğinde sonuna E+ ile başlayan bir sayı ekler ve 15 basamak sonrasını sayının sonuna 0 (sıfır) olarak ekler.
Bu sayı en yüksek pozitif sayı olduğu için, belirtilen hücre adresi veya metindeki son karaktere kadar kontrol edilmesini sağlar.

Ufak bir detay vereyim; aslında illâ ki 9,999999999999999E+307 gibi sayının tamamını yazmanıza gerek yok, 9,999E+307 yazmanız da çoğu zaman yine aynı şekilde algılanıp işlem yapmasını sağlar.

 

Nerelerde Kullanabiliriz?

  • Sütundaki son sayısal veriyi almak için (asıl görevi bu)
  • Sütundaki son metinsel veriyi almak için (indis > kaçıncı içinde kullanılarak)
  • Sütundaki son sayısal verinin kaçıncı satırda olduğunu bulmak için
  • Aranan veriyi içerir şeklinde tabloda arayıp karşılığını almak için
  • Sayı ve metnin birlikte yazıldığı durumlarda metnin solundaki sayıları almak için
  • Sayı ve metnin birlikte yazıldığı durumlarda metnin sağındaki sayıları almak için

bu ve buna benzer durumlarda bu özel sayıyı kullanabiliriz.

 

İlk örneğimizle başlayalım..

Aşağıdaki tabloda D sütundaki son Fatura Tarihini ve G sütunundaki son Tutarı almak istediğimizde, =ARA(9,99999999999999E+307;D:D) formülünü kullanabiliriz.

 

 

İsterseniz aranan bir veriyi tabloda içerir şeklinde arayıp istediğimiz sütundaki veriyi almak istediğimizde de bu yapıyı kullanabiliyoruz.

Örnek bir tablo üzerinde nasıl yapabileceğimizi görelim.
ARA fonksiyonu içerisinde, aradığımız veriyi 9,99E+307 yazıyoruz.
Aranan Vektör kısmında MBUL fonksiyonu ile aranan verinin tablodaki kaçıncı satırda olduğunu buluyoruz. Sonuç Vektör kısmında da sonuç olarak almak istediğimiz hücre aralığını belirtiyoruz.

Resimde de göreceğiniz üzere, örneğin Travel metinin Tabloda aradık ve karşılığındaki 34 sayısını aldık. Tablodaki veri ****Travel olmasına rağmen bu kelimeyi içerir şeklinde aradı ve buldu, istediğimiz sonucu bize geri döndürdü.

 

İsterseniz bir hücre içerisinde PEAKUP860 gibi bir veriden metni ve sayıyı ayırmak için ya da sadece sayıları almak için kullanabilirsiniz. Bunun için eğer alacağınız sayı verinin solundaysa ARA formülü içinde SOLDAN fonksiyonu ile birlikte, sağındaysa da SAĞDAN fonksiyonu ile birlikte kullanarak istediğimiz sonucu elde edebiliriz.

Aşağıdaki tabloda kullanımını göreceksiniz. Mantığını şöyle özetleyeyim; 9,999E+307 ile hücredeki son karaktere kadar aranmasını sağlıyoruz, sonra da SOLDAN formülü ile aranan verideki son sayısal değere kadar bak diyoruz. Formülde iki tane tire işaretini ( — ) görüyorsunuz, bunu da alacağımız veriyi sayıya döndürmek için kullandık.

 

 

Bu yazımızı çevrenizle de paylaşarak, daha fazla kişinin bilgi sahibi olmasını sağlayabilirsiniz.

Eğerhata İle Hataları Dize Getirin

Yöneticinize sunacağınız dokümanda işlemlerin sonucunda Excel #YOK, #DEĞER!, #BAŞV!, #SAY/0!, #SAY!, #AD? veya #BOŞ! hataları mı veriyor? Belki bir tane olunca görmezden gelinebilir ancak göz ardı edilemeyecek kadar çok olduğunu düşünün. Bu rahatsızlık veren durumdan Eğerhata fonksiyonuyla nasıl kurtulabileceğimizi birlikte görelim.

Elimizde satış toplamı, satış adedi ve ortalama fiyatın olduğu bir listemiz var. Bu listede hiç satılmayan bir ürün mevcut.

 

Satış toplamını satış adedine bölerek ürünlerin ortalama fiyatlarını elde etmek istiyoruz. Basit bir matematiksel operatörle bunu halledebiliriz. Ancak bu işlemin sonucunda ürün adedi sıfır olan B7 hücresinin satış toplamına bölümünde matematiksel işlem olarak 0’a bölme işlemi gerçekleşemeyeceği için C7 hücresinde #SAY/0! hatası verdi. Hücredeki hata türüne göre kullanılabilecek mantıksal fonksiyonlar değişse de biz burada en temel hata işleme fonksiyonu olan Eğerhata işlevi ile bu problemi çözeceğiz.

Eğerhata işlevi formüldeki hataları yakalamayı ve bu hatalar için istenen ifadelerin yazılmasını sağlar. Bir formül bir hata sonucu döndürürse belirttiğimiz sayısal veya metinsel değeri verir; aksi takdirde, formülün sonucunu verir.

Burada yaptığımız işlemi Eğerhata fonksiyonunun içine alıp bu şekilde işlemi yaptıralım. İşlem sonucu bir hata ifadesi belirecekse bu hatanın yerine istediğimiz ifade yazsın. Örneğimizdeki durum için ortalama fiyat sütununda bir hata mevcutsa bu hataların yerine 0 yazmasını istiyoruz.

 

Görüldüğü gibi artık hata ibarelerinden kurtulup daha uygun gösterime sahip bir dokümanımız oldu.

Eğerhata fonksiyonu, fonksiyonlardan en çok Ehatalıysa fonksiyonu ile karşılaştırılarak anılır. Bu noktada belirtilmesi gereken Ehatalıysa fonksiyonun Eğerhata fonksiyonundan farklı olarak sadece hata denetimi yaptığıdır. Eğerhata gibi kullanmak isteniyorsa başına ekstra bir eğer formülü girilmelidir.

Hataya özel hata işlevini gerçekleştiren başka fonksiyonlar da vardır. Eğerhata fonksiyonu bunların en geniş çaplı olanıdır. Her türlü hata için bir işlem döndürür. Gönül ister ki Excel’deki tüm işlemleriniz hatasız olsun ama eğer çeşitli hatalarla karşılaşıyor olursanız bu fonksiyonu kullanabilirsiniz.

Bir sonraki makalemizde görüşmek üzere.

 

Formüllü Hücreler Hiç Bu Kadar Güvende Olmamıştı

Çoğumuz, formül içeren Excel belgelerimizi  iş arkadaşlarımıza gönderiyor ya da belge üzerinde birlikte çalışıyoruz.  Hayal etmesi bile kötü ama, onlarca formülün olduğu ve binbir zahmetle hazırladığımız  bu sayfalarda bir iş arkadaşınızın yanlışlıkla bazı formülleri sildiğini düşünelim… Hücrelerinizi savunmasız bıraktığınız için, birbirine bağlı formülleriniz ve verileriniz can çekişmeye başlıyorlar!

 

Bir sürü formüllü hücreyi tek tek seçmek ve korumak güç olabilir. Ama üzülmeyin! Bu makale ile formüllü hücreleri tek hamlede seçip koruma altına alacağız! Artık formüllü hücrelerimiz tam bir Karate Kid! (Yoksa siz Karate Kid’i bilmiyor musunuz?)

3 temel şifreleme işlemi vardır:
Çalışma kitabını şifre ile koruduğumuzda ilgili Excel dosyası şifre ile açılabilir olacaktır. Bu dosyada yalnızca var olan sayfalar üzerinde çalışılabilecek ve yeni sayfalar eklemek mümkün olmayacaktır.
Sayfaları koruma ile de veri girişi, satır silme, sütun silme, sıralama yapma gibi sayfa kullanımını kısıtlayan bir koruma yapılabilir.
Hücre koruma ise isimlendirmeden de anlaşılacağı gibi belirli bir hücre ya da hücreler üzerinde yapılıyor. Tüm hücreleri değil de belirli bir alandaki hücreleri şifrelemeyi, diğer hücrelerde çalışmayı serbest bırakmak istediğimizde başvurduğumuz bir yöntemdir.

Excel’de her hücrenin şifre ile korunabilmesi için bir kilit alt yapısı vardır. Excel’in kilit alt yapısını bir kapının kilit mekanizması gibi düşünebiliriz. Kilit mekanizması olmayan bir kapı kilitlenebilir mi? Tabi ki hayır! Bu kilit alt yapısı default olarak tüm Excel belgelerinde,  tüm hücreler için aktif durumdadır ancak aşağıda açıklayacağımız adımlarla devreye girerler.

Adım 1- Formüllü hücrelerin bulunduğu Excel sayfasındaki tüm hücreleri seçeriz ve sayfaya sağ tıklayıp Hücreleri Biçimlendir penceresini açarız. Koruma içindeki Kilitli seçeneğini pasif yaparız.

 

Adım 2- Giriş sekmesindeki Düzenleme grubundan Bul ve Seç’e tıklayıp açılan listeden Özel Git’i seçeriz. Bu işlem için alternatif kısayol tuşu ise klavyedeki f5 tuşudur. f5 tuşuna bastıktan sonra açılan pencereden Özel.. ‘e tıklarız. Yapılan bu iki işlemde de aynı pencereye gideriz.

Bu pencereden Formüller seçeneğini seçeriz ve bu seçimi yaptıktan sonra listemizde ne kadar formüllü hücre varsa hepsi aynı anda seçili hale gelecektir.

Ardından formüllü hücreler seçiliyken sağ tıklayarak Hücreleri Biçimlendir penceresi tekrarda açarak kilitleri aktif duruma getiririz.

 

Yalnızca formüllü hücrelerin kilit mekanizması aktif edildiğine göre artık sayfayı şifreleyebiliriz.

Adım 4- Gözden Geçir sekmesinde Koru grubuna geliriz buradan Sayfayı koruyu seçeriz. Açılan pencereden sayfa şifresini yazdıktan sonra koruma işlemi tamamlanmış olacaktır. Bu işlemin ardından sayfamıza uyguladığımız şifrelemeler yalnızca formüllü hücreleri etkilemiş olacaktır.

 

Bu işlemlerden sonra artık Excel dosyalarınıza, sayfalarınıza ya da hücrelerinize kendilerini koruyacak gücü verebilirsiniz.

Formüllü hücrelerinizi kilitlemek bu kadar kısa ve kolay iken, siz de verilerinizi koruma altına almayı unutmayın!

Bir sonraki makalede görüşmek üzere…