17 Gelişmiş Excel Formülü – Tüm Profesyonellerin Bilmesi Gereken Bir Şey

Kendinizi sık sık ellerinizi esnetiyor ve Excel'de manuel olarak bir şeyler yapmak için saatler harcıyorsanız, doğru şekilde nasıl kullanacağınızı biliyorsanız Excel'in ne kadar güçlü olabileceğini kaçırıyorsunuz. Microsoft Excel, sürece çok fazla manuel müdahale gerekmeden hücredeki çok sayıda veri üzerinde verimli bir şekilde çalışabilen formülleriyle bilinir.

Uzun, sıradan, manuel görevleri birkaç saniyelik çalışmaya dönüştürmek için esrarengiz bir yeteneğe sahip 17 gelişmiş excel formülünü derledik. Bunlar, değerli zamanlarından tasarruf etmek veya kritik bir anda patronlarını etkilemek için her profesyonelin parmaklarının ucunda olması gereken formüllerdir.

Listeyi gözden geçirin ve bize favorinizi söyleyin!

Öğrenciler, en yüksek %400'e kadar olmak üzere ortalama %58 Maaş zammı alırlar.

1. İNDEKS MAÇI

Formül = İNDEKS(C3:E9,KAÇINCI(B13, C3:C9,0),KAÇINCI(B14,C3:E3,0))

Excel'deki DÜŞEYARA veya YATAYARA formülüne, arama görevlerini gerçekleştirmek için bazı dezavantajları olan mükemmel bir alternatif. INDEX MATCH, 2 ayrı işlevin bir kombinasyon formülüdür:

İNDEKS: Bu formül, sütun ve satır numarasına göre tablodaki bir hücrenin değerini döndürür.

MATCH: Bu formül, bir satır veya sütundaki bir hücrenin konumunu döndürür.

İNDEKS ve KAÇINCI formüllerinin birleşimine bir örnek: Bir kişinin adını temel alarak ararken ve boyunu döndürürken, INDEX MATCH formülünü kullanarak her iki değişkeni de (bu durumda ad ve boy) değiştirmek için bu formülü kullanabilirsiniz. .

Kaynak

Adım adım parçalara ayıralım:

INDEX ve MATCH Nasıl Birleştirilir

• INDEX yazın
• INDEX'e eklemek istediğiniz Alanı seçin
• Alanı F4 ile kilitleyin
• Verilerini aramak istediğiniz Satırı bulun
• Bilgi dizisiyle MAÇ yazın ve alanı F4 ile kilitleyin
• Tam eşleşme için 0 yazın, parantezleri kapatın
• Enter'a basın

İlginç Veri Bilimi Proje Fikirleri

Artık, doğru veriler için tüm hücrelerin ve satırların taranmasının otomatik olarak yapıldığı, sayfada tamamen dinamik ve işlevsel bir sütun ve satır kümesine sahipsiniz.

2. ORTALAMA

Herhangi bir sayı aralığının ortalamasını bulmak üzere ortalama formülü uygulamak için izlemeniz gereken adımlar şunlardır:

Hesapladığınız değerleri, hücreleri veya hücre ölçeğini biçimde girin.

Formül =ORTALAMA(sayı1, sayı 2, vb.) ile başlamalıdır.

Sayfadaki bir hücre aralığının ortalamasını yapmak istiyorsanız, izlemeniz gereken adımlar şunlardır:

• Hesapladığınız değerleri, hücreleri veya hücre ölçeğini, tıpkı yukarıda yaptığınız gibi formatta girin.
• Formül, =ORTALAMA(Başlangıç ​​Değeri: Bitiş Değeri) gibi olacaktır.

Hücrelerin bölge değerlerini nasıl gireceğinizi merak ediyorsanız, sayfanızdaki alanı farenizle seçip F4 ile kilitleyebilirsiniz. Bu, Excel'in gruptaki öğe sayısı ile şeklin herhangi bir toplamını ve bölünmesini yapmadan işin geri kalanını sizin için yapmasını sağlayacaktır.

3. ETOPLA

Excel'deki bu formül, SUMIF(aralık, ölçüt, [toplam aralık]) olarak gösterilir. Bu, sizin tarafınızdan belirlenen gereksinimleri karşılayacak istenen hücre aralığı içindeki değerlerin toplamı ile sonuçlanacaktır. Örneğin, =SUMIF(C3: C12, “>70.000), yalnızca değeri 70.000'den fazla olan hücrelerden C3 ve C12 hücreleri arasındaki değerlerin toplamını döndürür.

Finansman, maaş ve hatta maliyet hesaplamaları söz konusu olduğunda, sizin tarafınızdan belirlenen koşul tarafından belirlenen belirli çalışanlarla ilişkili müşteri adaylarının değerine ihtiyacınız vardır. Bunu manuel olarak yapmak çok zaman alıcıdır ve işleri yavaşlatır.

Yukarıdaki koşul için formül =SUMIF(aralık, ölçüt, [toplam_aralık]) olabilir; burada Aralık, değerleri seçmeniz gereken sayfanın Aralığı olarak tanımlanabilir.

