Excel Notlarım

User Defined Function

Açıklaması şöyle

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window

2. ALT-I ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above

2. clear the code out

3. close the VBE window

Örnek

Şöyle yaparız

Public Function noemail(s As String) As String
    Dim i As Long, L As Long

    If InStr(s, "@") = 0 Then
        noemail = s
        Exit Function
    End If
    
    L = Len(s)
    For i = L To 1 Step -1
        If Mid(s, i, 1) = "," Then
            noemail = Left(s, i - 1)
            Exit Function
        End If
    Next i
    
End Function

Kullanmak için şöyle yaparız

=noemail(A1)

Data Menüsü
Remove Duplicates
Seçili sütunlardaki çift değerleri siler.

Tabloya Çevirmek
Şöyle yaparız.

Turn the data into an Excel Table like this
1.select the data and hit Ctrl - T
2.select the data and click Insert > Table
3.Tick the box for "My table has headers".
Now you can use the drop-down commands in each table header to sort (and/or filter) by that column.

View Menüsü
Freeze Pane
Bazı satırların hareket etmemesini sağlar.

F4
Bir satırı en soldaki numarasına sağ tıklayıp sil. Silme işlemi hatırlanır. Başka satıra gidip F4'e basarsam o satır da silinir.

Replace
Ctrl+h ile kutusu açılır. Kullanırken "Match case" ve "Match Entire Cell Content" seçilebilir.

Fonksiyonlar
Addres

Açıklaması şöyle

You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number arguments for the ADDRESS function.

Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

And

TRUE ve FALSE alır. Sonuç olarak TRUE, FALSE döner.

=IF(AND(R1,S1),"Yes","No")

COUNTIF
Verilen koşulu sağlayan hücrelerin sayısını verir. Örnek a harfini içeren hücreleri sayıyor.

=COUNTIF(A1:A7,"*a*")

FILTER

Şöyle yaparız. Açıklaması şöyle. A1:A5 arasında 5'ten küçük sayıların sayısını verir

Separating conditions by * will act as AND, while separating them by + will work as OR.

=COUNT(FILTER(A1:A5,(A1:A5<5) + (B1:B5<5)))

IF
Verilen koşulu sağlarsa çıktı olarak ilk değeri sağlamazsa ikinci değeri verir.

=IF(SUM(B2:B30)>0,1,0)

LEFT
Örnek

Şöyle yaparızz

=LEFT(D2,LEN(B2))

Örnek

Şöyle yaparız. Soldaki iki karakteri al. Sağdaki geri kalan karakterleri alır. Sağdaki kısmı sayı yapar yani baştaki sıfırları atarr ve iki değeri birleştirir.

=LEFT(A1,2) & VALUE(RIGHT(A1,LEN(A1)-2))

SUBSTITUTE
Örnek
Şöyle yaparız. Virgül yerine "" yazar..

=SUBSTITUTE(A1,",","")

TEXTJOIN - Delimiter, Ignore Empty + Text

Örnek

Şöyle yaparız. Burada Filter'a verilen ilk parametre verinin aralığı, ikinci parametre key değerlerin aralığı, Key değerleri E2 ile aynı olanları döndürür. Bu değerleri TEXTJOIN ile birleştiririz

=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$7,$C$2:$C$7=$E2))

Elimizde şöyle bir veri olsun

| ID | Status   |
|----|----------|
|  1 | Complete |
|  2 | Active   |
|  3 | New      |
|  4 | New      |
|  5 | New      |
|  6 | Complete |

Çıktı olarak şunu alırız

| Status   | IDs     |
|----------|---------|
| Complete | 1, 6    |
| Active   | 2       |
| New      | 3, 4, 5 |

VLOOKUP
Örnek
Şöyle yaparız.

=VLOOKUP($B81,sheet2!$A$2:$M$281,2,FALSE) 

- Birinci parametre aranılan değerdir. $B81 aranılan değeri belirtir.
- İkinci parametre üzerinde arama yapılacak tablodur. sheet2!$A2:$M281 tabloyu belirtir.
- Üçüncü paramtre tablodaki bir satırda istenilen değer bulunursa hangi sütunun sonuç olarak bana döndürüleceğini belirtir. Bu örnekte sonuç olarak ikinci sütunu istiyoruz
- Dördünce parametre aranılan değerin tam olarak veya kısmi olarak bulunucağını belirtir. Açıklaması şöyle. Bu örnekte tam eşleşme bekleniyor.

The last argument of the VLOOKUP function determines the nature of your look up.

1 - TRUE will return an approximate match which is useful when you want to look up a value from group of ranges in ascending order;

0 - FALSE will return an exact match which will return the first match based on your look up criteria.