Excel'de grafikler nasıl bağlanır? Excel çalışma kitabı

Sütun verilerini bir tablodan diğerine taşımak için DÜŞEYARA işlevini kullandınız mı? Excel'in artık yerleşik bir veri modeli olduğundan, DÜŞEYARA işlevi kullanımdan kaldırılmıştır. İçlerindeki eşleşen verilere dayalı olarak iki tablo arasında bir ilişki oluşturabilirsiniz. Ardından, farklı kaynaklardan gelseler bile her tablodaki alanlarla Power View sayfaları veya PivotTable'lar ve diğer raporları oluşturabilirsiniz. Örneğin, müşteri satış verileriniz varsa, satış eğilimlerini yıl ve aya göre analiz etmek için içe aktarmak ve bağlantı oluşturmak isteyebilirsiniz.

Çalışma kitabındaki tüm tablolar PivotTable ve Power View alan listelerinde listelenir.

İlgili tabloları içe aktarırken ilişkisel temel excel verileri bu ilişkileri genellikle arka planda oluşturulan bir veri modelinde oluşturabilir. Diğer durumlarda, ilişkilendirmeleri manuel olarak oluşturmanız gerekir.

eğer takım İlişki kullanılamıyorsa, çalışma kitabı yalnızca bir tablo içerir.

Pencerede İlişkileri yönetimi düğmesine basın Yaratmak.

Pencerede bağlantı oluştur alanın yanındaki oku tıklayın Masa ve açılır listeden bir tablo seçin. Birden çoğa ilişkide, bu tablo çok üyeli bir parça olmalıdır. Müşteri ve zaman zekası örneğinde, her gün birçok satış olması muhtemel olduğundan, önce müşteri satış tablosunu seçmelisiniz.

eleman için Sütun (yabancı)öğeyle ilgili verileri içeren sütunu seçin. Örneğin her iki tabloda da tarih sütunu varsa şimdi o sütunu seçmelisiniz.

sahada İlgili tablo alanda seçilen tabloyla ilgili en az bir veri sütunu içeren bir tablo seçin Masa.

sahada İlgili sütun ( birincil anahtar) içeren sütunu seçin benzersiz değerler alanında seçilen sütundaki değerlere karşılık gelen Kolon.

Düğmeye bas TAMAM.

Excel'deki tablolar arasındaki ilişkiler hakkında daha fazla bilgi edinin

İlişki Notları

Örnek. Zaman zekası verilerini havayolu uçuş verileriyle ilişkilendirme

Microsoft Azure Marketplace'teki ücretsiz verilerle her iki tablo arasındaki ilişkileri ve zaman zekasını öğrenebilirsiniz. Bu veri kümelerinden bazıları çok büyüktür ve hızlı bağlantı internete.

Artık varış gecikmelerini takvimdeki diğer değerlerin yanı sıra yıl ve aya göre bölebilirsiniz.

tavsiye: Varsayılan olarak, aylar alfabetik sırayla listelenir. Power Pivot eklentisiyle, sıralama düzenini kronolojik sırada görünecek şekilde değiştirebilirsiniz.

Masa Temel TakvimABD Power Pivot penceresinde açık olmalıdır.

Ana tabloda tıklayın Sütuna göre sırala.

"Sırala" alanında şunu seçin: AyTakvim İçinde.

"Kime" alanında şunu seçin: Yılın Ayı.

PivotTable artık her "ay ve yıl" kombinasyonunu (Ekim 2011, Kasım 2011) yıldaki ay sayısına (10, 11) göre sıralar. Sıralama düzenini değiştirmek kolaydır çünkü kanal tarih akışı bu komut dosyasının çalışması için gerekli tüm sütunları sağlar. Farklı bir zaman istihbarat tablosu kullanıyorsanız, adımlarınız farklı olacaktır.

"Tablolar arası ilişkiler gerekli olabilir"

Alanlar eklendikçe Pivot tablo PivotTable'da seçilen alanlarla ilgilenmek için tablolar arasında bir ilişkinin gerekli olduğu konusunda bilgilendirileceksiniz.

Excel, bir bağlantının ne zaman gerekli olduğunu size söyleyebilse de, hangi tabloların ve sütunların kullanılacağını veya tablolar arasında bir bağlantının mümkün olup olmadığını söyleyemez. Sorularınıza yanıt almak için aşağıdakileri deneyin.

1. Adım: İlişkiye hangi tabloların dahil edileceğine karar verin

Modeliniz yalnızca birkaç tablo içeriyorsa, hangilerinin kullanılacağı açıktır. Ancak daha büyük modeller için yardıma ihtiyacınız olabilir. Bunun bir yolu, Power Pivot eklentisindeki Grafik Görünümü'nü kullanmaktır. Grafik Görünümü, veri modelindeki tüm tabloların görselleştirilmesini sağlar. Bununla, hangi tabloların modelin geri kalanından ayrıldığını hızlı bir şekilde belirleyebilirsiniz.

