Excel'de basit veritabanı sorguları için INDEX ve MATCH kullanın

Aslen Excel, gerçek bir veritabanı olarak tasarlanmamıştı. Erken veritabanı fonksiyonları nicelik ve nitelik olarak sınırlandırılmıştır. Ve bir Excel veritabanındaki her kayıt bir kerede ekranda görülebildiğinden, bir kerede tüm anıların bellekte olması gerektiği anlamına gelir - Excel veritabanları çok küçük olmak zorundaydı: birkaç kayıt içeren çok sayıda alan veya çok sayıda kayıt içeren birkaç alan; ve en küçük hesaplamalar

VLOOKUP (dikey) ve HLOOKUP (yatay), belirli bir bilgi için bir veritabanını sorgulamak için kullanılabilen tek işlevlerdir. Örneğin, 1000 $ 'dan daha büyük ancak 5000 $' dan daha az olan ancak sadece düz dosyalarda (yalnızca tek bir veritabanı matrisi) bulunan tüm kayıtları bulmak ve çıkarmak için sorgulayabilirsiniz.

Pivot Tablolar, kullanıcıların ilişkisel veritabanları oluşturabilmeleri için geliştirilmiştir. sorgulamak, daha az bellek kullanmak ve daha doğru sonuçlar sağlamak daha kolay. Bununla birlikte, ilişkisel bir veritabanınız yoksa veya buna ihtiyacınız yoksa, daha güçlü ve güvenilir veritabanı işlevlerine ihtiyacınız varsa, bunları bir başlangıç ​​için deneyin.

