Excel Formüller

Excel Formül Kullanımı


         Hücreler tek tek A1, A2,, B1, B2, C1, C2, şeklinde veya birden çok hücre iki nokta işaretiyle adreslenebilir. Örneğin A1 hücresinden C6 hücresine kadar hücrelerin adreslenmesi A1:C6 şeklindedir.
         Ardışık olmayan alanların seçili olduğunun gösterilmesi ise A1:C6;D1:F6 şeklinde seçili
hücre grupları arasında noktalı virgül kullanılarak ayrılır.


Hücreye formül girilirken dikkat edilecek hususlar;
1. Eşittir ( = ) işareti ile başlanır.
2. Formülde boşluk karakteri kullanılamaz.
3. Eşittir işareti kullanılmadan formül girilirse Excel bunu veri olarak alır ve herhangi bir hesaplama  yapmaz.
4. Formül doğru girildiğinde hücrede formülün sonucu görülür.
5. Hücreye girilen formül, Formül çubuğunda görülür.
6. Formül girişinden vazgeçmek için tuşuna basılır veya Formül çubuğundaki İptal (x) düğmesine tıklanır.
7. Formül girişini tamamlamak için tuşuna basılır veya Formül çubuğundaki Gir ( ) düğmesine tıklanır.
8. Formül çubuğunda formülün üzerine tıklanarak veya tuşuna basılarak formülde değişiklik yapılabilir.

Örneğin ; kullanıcı A1 hücresine yaşını ve A2 hücresine  =60*24*365*A1  formülünü yazarak kaç dakika nefes aldığını yani yaşadığı dakika sayısını bulabilir.

 Formüllerde Kullanılan Öğeler
Formüllerde eşittir işaretinden sonra aşağıda sıralanan öğeler kullanılır.

• Aritmetik Öğeler
+    Artı         
-     Eksi
*     Çarpım
/      Bölü
%    Yüzde
^     Üs

 Aritmetik Öğeler Örnekleri
=A1+B3         A1 hücresindeki değer ile B3 hücresindeki değeri toplar. Sonuç formülün bulunduğu hücrede görülür.
=A1-D2          A1 hücresindeki değerden D2 hücresindeki değeri çıkarır. Aynı şekilde sonuç formülün bulunduğu hücrede görülür.
=C4*C6          C4 ve C6 hücrelerindeki sayısal değerleri çarpar.
=F12^2           F12 hücresindeki değerin karesini alır. (Excel'de Üssü işareti Ekle_Simge menüsünden eklenebilir)

 Karşılaştırma Operatörleri
=       EŞİTTİR                              Örnek : B2=D2
>       BÜYÜKTÜR                        Örnek : B2>D2
<       KÜÇÜKTÜR                        Örnek : B2<D2
>=     BÜYÜK VEYA EŞİTTİR      Örnek : B2>=D2
<=     KÜÇÜK VEYA EŞİTTİR      Örnek : B2<=D2
<>     EŞİT DEĞİLDİR                  Örnek : B2<>D2

        Bu operatörlerin sağında veya solunda değerler bulunur. Bu değerler karşılaştırılır ve iki değerden biri ortaya çıkar. “Doğru” yada “Yanlış”. Karşılaşmanın sonucu doğru ise
Doğru, yanlış ise Yanlış değeri üretilir.

 Karşılaştırma Operatörleri Örnekleri
=C4<A2        C4 hücresi A2 hücresinden küçük ise formülün bulunduğu hücreden DOĞRU değer görüntülenir.
=B4<>F4       B4 hücresindeki değer F4 hücresindeki değere eşit değilse formülün bulunduğu hücrede DOĞRU değeri                                   görüntülenir.
=C6=4           C6 hücresindeki değer 4 ise sonuç doğrudur ve formülün bulunduğu hücrede DOĞRU değeri görüntülenir.

         Burada dikkat edilmesi gereken en önemli konu formülleri yazarken (“=” “eşittir”) ile başlamak gerekir.

• Başvuru İşaretleri
(:) İki nokta    İki alan arasında kalan alanları tanımlar. Örneğin; A1:A11  "A1'den A11'e kadar olan tüm hücreleri tanımlar".                         C:C  "C sütununun tamamını ifade eder".
                       4:4   "4.satırın tamamını tanımlar".

Excel Define Dynamic Named Range

Excel Dynamic Named Range


        Dynamic named range has advantages over normal named range. For example ,the dynamic named range expands automatically when user added a value to the range.

To define dynamic named range ;
On the Formula tab, in the Defined Names group,  Define Name is clicked. Or, Ctrl + F3 is pressed  to open the Excel Name Manager, and  the New button is clicked.
 New Name dialogue box is opened.
 In the Name box, name of for dynamic range is wrote. We entered Product_Prices name.
 In the Scope dropdown, Workbook is default value.
 In the Refers to box,  OFFSET and COUNTA formula is entered.

Our formula for Product_Prices name :  =OFFSET(Products!$B$2,0,0,COUNTA(Products!$B:$B);1)


The generic formula to create a dynamic named range in Excel is as follows:

= OFFSET(sheet_name!first_cell, 0, 0, COUNTA(column), 1)

        If user are defining a dynamic range in the current worksheet, user do not need to include the worksheet name in the references, Excel will do it automatically. If user are creating a range for some other sheet, first ; the sheet name, then the exclamation mark and cell or range reference must be entered.

As an example, we have specified the sum of cells in the field we defined with the name Product_Prices in cell E3 :

     
         Excel OFFSET function takes 5 arguments.
Reference: $B$2,
 Rows to offset: 0,
 Columns to offset: 0,
 Height: COUNTA($B:$B)
 Width is equal to 1 column.
COUNTA($B:$B) counts the number of cell values in column B that are not empty. When added a new value to the range, COUNTA($B:$B) increases. Thus, the range returned by the OFFSET function enlarges.

To understand the working logic of the dynamic named range, let's make some changes to the sample template:
excel dynamic named range

As seen in the animation above ; when a value is added or subtracted to the range, Excel updates the sum value as automatically.

Dynamic named range can be defined for multiple columns. For example, we have defined the column A and column F the dynamic named field with the following formula :
 =OFFSET(Company!$A$2,0,0,COUNTA(Company!$A:$A),6)

excel dynamic named range for multicolumn

Since the formula contains 6 columns, the number 6 was used as the width.

Let's make an example of get data from Company sheet to Invoice sheet using formula ;
- First, let's create a dynamic data validation list in cell B2 in Invoice .For this, we have defined the Companies name.
Companies =OFFSET(Company!$A$2;0;0;COUNTA(Company!$A:$A)-1)

While cell B2 is selected, = Companies is entered into the Source section of the Data _ Data Validation _ List menu.


- Let's create a dynamic named range that covers multiple columns.
  Company_List =OFFSET(Company!$A$2,0,0,COUNTA(Company!$A:$A),6)

Let's enter the necessary formulas into cells B3, B4, B5, B6 and B7.

  For B3 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,2,FALSE)),"",VLOOKUP(B2,Company_List,2,FALSE))
  For B4 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,3,FALSE)),"",VLOOKUP(B2,Company_List,3,FALSE))
  For B5 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,4,FALSE)),"",VLOOKUP(B2,Company_List,4,FALSE))
  For B6 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,5,FALSE)),"",VLOOKUP(B2,Company_List,5,FALSE))
  For B7 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,6,FALSE)),"",VLOOKUP(B2,Company_List,6,FALSE))