Not: PivotTable veya Power View raporunda kullanıldığında geçersiz olan belirsiz ilişkiler oluşturabilirsiniz. Tüm tablolarınız modeldeki diğer tablolarla bir şekilde ilişkili olabilir, ancak farklı tablolardan alanları birleştirmeye çalıştığınızda "Tablolar arası ilişkiler gerekli olabilir" mesajını alırsınız. En muhtemel nedeniçoktan çoğa bir ilişkiyle karşılaşmış olmanızdır. İhtiyacınız olan tablolara bağlanan tablolar arasında bir ilişkiler zinciri izlerseniz, tablolar arasında iki veya daha fazla bire çok ilişki olduğunu muhtemelen göreceksiniz. Her durumda çalışacak kolay bir geçici çözüm yoktur, ancak kullanmak istediğiniz sütunları tek bir tabloda birleştirmek için hesaplanmış sütunlar oluşturmayı deneyebilirsiniz.

2. Adım: Bir tablodan diğerine yol oluşturmak için kullanılabilecek sütunları bulun

Hangi tablonun modelin geri kalanıyla ilişkili olmadığını belirledikten sonra, modelin herhangi bir yerindeki başka bir sütunun eşleşen değerler içerip içermediğini belirlemek için o tablodaki sütunları inceleyin.

Bölgeye göre ürün satışlarını içeren bir modeliniz olduğunu ve ardından her bölgedeki satışlar ile demografik eğilimler arasında bir korelasyon olup olmadığını görmek için demografik verileri içe aktardığınızı varsayalım. Demografik veriler çeşitli kaynaklardan geldiğinden, tabloları başlangıçta modelin geri kalanından izole edilir. Demografik verileri modelinizin geri kalanıyla entegre etmek için, halihazırda kullanmakta olduğunuz demografik tablolardan birinde bir sütun bulmanız gerekecektir. Örneğin, demografik veriler bölgeye göre düzenlenmişse ve satış verileriniz bir satış bölgesini tanımlıyorsa, bir arama sağlamak için eyalet, posta kodu veya bölge gibi ortak sütunları arayarak iki veri kümesini ilişkilendirebilirsiniz.

Eşleşen değerlere ek olarak, ilişkilendirme oluşturmak için birkaç ek gereksinim vardır.

Eğer ihtiyacımız olursa Verileri bir Excel elektronik tablosundan diğerine otomatik olarak aktarın, bu tabloları bağlamanız gerekir. Excel'de iki tablodaki verileri bağlamanın birkaç yolu vardır. Tek Yön - bir formül kullanarak, "Excel'de tablolar nasıl bağlanır" makalesine bakın.
İşte, düşünelim farklı excel çalışma kitaplarında iki tablo nasıl bağlanır, tablolar arasında ilişkiler kurarak. Her şey ayarlandığında, verilerin başka bir tablodan bu tabloya aktarılması için bir tablonun açılması yeterlidir. Bağlantının çalışması için tüm kitapları açmanıza gerek yoktur. Bir açık çalışma kitabı ile çalışacaktır.
Yani, iki Excel çalışma kitabımız var - Kitap1 ve Kitap2. Kitap 1'de zaten doldurulmuş böyle bir tablo var. Tablo 1.Dikkat!
Tablo birleştirilmiş hücrelere sahipse, bağlantı içlerinde çalışmaz veya yoldan çıkar.
2. Kitapta da aynı tabloyu yapıyoruz ama içini doldurmuyoruz.

Başka bir işlev "Bağlantı ekle" bu şekilde çağrılabilir. Ayrıca kopyalıyoruz, ancak içerik menüsünde " işlevine tıklıyoruz. özel uç". Görünen iletişim kutusunda "Bağlantı Ekle" düğmesini tıklayın.

Bir çalışma sayfası başka bir sayfadaki verileri kullanıyorsa, bu sayfalar bağlantılı kabul edilir. Bağlama ile, birkaç farklı tablodaki hücre değerlerini aynı çalışma sayfasında bir araya getirebilirsiniz.

Aynı sayfa veya tablodaki bir hücrenin içeriğini değiştirme ( kaynak), sayfalardaki veya tablolardaki ilişkili hücrelerini değiştirir ( alıcılar). Bu ilke, bağlantı sayfalarını, hücrelerin içeriğini bir sayfadan diğerine basitçe kopyalamaktan ayırır.

Yürütme tekniğine bağlı olarak, bağlama "dümdüz" ve komut aracılığıyla ÖZEL UÇ.

Yöntem 1 - "Doğrudan hücre bağlama"