[sum_range], girilen ilk Aralığa ek olarak ekleyeceğiniz ek veya isteğe bağlı Aralık olarak tanımlanır.

İşte bir örnek:

Kaynak

4. TOPLAM İLE BİRLEŞTİRİLMİŞ OFSET

Tek başına, KAYDIR işlevi kullanışlı olmayabilir, ancak diğer hizmetlerle birleştirildiğinde, herhangi bir değişken sayıda hücreyi toplayabilen dinamik bir rol oluşturmak istiyorsanız, daha hızlı sonuçlar veren karmaşık bir formül elde edebilirsiniz. statik, OFFSET işleviyle birleştirilmelidir.

Bu nedenle, değişken hücrelerdeki değerlerin toplamını bulmak için formül şöyle olmalıdır:

=TOPLA(B4:OFFSET(B4,0,E2-1))

Kaynak

Burada SUM İşlevinin bitiş referansı OFFSET işleviyle değiştirilir. B4 ile başlayan TOPLA formülü bir değişkenle biter ve B4 ile başlayan bir KAYDIR formülüdür ve E2 ("3") eksi bir değerinden devam eder. Bu, yöntemin iki hücre üzerinde hareket etmesine ve üç yıllık verileri toplamasına yardımcı olur. Yukarıdaki referansta, F7 hücresinin B4: D4 = 15 hücrelerinin toplamını tuttuğunu görebilirsiniz.

5. EĞER VE

Bu formül, bir yığın veriyi taramak için belirli koşullar oluşturmanız gerektiğinde kullanışlıdır. EĞER ifadesi, halihazırda var olan bir iz üzerinde bu koşullara dayalı olarak yeni bir alan oluşturmak için gelişmiş excel EĞER işlevinin kullanılmasına yardımcı olur.

Örneğin, maaşları 50 binin üzerinde ve çalışan kimliği 3'ten büyük olan çalışanları işaretlemek istiyorsanız, formül şöyle olacaktır:

EĞER(VE(E4>3,F4>50000)1,0)

Kaynak

Yukarıdaki verilerde gerçek durum olmadığından formül 0 değerini döndürür.

6. BİRLEŞTİR

Veri sayfanızda çok sayıda metin dizeniz varsa ve verilerin tek satırda görünmesini istiyorsanız, bu formül sizin için idealdir. Örneğin, çalışan kimliğini ve Çalışan adını tek bir sütunda göstermek istiyorsanız, yöntem şu şekilde olmalıdır:

=BİRLEŞTİR(B3, C3)

Kaynak

7. PMT

Bu işlev, belirli bir faiz oranı, anapara tutarı ve kredinin süresi göz önüne alındığında, bir kredinin gelecekteki ödemelerini hesaplamanıza yardımcı olacaktır. İşte başlamanız gerekenler:

• Kredinin vadesi
• Kredinin başlangıç ​​anaparası (parası)
• gelecekteki değer
• kredi türü

Şimdi, bir anapara tutarı için aylık ödemeyi bulmak istiyorsanız, bunun formülü şöyle olacaktır:

= PMT (oran, başına, PV, [fv], [tür])

Bunu bir örnekle anlayalım:

Kaynak

=PMT(C2/12.B2.A2)

Ve en iyi yanı, tüm alanlar için aylık ödeme tutarını otomatik olarak hesaplamak için PMT hücrelerinin sağ alt köşesini alanlar boyunca sürükleyebilirsiniz!

Popüler Veri Bilimi İş Türleri

8. KIRMIZI

Bu, alanlardaki istenmeyen boşlukları temizleyen excel'de en çok kullanılan formüllerden biridir. Örneğin: Bir ismin başındaki boşlukları kaldırmanız gerekiyorsa, bunu TRIM işlevini kullanarak yapabilirsiniz:

=KES(C6)

Kaynak

Bu size cephede ve uçta fazladan boşluk bırakmadan Chandan Kale'nin değeriyle geri döner.

9. UZUNLUK

Bu, bir metin dizisindeki karakter sayısını size söyleyen bir işlevdir. Bunu kullanmanın en heyecan verici yollarından biri, örneğin, bağış sütunundaki rakamları sayarak 1.000 doların üzerinde bağış yapan bağışçıları vurgulamak istiyorsanız, formül şöyle olacaktır:

=UZUNLUK(B2)

Kaynak

Bağış sütunundaki tüm hücreleri vurgulamak istiyorsanız, şunları yapmanız gerekir:

• Menüden Ana Sayfa sekmesini seçin
• Koşullu Biçimlendirme'ye tıklayın (araç çubuğunda)
• Hangi hücrelerin biçimlendirileceğini belirlemek için Bir Formül Kullan'ı seçin.

Kaynak

Burada, ">3" koşulunu yaparsanız, 1.000 ABD Dolarının üzerindeki herhangi bir şey, Biçim seçeneğini tıklatarak seçebileceğiniz benzersiz biçimlendirmeyi alır.

10. SEÇ

Bu, finansal modellemede senaryo analizi için harika bir işlevdir. Belirli sayıda seçenek arasından seçim yapmanızı ve seçtiğiniz “seçimi” geri döndürmenizi sağlar. Örneğin, varsayımlara dayalı olarak gelecek yıl gelir artışı için üç farklı değeriniz varsa, SEÇ işlevini kullanarak, ihtiyacınıza göre tutarı iade edebilirsiniz.