excel veri doğrulama listesi benzersiz sıralı değerler içerir

Excel Alfabetik Sıralama Formülü

Excel Sütunu Formül İle Alfabetik Sıralama



        Excel formülleri kullanarak , A sütunundaki verileri F sütununa alfabetik olarak (A-Z) sıraladık.
İlk olarak , Formüller Ad Yöneticisi menüsünden A2:A16 hücreleri için Liste adında bir isim tanımladık.


Tanımladığımız Ad  :     Liste==Sayfa1!$A$2:$A$16 

Daha sonra sütundaki verileri sıralamak için formülümüzü oluşturduk :
=İNDİS(Liste;KAÇINCI(KÜÇÜK(EĞERSAY(Liste;"<"&Liste);SATIR(1:1));EĞERSAY(Liste;"<"&Liste);0))

Herhangi bir hücre seçilerek , formül çubuğuna yukarıdaki formülü yapıştırdıktan hemen sonra dizi formül olduğu için CTRL+SHIFT+ENTER tuşlarına aynı anda basarız . Formülün başına ve sonuna { } karakterlerinin eklendiğini gözlemleyebilirsiniz.
        Hücrede, örneğimizdeki A harfi ile başlayan ilk değer olan ASİYE AÇ ismi görüntülenir. Şimdi bu hücre seçili iken ,hücrenin sağ alt köşesine geldiğimizde  + işareti belirir , + işareti ekrandan kaybolmadan sağ alt köşeden tutarak aşağıya doğru sürükleme işlemini yaparız ve işte isimler alfabetik olarak sıralanmış oldu.


Ad tanımlama yapmadan da direkt olarak formül şu şekilde pratik olarak kullanılabilir :
=İNDİS($A$2:$A$16;KAÇINCI(KÜÇÜK(EĞERSAY($A$2:$A$16;"<"&$A$2:$A$16);SATIR(1:1));EĞERSAY($A$2:$A$16;"<"&$A$2:$A$16);0))

Excel'de formül ile veri sıralamayı dinamik hale getirmek için örneğin ; A2:A16 aralığındaki hücrelerden birinin veya birkaçının değeri değiştiğinde , sıralamayı hemen güncellemek için şu sıralama formülü kullanılabilir :
 =EĞERHATA(İNDİS($A$2:$A$16;KAÇINCI(KÜÇÜK(EĞERSAY($A$2:$A$16;"<"&$A$2:$A$16);SATIR(1:1));EĞERSAY($A$2:$A$16;"<"&$A$2:$A$16);0));"")

Bazı durumlarda alfabetik sıralamak istediğimiz sütunda boş hücre veya sayı olabilir ;

 bu gibi durumlarda ,düzgün bir sıralama sağlamak için formülümüzü şu şekilde kullanabiliriz :

=EĞERHATA(İNDİS($A$2:$A$16;KAÇINCI(KÜÇÜK(DEĞİL($A$2:$A$16="")*EĞER(ESAYIYSA($A$2:$A$16);EĞERSAY($A$2:$A$16;"<="&$A$2:$A$16);EĞERSAY($A$2:$A$16;"<="&$A$2:$A$16)+TOPLAM(--ESAYIYSA($A$2:$A$16)));SATIRSAY($A$2:A2)+TOPLAM(--EBOŞSA($A$2:$A$16)));DEĞİL($A$2:$A$16="")*EĞER(ESAYIYSA($A$2:$A$16);EĞERSAY($A$2:$A$16;"<="&$A$2:$A$16);EĞERSAY($A$2:$A$16;"<="&$A$2:$A$16)+TOPLAM(--ESAYIYSA($A$2:$A$16)));0));"")

Örnek dosyaları buradan indirebilirsiniz:excel veri doğrulama listesi benzersiz sıralı değerler içerir