Doğrudan bağlantı Sayfalar, formülün öğelerinden biri olarak başka bir sayfanın hücresine başvuru kullanıldığında, bir hücreye formül girerken doğrudan kullanılır. Örneğin, çalışma sayfası2'deki tablo hücresi B4, başka bir çalışma sayfasının (örneğin, Sayfa1) A4 hücresine bağlantı kullanan bir formül içeriyorsa ve her iki sayfa da verilerle yüklüyse, bu durumda sayfaların bu şekilde bağlanmasına "doğrudan" denir.

Terim "doğrudan" bağlama anlamına gelir kullanıcının kendisi formülü girerken doğrudan gösterirsayfa adı Ve mutlak hücre adresiünlem işareti "!" ile ayrılmış.

Formül örnekleri: = C5*Sayfa1! A4

Sayfa3! B2*%100

Sayfa1! A1- Sayfa2! A1

Not.

Yüklenmemiş (açılmamış) çalışma kitaplarında bulunan hücrelere ve sayfalara bir bağlantı belirtmek için, formülde boşluk bırakmadan belirtmeniz gerekir. tam yol dosya konumu. Yol, köşeli parantez içinde diskin adı, dizin (klasör), çalışma kitabı adı (dosya adı) ve bağlantının gittiği sayfanın adının belirtildiği tek tırnak içinde belirtilir.

Yöntem 2 - Hücreleri "Özel Yapıştır" komutuyla bağlama

Komut yoluyla bağlantı ÖZEL UÇ bir çalışma sayfasındaki herhangi bir tablo hücresinin başka bir çalışma sayfasındaki bir hücrenin değerini içermesi gerekiyorsa gerçekleştirilir.

Sayfadaki C4 hücresine yansıtmak için Fiyat orijinal sayfadaki H4 hücresinin değeri Satın almak, imleci kaynak sayfanın H4 hücresine yerleştirmeniz ve komutu çalıştırmanız gerekir. Düzenle–Kopyala. sayfada Fiyat imleci orijinal hücreye bağlanması gereken C4 hücresine getirin ve komutu yürütün Düzenle - Özel Yapıştır - Bağlantıyı Yapıştır(bkz. şekil 8). Daha sonra sayfada Fiyat orijinal sayfanın hücresinin bir göstergesi görünecektir Satın almak, Örneğin: = Satın Al!$N$4

Bu bağlama ile EXCEL otomatik olarak hücre başına mutlak adresi kullanır, çünkü göreli bir referans adresi, özellikle yüklenmemiş dosyalara (çalışma kitaplarına) erişirken hatalara yol açabilir.

Egzersiz yapmak. Tablodaki C4, C5, C6, C7, C8 hücrelerini bağlayın Satın alma maliyetleri bir sayfada Fiyat sayfadaki karşılık gelen hücrelerle Satın almak, hücreleri bağlamak için çeşitli yöntemler kullanarak (Şekil 8).

Pirinç. 8 Farklı çalışma sayfalarının hücrelerini bağlama

! Hücreleri bağlarken, hangi hücrelerin kaynak hücreler olduğunu belirleyin.

! Bir bağlantılı tablo için, farklı çalışma sayfalarındaki veya geçerli sayfadaki farklı tablolardan alınan hücreler kaynak hücreler olabilir.

Bağımsız çalışma için görevler.

Bağımsız bir görevi gerçekleştirmeden önce, sayfa tabloları örneğini göz önünde bulundurun Fiyat hücreleri birbirine bağlar.

Örneğin:

bir sayfada Fiyat masada Satın alma maliyetleri A4:A8 hücreleri tablo hücrelerine bağlıdır Satın alınan ürünlerin miktarı bir sayfada Satın almak;

B4:B8 hücreleri başlangıçtır, çünkü. satın alınan malların fiyatları hakkında ilk bilgileri içerir;

C4:C8 hücreleri, çalışma sayfasındaki H4:H8 hücrelerine bağlıdır Satın almak;