[Daha fazla okuma: Yeni PC'niz bu 15 ücretsiz, mükemmel programa ihtiyaç duyar]

Dizin, Eşleşme ve Dizin Eşleştirme

Excel'de, INDEX işlevi belirli bir konumdan bir öğe döndürür (bir liste, tablo, veritabanı).

MATCH işlevi bir değerin konumunu döndürür (bir listede , tablo, veritabanı). Ve birlikte kullanılan INDEX-MATCH fonksiyonları bir tablodan bir esinti çıkarmayı kolaylaştırır.

INDEX işlevinin sözdizimi şöyledir: INDEX (dizi, satır_sayısı, [sütun_sayısı]). Dizi, birlikte çalıştığınız hücre aralığıdır. Row_num, aradığınız veriyi içeren aralıktaki satır numarasıdır. Column_num, aradığınız verileri içeren aralıktaki sütun sayısıdır. INDEX formülü sütun harflerini tanımıyor, bu nedenle sayıları kullanmalısınız (soldan sayma).

MATCH işlevi için sözdizimi şöyledir: MATCH (lookup_value, lookup_array, [match_type]). Lookup_value, değer, mantıksal bir değer veya hücre referansı olabilecek, aradığınız sayı veya metin. Lookup_array, birlikte çalıştığınız hücrelerin aralığıdır. Match_type, MATCH işlevini belirler; yani tam eşleşme veya en yakın eşleşme.

A. INDEX fonksiyonu

Örneğimizde, ünlü Commodore James Norrington'ın Karayipler'deki tüm korsan gemileri izleyen bir e-tablosu var. Norrington'ın listesi, geminin bölgedeki deniz haritaları ile eşleşen savaş formasyonları tarafından düzenleniyor. Bir gemiyi ilerlediğinde, Index formülünü e-tablosuna girer, böylece gemiyi ve kapasitelerini tanımlayabilir. Bu ilk sorguda Norrington, ilerleyen geminin türünü bilmek istiyor.

1. Sorgularınız için bir konum (hücre veya hücre aralığı) seçin (diğer bir deyişle, işlevler ve sonuçlar), ardından imlecinizi bu hücreye taşıyın. Örneğin: 18. satırdaki herhangi bir hücre.

2. INDEX işlevini (önce eşittir işareti ile) ve bir açılış parantezini girin, ardından aşağıdaki gibi veritabanı / tablo aralığını vurgulayın (veya yazın): = INDEX (A2: I16

Not: Mutlak bir referans istiyorsanız Bu durumda, eğer / kopyalanırsa, aralık değiştirilmezse, formülü sabit kodlamak demektir, her hücre referansından sonra F4'e basın. Ayrıca menzili de vurgulayabilirsiniz: Tam menzili seçtikten sonra sadece F4'e basın ve mutlak referans sembolleri eklenir.

3. Ardından, argümanları ayırmak için virgül (yani, ayrı formül parçaları) girin, ardından satır numarasını ve virgül numarasını ve ardından sütun numarasını girin (evet) , her zamanki sütun harfi ve doğru parantez değil, bir sayı olmalı ve (veya Enter tuşuna basın ve Excel'in sizin için son parantezi eklemesine izin verin.) Tam formül şu şekilde görünür: = INDEX ($ A $ 2: $ I $ 16) , 15,2).

Not: Satır numaralandırması, e-tablodaki ilk sayı değil, aralıktaki ilk sayı ile başlar. Her ne kadar Cavalleria korsan gemisi, Excel satır 16'da olsa da, aslında formülümüzdeki satır 15, çünkü aralığımız A2'de başlıyor ve I16'dan geçiyor. A2 satır 1 ise, A16 sıra 15).

4. Norrington'un aradığı gemi tipinin bir War Sloop olduğunu unutmayın.

JD Sartain

Veritabanınızdaki belirli bilgileri bulmak için dizin işlevini kullanın.

B. INDEX aralıkları

Şimdi, bu veritabanı tablosu ile daha fazlasını yapabiliriz. Bir şeyi bilmek istediğiniz her zaman aralığı yeniden tanımlamanız gerekmez. Bunu kolaylaştırmak için, aralığı bir kez tanımlayacağız ve daha sonra adlandıracağız. O zaman aralığın ismini formüllerimiz arasına koyabiliriz.

1. A2'ye gidin ve A2 ile I16 arasındaki aralığı vurgulayın.

2. Formüller sekmesinden, Tanımlanmış Adlar grubundan Ad Tanımla öğesini seçin.

3. Açılan iletişim kutusunda, Ad alanı kutusuna aralığı için bir ad girin.

4. Ardından, Çalışma kitabındaki ya da Çalışma Kitabındaki çalışma sayfalarından biri olan Kapsamı (aralığın bulunduğu yer) girin.

5. Gerekirse bir yorum girin.

6. Ve son olarak, To the To To To alanın doğru ismi ve aralığı gösterdiğini doğrulayın, ardından OK 'a tıklayın.

7. Aralığınızın gerçekten Excel'de kaydedildiğini doğrulamak isterseniz, bu küçük testi deneyin: Ctrl + G'ye (GoTo komutu) basın. GoTo iletişim kutusunda Gemiler 'ı seçin, sonra OK ' a tıklayın ve Excel, A2: I16 aralığını yeniden vurgular.

C aralığı nasıl tanımlanır ve kaydedilir. SUM & AVERAGE formülleriyle INDEX

Norrington, filosunun savaş yeteneklerini değerlendiriyor. Öncelikle korsanların kaç tane topladığını, gemi başına düşen ortalama top sayısı ve tüm bu korsan gemilerini yöneten toplam mürettebat sayısını bilmek istiyor. Aşağıdaki formülleri girer:

1. = SUM (INDEX (Gemiler, 8)) toplamda 334, topların toplam sayısı ve

2'dir. = AVERAGE (INDEX (Gemiler, 8)), gemi başına 22.27 veya yaklaşık 22.27 top sayısına eşittir.

3. = SUM (INDEX (Gemiler, 7)), tüm gemilerdeki toplam mürettebatın toplamı olan 2350'ye eşittir.

Gemilerle sayı 8 arasında neden bir virgül, boşluk, virgül var ve bu rakamlar ne anlama geliyor? Gemiler Range (virgülle takip edilir), Row argümanı boş (veya boşluk) çünkü Norrington tüm satırları istiyor, ve 8 de 8. sütunu temsil ediyor (sütun H, Cannons).

Bazıları Neden bu sütunların alt kısmına SUM ve / veya ORTALAMA formülleri girilmiyor? Bu küçük elektronik tabloda, evet, bu kadar kolay olurdu. Ancak elektronik tablo 5000 satır ve 300 sütun içeriyorsa, SUM ve ORTALAMA kullanarak INDEX

JD Sartain

03 INDEX formüllerini kullanmak isteyeceksiniz.

Aralık seçildiğinde, Norrington boş bir elektronik tabloyu açabilir. Bu aynı çalışma kitabında ve B sütununda (formüllerin yerine sonuçları gösterir) kendi sorgularını (formüller), bu sorguları A sütununda tanımlayan bir açıklama ile yazın (Not: C sütunu, B sütunundaki gerçek formülleri gösterir).

5000 kayıtlarının büyük veritabanını görsel olarak görmek zorunda kalmaz ya da formüller hesaplanırken birkaç saniye beklemek zorunda kalmaz. Sorgu Sayfasından ihtiyaç duyduğu tüm bilgileri alabilir. Unutmayın, e-tablo ne kadar büyükse, daha yavaş çalışır, özellikle de çok fazla formül varsa.

JD Sartain

04 Commodore James Norrington'ın korsan gemisi bilgi / sorgu sayfası.

D. MAX

ile INDEX MATCH Şimdi, Norrington en çok korsan gemide kaç tane korsanın olduğunu ve hangi geminin olduğunu bilmek istiyor? En yüksek korsan sayısını elde etmek için INDEX'i MAX formülü ile kullanır, ancak aynı zamanda hangi geminin onları taşıdığını da bilmesi gerekir. Bu yüzden gemide en fazla korsanın bulunduğu gemiyi bulmak için INDEX / MATCH'yi MAX formülü ile kullanır.

1. = MAX (INDEX (Gemiler, 7)) 300, gemilerin en yüksek korsan sayısı

2'dir. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Gemiler)), $ G $ 2: G $ 16, 0)), Royal Korsan, geminin en korsanları olan gemiye

3. = İNDEKS ($ F $ 2: $ F 16 $, MATCH (MAX (Gemiler), $ G $ 2: G 16 $, 0)) eşittir Stede Bonnet, Royal James Kaptanı 300

JD Sartain

Veritabanınızdan belirli bilgileri almak için INDEX-MATCH ve MAX kullanın.