Bir süredir Excel ipuçlarını okuyorsanız, Excel DÜŞEYARA yerine Excel INDEX () ve MATCH () işlevlerini kullanmaktan bahseden birini her zaman bulmuşsunuzdur. Kendi adıma konuşursak, aynı anda İKİ yeni işlevi denemek ve ustalaşmak her zaman çok zordu. Ama bu harika bir numara. Bana beş dakika verin ve bunu basit bir İngilizce ile açıklamaya çalışayım.
DÜŞEYARA'nın 30 saniyelik incelemesi
Çalışan kayıtları tablonuz olduğunu varsayalım. İlk sütun bir çalışan numarasıdır ve kalan sütunlar çalışanla ilgili çeşitli verilerdir. Çalışma sayfasında bir çalışan numaranız olduğunda, çalışanla ilgili belirli bir veriyi döndürmek için DÜŞEYARA kullanabilirsiniz. Sözdizimi DÜŞEYARA şeklindedir (değer, veri aralığı, sütun no., YANLIŞ). Excel'e, "Veri aralığına git. Veri aralığının ilk sütununda (değer) bulunan bir satır bulun. Bu satırdan (sütun no.). Değeri döndürün. Asıldığını öğrendikten sonra, çok basit ve güçlü.
Sorun
Bir gün, çalışanın ismine sahip olduğunuz, ancak çalışan numarasına ihtiyacınız olduğu bir durumla karşılaşırsınız. Aşağıdaki resimde A10'da bir adınız var ve çalışan numarasını B10'da bulmanız gerekiyor.
Anahtar alan, almak istediğiniz verinin sağındayken DÜŞEYARA çalışmayacaktır. Yalnızca DÜŞEYARA sütun numarası olarak -1'i kabul ederse, sorun olmaz. Ama öyle değil. Yaygın bir çözüm, geçici olarak yeni bir A sütunu eklemek, adların sütununu yeni A sütununa kopyalamak, DÜŞEYARA ile doldurmak, Özel Değerler Yapıştırmak ve ardından geçici A sütununu silmektir. Excel uzmanları muhtemelen bu hareketi uykularında yapabilir.
Size meydan okumayı ve bu tek adımlı yöntemi kullanmayı denemenizi önereceğim. Evet, formülü birkaç hafta duvarınıza yapıştırmanız gerekecek, ancak bunu DÜŞEYARA ile de uzun zaman önce yaptınız, değil mi?
Sanırım bunun bu kadar zor olmasının nedeni, muhtemelen daha önce hiç kullanmadığınız iki işlevi kullanıyor olmanızdır. Öyleyse, onu iki parçaya ayırmama izin verin.
İlk olarak, INDEX () işlevi vardır. Bu korkunç adlandırılmış bir işlevdir. Birisi "indeks" dediğinde, zihnimde bu işlevin yaptığına benzer bir şey çağrıştırmaz. Dizin, üç bağımsız değişken gerektirir.
=INDEX(data range, row number, column number)
İngilizce olarak, Excel veri aralığına gider ve (satır numarası). Satır ile (sütun numarası). Sütunun kesişimindeki değeri size verir. Hey, bir düşün - bu oldukça basit, değil mi? =INDEX($A$2:$C$6,4,2)size B5'teki değeri verecektir.
Bizim sorununa INDEX () uygulanması, bu aralıktan işçi sayısını döndürmek için karar veremiyorsun bu kullanırsınız: =INDEX($A$2:$A$6,?,1). Aslında, bu parçası o kadar önemsiz görünüyor ki işe yaramaz görünüyor. Ancak soru işaretini bir MATCH () işleviyle değiştirdiğinizde, çözüme sahip olursunuz.
İşte sözdizimi:
=MATCH(Value, Single-column data range, FALSE)
Excel'e, "Veri aralığını ara ve bana (veri) için bir eşleşme bulduğun göreceli satır numarasını söyle. Yani, A10'da çalışanın hangi satırda olduğunu bulmak için kullanırsın =MATCH(A10,$B$2:$B$6,FALSE). Evet, bu Dizinden daha karmaşıktır , ancak DÜŞEYARA profesyonellerinin tam ortasında olmalıdır. A10 "Miller, Bob" içeriyorsa, bu MAÇ, B2: B6 aralığının 3. satırında olduğunu döndürecektir.
İşte - MATCH () işlevi, Index işlevine hangi satıra bakılacağını söyler - bitirdiniz. Dizin işlevini alın, soru işaretimizi KAÇINCI işlevi ile değiştirin ve artık anahtar alan sol sütunda olmadığında DÜŞEYARA eşdeğerini yapabilirsiniz. İşte kullanılacak işlev:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Duvarımdaki yapışkan not aslında onu iki satır olarak gösteriyor. Önce MATCH () için açıklamayı yazdım. Bunun altına INDEX () için açıklamayı yazdım. Ardından, MATCH () işlevinin INDEX () işlevinin 2. bağımsız değişkenine düştüğünü belirtmek için ikisi arasına bir huni şekli çizdim.
İlk birkaç seferde bunlardan birini yapmak zorunda kaldım, sadece oraya yeni bir geçici A sütunu çarpmak istedim, ama bunun yerine bu şekilde yapmanın acısını yaşadım. Daha hızlıdır ve daha az manipülasyon gerektirir. Dolayısıyla, bir dahaki sefere DÜŞEYARA işlevine negatif bir sayı koyabilmeyi dilediğinizde, sorunlarınızı çözmek için bu garip DİZİN ve KAÇINCI kombinasyonunu deneyin.