D4:D8 hücreleri, satın alınan mallara harcanan tutarı hesaplamak için formüller içerir ve kendi tablolarının hücrelerine atıfta bulunur (örneğin, D4 hücresindeki formül, = B4 * C4'e benzer, bu, malların fiyatını çarpmak anlamına gelir. miktar);

D9 hücresi, D4:D8 hücrelerinin toplamıdır;

ikinci tabloda Fiyat hesaplama aynı sayfada, A14:A18 hücreleri madde 1'e benzer şekilde bağlanır;

B14:B18 hücreleri, mevcut sayfa B4:B8'in orijinal hücrelerine bağlıdır;

C4:C8 hücreleri başlangıçtır, çünkü satın alınan mallar için salonun işaretlemesi hakkında ilk bilgileri içerir;

D14:D18 hücreleri, bir ürünün satış fiyatını hesaplamak için formüller içerir ve kendi tablolarının hücrelerine atıfta bulunur (örneğin, D14 hücresindeki formül = B14 * C14 + B14'e benzer, bu, satın alma fiyatını set ile çarpmak anlamına gelir. satın alma fiyatına eklenmesi gereken kâr marjı miktarını veren kâr payı yüzdesi);

Bu tablolarla tüm işlemleri yaptıktan sonra "çalışabilirliğini" kontrol edin.

Değiştirmekürünün adı - Divan bir sayfada A4 hücresinde Satın almak diğerine - örneğin Divan.

Değiştirmek satın alınan mal miktarı Divan Haziran'da (çalışma sayfasındaki G4 hücresinde Satın almak 11 sayısını girin).

Değiştirmek sayfadaki B4 hücresindeki Kanepe'nin satın alma fiyatı Fiyat diğeri için - 2500.00 ruble.

Değiştirmek sayfadaki C14 hücresindeki Kanepe marjının yüzdesi Fiyat%50'den %32'ye.

Kontrol etmek , ilgili tablolarda değişiklik oldu mu olmadı mı?

Not, çeşitli tabloların hangi hücrelerinin değişikliklerden etkilendiği.

Dikkat! Hücreleri PASTE SPECIAL yoluyla bağlarken, bitişik hücrelere kopyalama, hücrelerin mutlak adreslenmesi nedeniyle sorunlu hale gelir.

1. Egzersiz.Çalışma kitabı tablolarının geri kalanının hücrelerini çeşitli yöntemler kullanarak bağlayın.

Talimat. Gelir ve 1 çeyrek gelir hesaplama tablolarında, sadece 1 çeyrek kaynak hücrelerini toplama formülü kullanılır.

Görev 2. Sayfalarda oluştur Hasılat Ve Gelir 2. çeyrek için elektronik tablolar. Bu tabloları karşılık gelen kaynak verilere bağlayın.

Talimat.

Görev 3. Bir yaprağa pasta grafiği çizme Gelir ve ürün türüne göre gelir dağılımını analiz eder.

Görev 4.Çalışma kitabının sonuna bir çalışma sayfası ekleyin Konsolide beyan.Ürün adlarına göre 1. çeyrek ve 2. çeyrek için alış ve satış sayısını, marjı, alış ve satış fiyatlarını, satış gelirini yansıtan bir pivot tablo oluşturun. Bu tabloyu diğer çalışma sayfalarındaki karşılık gelen kaynak verilere bağlayın.

Talimat. 2. çeyrek için gelir ve gelir hesaplama tablolarında, yalnızca 2 çeyrek için kaynak hücreleri kullanın.

Elektronik tablodaki bilgiler Microsoft Excelçizimden çıkarılan verilerle birleştirilebilir.

Bir e-posta bağlantısı oluşturarak elektronik tablo sayfanın tamamından veya yalnızca bir kısmından bilgi ekleyebilirsiniz, örn. adlandırılmış aralık. Dış Verilere Bağla iletişim kutusunda aşağıdakileri yapabilirsiniz:

• Çizimden alınan verileri bağlamak istediğiniz XLS dosyasını seçerek veri bağlantısını ayarlayın.
• Ayıklanan çizim verilerini elektronik tablo verileriyle eşleyin.
• Çıkarılan çizim verileriyle birleştirmek istediğiniz XLS dosyasındaki sütunları seçin.

Verileri Düzenle sayfası şunlara erişim sağlar: iletişim kutusu Alınan verileri tüm Excel elektronik tablosuna veya bir dizi satır ve sütuna bağlayabileceğiniz Dış Veri Bağlantısı.

Bir e-tabloya bağlantı oluşturmak için, veri almayı gerçekleştiren bilgisayarın şu özelliklere sahip olması gerekir: Microsoft uygulaması Excel. kurulu değilse Microsoft programı excel veya XLS dosyası verilerin alındığı makinede yerel olarak depolanmaz, veri bağlantısı kurulamaz.

Satır veya sütun eklemek gibi bağlantılı elektronik tabloyu değiştirdikten sonra, REFRESH komutunu kullanarak çizimdeki tabloyu değişikliği yansıtacak şekilde güncelleyebilirsiniz. Benzer şekilde, bir çizimdeki bir tabloyu değiştirirseniz, ilgili elektronik tablo aynı komut kullanılarak güncellenebilir.

Not: Bir veri bağlantısını kaldırmak için REFER komutunu kullanın. Palet veri bağlantıları Dış bağlantılar" silinemez.

Veri Bağlama ve Eşleme Hakkında

Veri bağlama, elektronik tablonun tamamını veya bir kısmını çıkarılan çizim verileriyle birleştirmek için bir Microsoft Excel elektronik tablosunun seçilmesinden oluşur. Veri bağlama hakkında daha fazla bilgi için bkz. "Dış verilerle tablo ilişkilendirmesi oluşturma".

Veri Eşleme işlemi, iki veri kümesi arasında bir bağlantı oluşturur: çıkarılan çizim verileri tablosu sütunundaki veriler ve Excel elektronik tablo sütunundaki veriler.

Aşağıdaki örnekte basit bir ürün ağacı ele alınmaktadır. Çizimden üç veri sütunu çıkarılır. Bu sütunlar aşağıdaki verileri içerir: parça numarası, başyazı Ve Miktar. Elektronik tablodan iki ek veri sütunu, belirtime dahil edilebilir. Bu sütunlar aşağıdaki verileri içerir Fiyat Ve Sağlayıcı.

İÇİNDE bu örnek elektronik tablo, başlığı olan bir parça numarası sütunu içerir Katalog numarası ve sütunla eşleşir detay numarasıçıkarılan veri tablosunda. Dış Verilere Bağla iletişim kutusunda, "Detay numarası"çizim verileri için bir sütun olarak ve "Katalog numarası" harici veriler için bir sütun olarak. Hem çizim veri sütununda hem de harici veri sütununda karşılık gelen en az bir veri hücresi olduğundan, veri çıkarma işlevi iki kaynağın nasıl ilişkili olduğunu "bilir".

Veri tutarlılığı, Dış Veriye Bağla iletişim kutusundaki Uyumluluğu Kontrol Et düğmesine tıklanarak kontrol edilebilir. Bir uyumsuzluk varsa, nedenini belirten bir uyarı mesajı görüntülenir. Bazı durumlarda uygun yaratımçizim verileri ile elektronik tablo verileri arasında bağlantı yoktur.

Eşlenmiş verileri kontrol etme

Veri tutarlılığı denetimi hatası, çizim verileri ile harici veriler (elektronik tabloda) arasında geçerli bir eşleşme bulunamadığı anlamına gelir. Aşağıdaki hata mesajları görüntülenir.

Çizim verilerindeki değerler ile dış veriler arasında herhangi bir benzerlik bulunamadı.

• Sorun devam ederse, dış veriler birleştirilmez. Elektronik tablo sütun verilerini çizim verileriyle karşılaştırın ve eşleşen değerleri bulmaya çalışın. Örneğin, çizim verileri ve harici veriler şu şekilde ayarlanabilir: Farklı anlamlar kesinlik.

Dış verilerdeki veri sütunu benzersiz değerler içermez.

• Harici bir veri sütunu, aynı verilere sahip bir veya daha fazla hücre içerir. Bu durumda, çizim verilerini harici verilere benzersiz bir şekilde eşlemek mümkün değildir.

Her şeyden önce, tabloları ortak özelliklere göre bağlamak, MS Excel için tipik bir işlev değildir. Bu tür görevler için, özel programlar sözde veritabanları. Onlara "veritabanı yönetim sistemleri" veya DBMS demek daha doğru olsa da. Bunlar arasında örneğin MS Access, MS SQL Server, Oracle ve diğerleri. Bu sistemlerde tabloları ortak anahtar alanlara göre birleştirmek belki de en yaygın işlemdir. Ve çözümü için güçlü ve kullanışlı araçlar. Tablolar arasında çok çeşitli ilişki türlerini düzenlemenize, veri bütünlüğünü kontrol etmenize, kayıtların kademeli olarak silinmesini gerçekleştirmenize vb. olanak tanırlar. Ancak, DBMS'yi tam olarak kullanmak için biraz eğitime, sorgulama dili bilgisine vb. ihtiyacınız vardır. Bu nedenle, bir muhasebecinin uygulamasında, saf haliyle bir VTYS nadiren kullanılır ve tüm veri işleme genellikle excel programı. MS Excel'e gelince, burada tabloları bağlama olanakları sınırlıdır. Temel olarak, bu bağlantı, farklı sayfalara veya çalışma kitaplarına başvuran formüller yazmak içindir. Birden çok veritabanı üzerinde çalışan basılı bir rapor veya pivot tablo oluşturabilirsiniz. Ancak Excel'deki veri kümeleri arasındaki ilişkilerin tam teşekküllü organizasyonu için özel araçlar yoktur ve olmamalıdır.

Ancak, her şey o kadar da kötü değil ... Gerçek şu ki, gerçek işte Tüm tabloları bir muhasebeciye bağlamak için seçenekler genellikle gerekli değildir. Evet ve kayıtların kademeli olarak silinmesi olmadan da bir şekilde idare edecektir. Ancak büyük bir tabloyu bir referans kitabına bağlamak oldukça alakalı ve pratik olarak uygulanabilir bir görevdir. Ve en önemlisi (!), Böyle bir işlev oldukça gerçekleştirilebilirMS Excel. Ve bunun için gerekli olan tek şey, birkaç basit numara ve bir köprü sistemidir.

Örnek olarak, şekli Şekil 1'de gösterilen iki tablo kullanacağım. 1 ve 2. İlk tablo (sayfada bulunur " Emirler ”) dört alandan oluşur: “ Sipariş”, “Tarih”, “Müşteri”, “Miktar” ” ve boş alan “ iken Yorum. ". Bu, sipariş veri tabanının basitleştirilmiş bir versiyonudur. alanında" Yorum. » sonunda dizin öğesine bir bağlantı koyacağız. Açıktır ki, veritabanında Emirler » Bir karşı taraf birçok kez görünebilir - bu müşteri tarafından verilen siparişlerin sayısına bağlıdır.

İkinci tablonun adı " Müşteriler ". İçinde sadece beş sütun bıraktım: "İsim ” (işletmenin adı), ardından müdürün adı, yasal adresi, telefon numarası ve e-posta.

Önemli!"Müşteriler" dizininde » bir satır, bir kuruluşla ilgili verileri içerir. Tekrarlara burada izin verilmez, her müşteri veritabanında yalnızca bir kez bulunur.

Yani, tablo arasında bir ilişki düzenleyeceğiz " Siparişler" ve "Müşteriler ". Bunun için sahada Yorum. » tablolar « Siparişler » belirli bir tablo satırına işaret edecek bir köprü koyacağız « Müşteriler ". Köprünün algoritması aşağıdaki gibi olacaktır. İlk olarak, alanın içeriğini ayrıştırmalıdır " NaimPredpr " V akım çizgisi tablolar " Emirler ". Başka bir deyişle, hatırla Firma Adı Kim belirli bir sipariş verdi. Bağlantı daha sonra sayfaya atlamalıdır " Müşteriler ". Bu sayfada, siparişi veren şirketin parametrelerini açıklayan bir satır bulun. Ve son olarak - işaretçiyi ayarlayın geçerli hücre dizindeki ilgili satıra " Müşteriler ". Genel hatlarıyla bu şekilde görünüyor.

Bu arada, veritabanı terminolojisinde böyle bir ilişki " olarak adlandırılır. birden çoğa " (masanın yanından bakıldığında" Müşteriler "). Yani, müşteri dizininin bir öğesi, sipariş veritabanındaki birkaç kayda atıfta bulunur. MS Excel'de böyle bir bağlantı oluşturmak için özel bir araç yoktur. Onları kendimiz inşa etmeliyiz. Algoritmaya dayalı olarak bunu yapmak için şunlara ihtiyacımız var: bir köprü oluşturmak için yerleşik bir işlev (böyle bir işlev " olarak adlandırılır) KÖPRÜ() ”) ve bir tabloda veri aramak için bir işlev. işlevini kullanmanızı öneririm " KİBRİT() ". Ama hepsi bu kadar değil. Köprü olmalıdır tam adres geçiş için: dosya adı, çalışma sayfası adı ve etkinleştirmek istediğiniz hücre. Dosyanın, sayfanın ve hücrenin adını almak için " işlevini kullanacağız. HÜCRE() ". Ek olarak, atlama adresiyle bir satır oluşturmak için metinle çalışmak için yerleşik işlevlere dönmemiz gerekecek: " PSTR() "ve" SEARCH() ". Aslında tüm ana araçlar bu. Pratik uygulamaya geçebilirsiniz.

Önce birkaçını yapalım ön işlem. " işleviyle başlayalım. KÖPRÜ() ", olacak anahtar tablolar arasındaki ilişkileri düzenlerken. İki seçeneği var. Birincisi adres, yani bağlantının işaret ettiği dosyanın ve çalışma sayfasının adı. İkinci parametre, bu sayfada köprünün işaret edeceği belirli bir hücrenin adresidir. Birçok köprümüz olacak. Bunları her oluşturduğunuzda, işlev parametrelerinde dosya adını ve sayfa adını belirtmeniz gerekecektir. Uzun ve uygunsuz. Bu nedenle, sayfa ve dosya adıyla bir kez bir değişken oluşturmak ve ardından değişkenini " işlevinde belirtmek daha iyidir. KÖPRÜ() " ihyaç olduğu gibi. MS Excel ile ilgili olarak, böyle bir değişken olarak kullanmak uygundur. adlandırılmış hücre aralığı. Burası başlayacağımız yer.

1. Excel'i çağırıyoruz, belgeyi yüklüyoruz ve menüye gidiyoruz " formüller ". Bant, Şekil l'deki gibi görünecektir. 3.

2. Grupta " Belirli isimler» simgesine tıklayın « Ad Yöneticisi ". Şekil l'de gösterilen pencere 4.

3. Bu pencerede " düğmesine basın. Yaratmak ". Pencere " isim yapmak ”, Şek. 5.

4. Bu pencerede " alanında Ad:" "Listem" metnini girin. Aralık alanında: » formülü yazdır «=ORTA(HÜCRE("dosya adı" ;Müşteriler!$A$1);ARA(" [ " ;HÜCRE(" dosyaadı " ;Müşteriler!$A$1));256)& " ! " » (Şek. 3).

5. "Bir ad oluştur" penceresinde "Tamam"ı tıklayın.

6. Ad Yöneticisi penceresinde Tamam'a tıklayın.

Şimdi gerçekte ne yaptığımıza bakalım mı? Çalışma kitabının yeni bir adlandırılmış aralığı var " Listem".

Önemli! Bizim durumumuzda, adlandırılmış aralığın adı boşluk OLMADAN olmalıdır. Aksi takdirde, adresleme formülleri doğru çalışmayacaktır.

Herhangi bir adlandırılmış aralık gibi, bir Excel çalışma kitabındaki bir hücreye veya hücre grubuna işaret eder. Ancak bizim durumumuzda bu grubun adresi kalıcı değil, dinamik. " alanına girdiğimiz formülle oluşturulur. Menzil: ". Kısaca bu formülün çalışması hakkında. İçeriden başlayalım - işlevle " HÜCRE()".

"HÜCRE(" dosyaadı " ;Müşteriler! $A$1) ifadesi "hücreyi ifade eder""Müşteriler" sayfasında A1" ” ve bu hücre için döndürür açık adres , yani dosyanın yolu, dosyanın adı ve bu hücrenin bulunduğu sayfanın adı. Örneğin, tablolar içeren belgemin adı " DeanReferencesExcel_.xls ". Diskte saklanır D:" klasöründe " !Factor ". O zaman formülün sonucu şöyle olacaktır:D:!Factor[DeanReferencesExcel_.xls]Müşteriler».

Bu hattan, sadece almamız gerekiyor dosya adları Ve çarşaf A çalışma kitabı - köprü oluştururken sürücü harfine ve klasör adına ihtiyaç duyulmaz. Bu sorunu çözmek için standart fonksiyonları kullanıyoruz.Metinle çalışmak için Excel.

" işleviyle bir dizenin bir bölümünü kesebilirsiniz. ORTA() ". Ama şunu belirtmesi gerekiyor: orjinal metin(hücreye giden tam yola sahibiz), başlangıç ​​pozisyonu Ve işaret sayısı Orijinal diziden çıkarılacak olan.

Başlangıç ​​pozisyonunu belirlemek kolaydır. Bunu yapmak için, işlevi kullanarak ARAMAK() ” köşeli parantezinin ilk geçtiği yeri buluyoruz (“[ ”) hücreye giden yolun saklandığı metne. Bu işlemi gerçekleştiren formül parçası şöyle görünür: SEARCH(" [ " ;HÜCRE(" dosyaadı " ; Müşteriler!$A$1)) ”. " satırı için D:!Factor [DeanReferencesExcel_.xls]Müşteriler» bu formül geri dönecek « 12 ". Kaynak metinden kesilmesi gereken karakter sayısını maksimum seçtim - " 256".

Elde edilen değerleri " işlevine koymak kalır. ORTA() ". İlk parametresi, hücrenin adresini içeren metindir. İkinci parametre, ipin bir kısmını kesmek için başlangıç ​​pozisyonunun numarasıdır. Son parametre, satır başına izin verilen maksimum karakter sayısıdır. Bu işlemin formülü şöyle olacaktır:=ORTA(HÜCRE("dosya adı" ;Müşteriler!$A$1);ARA

(" [ " ;HÜCRE(" dosyaadı " ;Müşteriler!$A$1));256) ". Örneğimizde, böyle bir formül, sonucu " biçiminde döndürür.[DeanReferenceExcel_.xls]Müşteriler". Aslında, bu sayfaya bir bağlantı " Müşteriler " çalışma kitabı " DeanReferencesExcel_.xls ". Sonraki hücrenin adresi olabilir. Ancak şimdiye kadar bir öğe eksik - sayfa adı ile hücre adresi arasında olmalı Ünlem işareti (« ! "). " işlemini kullanarak bu sembolü formüle ekleyebiliriz.& " ve son sürümde ifade şöyle görünecek:"=ORTA(HÜCRE("dosya adı" ;Müşteriler!$A$1);ARA(" [ " ;HÜCRE(" dosyaadı " ; Müşteriler!$A$1)); 256)&" ! " ". Ve çalışmasının sonucu şöyle olacak:».

Tavsiye Formül parçalarını dikkatli bir şekilde çalışma sayfasının ayrı hücrelerine kopyalayın ve ardından her bir parçasının çalışması parmaklarınızın ucunda olacaktır.

Bu arada, adlandırılmış bir aralık içindeki bir formülün işleyişini kontrol etmek kolaydır. " ifadesini girin=Sayfam ” ve “ düğmesine basın. Girmek ". Sonuç hücrede görünmelidir:[DeanLinkExcel_.xls]Müşteriler!».

Önemli! Lütfen şunu unutmayın: doğru operasyon sayfa adı formülleri boşluk içermemelidir.

1. Sayfaya gidin " Emirler ", hücreye geç" E2".

2. Formülü girin "=HYPERLINK(My_Sheet&ADDRESS(SEARCH(C2;Müşteriler!$A:$A;0); 1);" > " ) ».

3. Bu formülü tablonun tüm yüksekliğine kopyalayın. Çalışmamızın sonucu Şekil 1 de görülmektedir. 6.

Neye sahip olduğumuzu kontrol edelim. Masada " Emirler »Örneğin hücreye sol tıklayın « E6 ". Bu satır, " numaralı siparişle ilgili bilgileri içerir. 5 "şirketten" PE "Kolo"". Tıkladıktan sonra Excel, " Müşteriler " ve aktif hücre işaretçisi şu adreste olacaktır: " A6 ". Dizinin bu konumunda " hakkında bilgiler bulunur. PE "Kolo"" (Şek. 7).

Formülün algoritmasını kısaca ele alalım. İfade "=MATCH(C2;Müşteriler! $A:$A;0)» sütunda bir hücre bulur «"Müşteriler" sayfasında A" ”, hücreden karşı tarafın adını içeren” C2" sayfası "Siparişler ". " işlevinin son parametresi MATCH() "eşittir" 0 ". Bu, ilkeye göre bir değer arayacağı anlamına gelir. kesin maçlar. Örneğimize geri dönelim. " dizesiyle çalıştığımızı varsayalım. 6 » tablolar « Siparişler » (Şek. 6). Bu satır, sipariş numarasını içerir " 5 "firmadan" PE "Kolo" ". Köprüyü hücreden kopyaladıktan sonra " E2 » sütunun aşağısında «"E6" hücresindeki E" "Arama işlevine sahip formül şu şekilde olacaktır:"MATCH(C6;Müşteriler!$A:$A;0)". Ve bu ifadenin sonucu şuna eşit olacaktır: " 6 ". Bunun anlamı, tabloda Müşteriler " Şirket tanımı " PE "Kolo" " konumlanmış altıncıçalışma sayfası satırı.

Devam etmek. Bu ifadenin sonucunu fonksiyon adresinin ilk parametresi olarak değiştireceğiz. İkinci parametre " 1 ". Daha sonra ifade" ADRES(ARA (C2;Müşteriler!$A:$A;0);1)" (tablonun ikinci satırı " Emirler "") aslında "anlamına gelir ADRES(2;1) ". Bu işlev, çalışma sayfasının ikinci satırında ve ilk sütununda bulunan hücrenin adresini metin olarak döndürür. Sipariş için " 1 ' (ikinci satır) bu, ' adresi olacaktır.$A$2 ". Tablonun altıncı sırasındaki bir sipariş için (şirket " PE "Kolo" ") ifade, " dizesini döndürür$A$6" vb.

Bir hücrenin adreslenmesi ile açıktır. Şimdi sonucu işlevde kullanmanız gerekiyor " KÖPRÜ() ".

İşte durum. Fonksiyonun " olduğunu hatırlatmama izin verin. KÖPRÜ() » iki parametre. İlki, köprünün işaret ettiği adrestir. Atlanacak hücrenin dosya, sayfa ve adresinin adını içerir. Dosya ve sayfa adlarına zaten sahibiz, bu bilgiler " değişkeninde saklanır. Listem ". Belirli bir sipariş için karşı tarafa bağlanmak üzere hücrenin adresini aldık. Bu iki parçayı operasyonla birleştirmeye devam ediyor "& ". Ve atlama adresi için son ifade şöyle olacaktır:My_List&ADDRESS(SEARCH(C2; Müşteriler!$A:$A;0);1)».

Bakalım elimizde ne var. hücre için" C2 » bu formül sonucu döndürür «[DeanReferenceExcel_.xls]Müşteriler!$A$2" - yani hücreye bir referans " A2 » dizinde « Müşteriler ". Bu doğru: sipariş numarası için " 1 » bağlantı karşı tarafı işaret ediyor « TOV " Vurmak " ". Hücreye başvurursanız C6 " (sipariş numarası ""PE "Kolo"dan 5" ”), ardından tablonun bu satırında “ Emirler "Atlama adresinin ifadesi şöyle görünecektir:"My_Sheet&ADDRESS(SEARCH(C6;Müşteriler!$A:$A;0);1)". Ve formülün sonucu şöyle olacaktır:[DeanReferenceExcel_.xls]Müşteriler!6$A$».

" fonksiyonunun ikinci parametresine geçiyoruz. KÖPRÜ() ". şu metin olmalıExcel köprüyü yerinde gösterecektir. Başka bir deyişle, bu, köprünün kendisinin adıdır. sembolünü seçtim">" ("daha fazla" imzalayın "). Ve sonra bir köprü oluşturmak için son formül şöyle olacaktır:=HYPERLINK(My_List&ADDRESS(SEARCH(C2; Müşteriler!$A:$A; 0);1);" > " ) ».

Tablolar arasında ilişkiler oluşturmakla ilgili her şey bu kadar.Excel. Görevle başa çıktık, tüm bağlantılar doğru çalışıyor. Tablonun tasarımına birkaç söz eklemek istediğim tek şey. Şimdi sütunda