Formül:

=SEÇ(C7,D3,D4,D5)

Kaynak

11. HÜCRE, SOL, ORTA ve SAĞ

Yukarıdaki işlevlerin tümü, bazı gelişmiş formüller elde etmek için birçok şekilde birleştirilebilir.

• HÜCRE işlevi, hücrenin içeriği hakkında farklı türde bilgileri döndürmek için kullanılır.
• LEFT hizmeti, hücrenin başından itibaren metni değiştirmek için kullanılır.
• MID işlevi, hücrenin herhangi bir başlangıç ​​noktasından metin döndürebilir.
• SAĞ işlevi yalnızca hücrenin sonundaki metni döndürür.

Kaynak

12. XNPV ve XIRR

Yatırım bankacılığı, hisse senedi araştırması veya nakit akışlarının indirilmesini gerektiren diğer herhangi bir kurumsal finans alanıyla karşılaşan tüm analistler için, bu formüllerin size çok fazla zaman kazandıracağından ve homurdanmaktan kesinlikle kurtulacaksınız!

Veri Bilimi Mülakat Soruları ve Cevapları

Örneğin, belirli bir iskonto oranı ve düzensiz aralıklarla meydana gelen nakit akışları kullanarak herhangi bir yatırım için Bugünkü Net Değeri (NPV) hesaplamak istiyorsanız, aşağıdaki işlevi kullanın.

=XNPV(indirim oranı, nakit akışları, tarihler)

Kaynak

Öte yandan, XIRR işlevi, düzensiz aralıklarla meydana gelen bir dizi nakit akışı için dahili getiri oranını (dışarı çıkış = giriş) söyler, örneğin:

13. COUNTA ( )

Analistler genellikle, değerlere (sayılar, hatalar, metin, mantıksal değerler) sahip ve boş olan hücrelerin sayısını bulmaya çalışırken parmak uçlarında bulunurlar. COUNTA( ) işlevi, seçili bir aralıktaki boş olmayan hücrelerin adını bulmanıza yardımcı olabilir. Örneğin, A1-A10 sütunlarına sahip bir veri sayfası için değerleri sayma formülü şöyledir:

=COUNTA(A1: A10)

Kaynak

14. FV

Bir şeye para yatırmak ve değerini bilmek istiyorsanız bu formül kullanışlıdır. FV formülünün gereksinimleri şunlardır:

• Kredinin faiz oranı
• Ödeme Sayısı
• Her dönem için ödeme
• Mevcut Başlangıç ​​Bakiyesi
• Kredi Türü

Örneğin, birkaç boş CD'yi karşılaştırmak istiyorsanız ve bir CD'ye yatırım yapmak için 20.000$'lık bir mirasınız varsa. Faiz oranları ondalık biçimde gösterilir; ödemeler sıfır. Senaryonun formülü şöyle olacaktır:

=FV(A2/12,B2,C2,D2)

Kaynak

Sonuçlar:

Kaynak

15. RANDBETWEEN

Bu işlev, önceden tanımlanmış bir sayı aralığında rastgele bir sayı seçmeye yardımcı olur. Formüle en düşük ve en yüksek sayıları koyduğunuzda, Excel, Aralıktaki adların eklendiği alanlardan doğru verileri seçebilir ve bunlardan rasgele seçim yapabilir. Senaryo için yöntem:

=RANDBETWEEN(başlangıç ​​noktası, bitiş noktası)

Kaynak

16. KÜÇÜK

Excel'deki KÜÇÜK işlevi, önem derecesine göre sıralanmış bir listedeki değerin konumuna göre sayısal değerler döndürür. Bu işlev, bir diziden veya en küçük değer, 2. en düşük değer, 3. en düşük değer vb. gibi hücre aralığından "n. en küçük değerleri" almaya yardımcı olur.

Formülün sözdizimi şu şekildedir:

=KÜÇÜK (zaman, aralık)

Örneğin,

Kaynak

KÜÇÜK işlevi otomatik olduğundan, sıralanan değeri belirtmek için 'nth' için bir aralık ve bir tamsayı sağlamanız gerekir. Bu argümanların resmi isimleri 'dizi' ve 'k'dir.

17. ÇEYREK

Bu işlev, belirli bir veri kümesindeki çeyreği (dört eşit grubun her biri) döndürür ve minimum değeri, birinci çeyreği, ikinci çeyreği maksimum değerini döndürebilir. Bu işlev, bir dizideki alanların dörtte birlik miktarını getirir. İşlev, istenen yüzdelik dilime göre sayısal bir değer döndürür.

Sözdizimi: =QUARTILE (dizi, quart)

Kaynak

ÇÖZÜM:

21. yüzyıl iş yeri söz konusu olduğunda Microsoft Excel oldukça kaçınılmazdır, ancak işin püf noktası, bu kadar göz korkutucu olmak zorunda olmamasıdır. Onu arkadaşın yap ve üretkenliğinin artmasını izle!