Microsoft Excel'de veri birleştirme. Birden çok sayfadaki verileri birleştirme

Farklı tablolara, sayfalara ve hatta kitaplara yerleştirilmiş aynı türden verilerle çalışırken, algılama kolaylığı için bilgileri bir arada toplamak daha iyidir. Microsoft Excel'de bu görev, adı verilen özel bir araç kullanılarak halledilebilir. "Konsolidasyon"... Birbirinden farklı verileri tek bir tabloda toplama yeteneği sağlar. Bunun nasıl yapıldığını öğrenelim.

Doğal olarak, tüm tablolar bir araya getirilemez, yalnızca belirli koşulları karşılayanlar:

• tüm tablolardaki sütunlar aynı ada sahip olmalıdır (yalnızca sütunların permütasyonuna izin verilir);
• boş değerlere sahip sütun veya satır olmamalıdır;
• tablolar için şablonlar aynı olmalıdır.

Konsolide Tablo Oluşturma

Örnek olarak aynı şablon ve veri yapısına sahip üç tablo kullanarak birleştirilmiş bir tablonun nasıl oluşturulacağına bakalım. Her biri ayrı bir sayfada bulunur, ancak aynı algoritmayı kullanmanıza rağmen, farklı kitaplarda (dosyalarda) bulunan verilerden birleştirilmiş bir tablo oluşturabilirsiniz.

1. Birleştirilmiş tablo için ayrı bir sayfa açıyoruz.



2. Açılan sayfada, yeni tablonun sol üst hücresi olacak hücreyi işaretleyin.
3. Sekmedeyken "Veri" düğmeye tıklayın "Konsolidasyon" araç kutusundaki bantta bulunan "Verilerle çalışma".



4. Veri konsolidasyonu kurulum penceresi açılır.

   

   alanında "İşlev" satırlar ve sütunlar eşleştiğinde hücrelerle hangi işlemin gerçekleştirileceğini ayarlamak gerekir. Bunlar aşağıdaki eylemler olabilir:

   • toplam;
   • sayı;
   • ortalama;
   • maksimum;
   • minimum;
   • İş;
   • sayı miktarı;
   • ofset sapma;
   • tarafsız sapma;
   • kaydırılmış varyans;
   • tarafsız varyans.

   Çoğu durumda, işlev kullanılır "toplam".



5. alanında "Bağlantı" konsolidasyona tabi olan birincil tablolardan birinin hücre aralığını belirtiriz. Bu aralık aynı dosyada ancak farklı bir sayfadaysa, veri giriş alanının sağındaki düğmeye basın.



6. Tablonun bulunduğu sayfaya gidin, istediğiniz aralığı seçin. Verileri girdikten sonra hücrelerin adresinin girildiği alanın sağ tarafında bulunan butona tekrar tıklayın.



7. Konsolidasyon ayarları penceresine dönersek, daha önce seçmiş olduğumuz hücreleri aralık listesine eklemek için butonuna tıklayınız. "Ekle".

   

   Gördüğünüz gibi, bundan sonra aralık listeye eklenir.

   

   Benzer şekilde, veri konsolidasyon sürecine katılacak diğer tüm aralıkları ekliyoruz.

   

   Gerekli aralık başka bir kitapta (dosyada) bulunuyorsa, hemen düğmesine tıklayın. "Genel Bakış...", sabit diskteki veya çıkarılabilir medyadaki bir dosyayı seçin ve ancak o zaman yukarıdaki yöntemi kullanarak bu dosyadaki bir hücre aralığını seçin. Doğal olarak, dosya açık olmalıdır.



8. Aynı şekilde konsolide tablo için başka ayarlar da yapabilirsiniz.

   Sütun adlarını başlığa otomatik olarak eklemek için parametrenin yanına bir onay işareti koyun. "Üst satır başlıkları"... Verilerin özetlenmesi için parametrenin yanına bir onay işareti koyun. Sol Sütun Değerleri... Birincil tablolardaki verileri güncellerken birleştirilmiş tablodaki tüm bilgilerin güncellenmesini istiyorsanız, parametrenin yanındaki kutuyu işaretlediğinizden emin olun. "Ham verilere bağlantılar oluşturun"... Ancak bu durumda, orijinal tabloya yeni satırlar eklemek istiyorsanız, bu öğenin işaretini kaldırmanız ve değerleri manuel olarak yeniden hesaplamanız gerekeceğini dikkate almanız gerekir.

   Tüm ayarlar yapıldıktan sonra butonuna tıklayın. "TAMAM".



9. Birleştirilmiş rapor hazır. Gördüğünüz gibi, verileri gruplandırılmıştır. Her grup içindeki bilgileri görüntülemek için tablonun solundaki artı işaretine tıklayın.

   

   Grubun içeriği artık görüntülenebilir. Diğer herhangi bir grup da benzer şekilde ifşa edilebilir.

Gördüğünüz gibi, Excel'de veri konsolidasyonu çok kullanışlı araç, bu sayede sadece farklı tablolarda ve farklı yerlerde bulunan bilgileri bir araya toplayabilirsiniz. farklı çarşaflar, ancak diğer dosyalara (kitaplara) bile yerleştirildi. Bu nispeten basit ve hızlı bir şekilde yapılır.

Nedir: Veri konsolidasyonu temel olarak farklı sayfalarda bulunan bilgilerin tek bir çalışma sayfasında gruplandırılacak şekilde birleştirilmesidir. Bu nedenle, konsolidasyon aracı, birden fazla elektronik tablo ile çalıştığınızda ve bunlardan tek bir hesap tablosunda bilgi almak istediğinizde birkaç durumda pratiklik ile karşımıza çıkıyor.

Kullanım örneği:

Bu aracı ve uygulamasını daha iyi anlamak için bilgisayar bileşenleri (bilgisayar parçaları) satan bir şirket örneğini kullanacağız. Bu şirketin rastgele satış yaptığı üç birimi vardır ve her biri satıcı adı, satılan ürün ve satış tarihi hakkında bilgiler içeren bir satış tablosunu besler. Aşağıdaki resimde gösterildiği gibi:

Bu çalışma sayfaları şirketin sunucusunda belirli bir satış yönetimi klasörüne kaydedilir ve bir noktada tüm bilgileri tek bir sayfada toplamak için bu üç sayfadaki bilgileri gruplamak gerekir.

Böylece konsolidasyon aracı bilgisi olmadan kullanıcı, şirketin üç birimi hakkında bilgi alacak olan çalışma sayfasını günlük olarak güncellemek zorunda kalacak ve böylece gereksiz işler yaratılmış olacaktır. Öte yandan, bu bilgileri bir çalışma sayfasında birleştirmek, diğer sayfalarda değişiklik olduğunda otomatik olarak güncelleyecektir.

Tablo verilerini birleştirme:

Konsolidasyon aracının kullanımını anladıktan sonra, e-tabloları nasıl birleştireceğinizi adım adım göstermek için yukarıdaki örneği kullanacağız. Aşağıya bakınız:

1 - Birleştirilecek çalışma sayfalarını belirleyin. Verilen örnekte, çalışma sayfaları aynı klasördedir, ancak içinde olabilirler. farklı yerler ah sorunsuz.

2 - Yeni bir tablo açın ve Diğer Kaynaklardan seçeneğini seçip tekrar Microsoft Sorgusu seçeneğini seçerek Veri sekmesine gidin.

3 - "Microsoft Sorgusu" seçeneğini seçtikten sonra, "Excel Dosyaları" seçeneğini seçmeniz ve "Tamam" ı tıklamanız gereken yeni bir pencere açılacaktır.

4 - Birleştirilecek dosyaların yolunu belirtmeniz gereken yeni bir pencere açılacaktır. Bu örnekte, şirketin üç bölümünün elektronik tablolarına işaret edeceğiz.

İlk çalışma sayfasını seçin ve Tamam'a tıklayın. Bir sonraki adımda, Sorgudaki Sütunlar alanına istediğiniz tabloları ekleyin ve işiniz bitene kadar devam edin.

6 - Bitir'i tıklattığınızda, ilk çalışma sayfasının zaten birleştirilmiş olduğuna dikkat edin. Birleştirilecek kalan sayfaları eklemek için 2'den 4'e kadar olan adımları tekrarlayın. Prosedürün sonunda, tüm çalışma sayfalarını aşağıda gösterildiği gibi tek bir çalışma sayfasında gruplandıracağız:

Bu şekilde, üç çalışma sayfasından herhangi birinde yapılan her değişiklik, konsolide ettiğimiz elektronik tabloda da değişecektir. Bu durumda Veri sekmesine gidin ve Tümünü Yenile düğmesine tıklayın.

Yukarıdaki örnekte, konsolidasyon aracının, birden çok sayfadaki bilgileri tek bir dosyada gruplamak için kritik olduğu sonucuna varabiliriz. Bu tablolarda depolanan verilerin türünden bağımsız olarak, veri konsolidasyonu, raporlar ve diğer analizler için bilgileri bağlamanın ve bunları elektronik tabloların her birindeki değişiklikleri yansıtacak şekilde güncellemenin pratikliğinden yararlanır.

Bu içerik hakkında herhangi bir şüpheniz mi var? Size yardımcı olabilmemiz için aşağıya bir yorum bırakın!

konsolidasyon bir veya daha fazla veri alanından gelen verilerin birleşimidir ve bunları bir özet sayfasında bir tablo olarak görüntüler.

Örneğin, her bölge ofisi için bir gider tablonuz varsa, bu verileri kurumsal gider tablosuna dönüştürmek için konsolidasyonu kullanabilirsiniz. Bu ana sayfa, kuruluş genelinde toplam ve ortalama satışları, mevcut envanter kalemlerini ve en çok satan ürünleri içerebilir.

Veriler konsolide edildiğinden, düzenli olarak veya ihtiyaç duyulduğunda güncellenmesi ve özetlenmesi daha kolay olacak şekilde düzenlenir.

Verileri birleştirmenin iki ana yolu vardır.

Lokasyona göre konsolidasyon. Bu yöntem, veriler aşağıdaki durumlarda kullanılır: çeşitli kaynaklar aynı şekilde sıralanır ve aynı sütun ve satır etiketlerini kullanır (örneğin, aynı şablondan oluşturulmuş birden fazla gider sayfanız varsa).

Kategoriye göre konsolidasyon. Bu yöntem, farklı kaynaklardan gelen verilerin farklı sıralanması durumunda yararlıdır, ancak bunlar aynı sütun ve satır etiketlerini de kullanırlar (örneğin, her ay için aynı yerleşime sahip ancak farklı öğeler veya farklı bir sayı içeren stok veri sayfalarınız varsa) elemanlar).

Verileri birleştirmek için şu komutu kullanın: konsolidasyon(sekme Veri, grup Verilerle çalışma).

Verileri konuma göre birleştirme konsolide edilecek veriler aynı sırada yerleştirilirse kullanılır, yani. tablolar aynı. Konsolidasyon teknolojisi aşağıdaki gibidir:

Solu temsil eden hücreyi seçin üst köşe birleştirilmiş verileri yerleştirmek istediğiniz alan.

sekmesinde Veri grup içinde Verilerle çalışma takım seç konsolidasyon.

iletişim kutusunda konsolidasyon açılır listeden seçin İşlev verileri birleştirmek için kullanmak istediğiniz özet işlevi.

alanında Bağlantı veri konsolidasyonu (hücre aralığı) için kaynak alanı girin, tıklayın Ekle... Verilerin konsolidasyona katılacağı tüm aralıklar için bu adımları tekrarlayın ve ardından Tamam'a tıklayın.

Kategoriye Göre Veri Konsolidasyonu kaynak alanlardaki veriler sırasızsa ancak aynı başlıklara sahipse kullanılır. Bu konsolidasyonun teknolojisi, değer olarak veri konsolidasyonu teknolojisi ile aynıdır. Ancak, iletişim kutusunda konsolidasyon grup içinde İsim olarak kullan parametreler ayarlanmalıdır üst satır başlıkları ve / veya sol sütun değerleri Kaynak alanlarda başlıkların yerini belirtmek için.

Verilerinizi konsolide ettiyseniz, kaynak alanları değiştirirseniz konsolidasyonu tekrarlamanız gerekir. Orijinal veriler değişirse, birleştirilmiş verileri orijinale bağlayarak tekrar konsolidasyonu önleyebilirsiniz. Başka bir çalışma kitabındaki kaynak veriler değiştiğinde konsolidasyonu otomatik olarak güncellemek için onay kutusunu seçin. Ham verilere bağlantılar oluşturun... Parametrenin ayarlanması, kaynak veriler ile konsolidasyon sonuçları arasında dinamik bir bağlantı kurulduğu anlamına gelir, bu da verilerin otomatik olarak güncellenmesini sağlar.

Bu onay kutusu yalnızca veri sayfası başka bir çalışma kitabındaysa seçilebilir. Bu onay kutusu seçildiğinde, konsolidasyona dahil edilen hücre ve aralık kümesini değiştiremezsiniz.

Veri Konsolidasyonu, birkaç farklı alanda bulunan verilerin seçilen işleme fonksiyonuna göre birleştirildiği bir özetleme yöntemidir. Bu durumda, bir veya daha fazla kaynak alanda bulunan veriler işlenir ve tek bir özet tablosunda görüntülenir.

Veri kaynakları, özet tabloyla aynı sayfada, aynı çalışma kitabındaki diğer sayfalarda veya farklı çalışma kitaplarında olabilir. Konsolidasyon, verileri işlemek için özetlemek için kullandığı işlevlerin aynısını kullanır. Orijinal (birleştirilmiş) verilerin konumuna kaynak alanı denir. Çalışma sayfasının nihai (birleştirilmiş) verilerin yerleştirileceği kısmı hedef alandır.

Verileri birleştirmenin iki yolu vardır: konuma göre ve kategoriye göre.

Lokasyona göre konsolidasyon. Konum konsolidasyonu, kaynak alan verileri aynı sırada ve aynı başlıklara sahip olduğunda kullanılır. Tek bir şablona dayalı kurumsal departman bütçeleri gibi sayfa verilerini birleştirmek için bu yöntemi kullanın. İlk çeyrekte üç farklı mağazaya sağlanan donanım için bir muhasebe örneği düşünün (Şekil 6.54). Karşılaştırma için, çalışma kitabının farklı sayfalarında (Ocak, Şubat, Mart) bulunan kimlik bilgileri şekilde yan yana gösterilmiştir, bu da üç sayfanın her birinde verilerin göreceli konumunun tamamen çakıştığını fark etmeyi mümkün kılar. hem satırlar hem de sütunlar.

Pirinç. 6.54. Muhasebe verisi

Yere göre konsolide etmek için, kullanıcının önceden sütun başlıkları (Marl, Mag2 ve MagZ) ve satır adları (cıvatalar, somunlar, pullar ve vidalar) içeren bir satırın olacağı bir hedef alan oluşturması gerekir. Tabii ki, bu başlıkların ve başlıkların konumu, orijinal tablolardaki konumlarıyla eşleşmelidir. Daha sonra Marl kolonu ile civata sırasının kesiştiği yerde bulunan sol üst hücreyi imleç ile seçip Data/Consolidation... komutunu verin. Ardından, bu komut tarafından çağrılan Konsolidasyon iletişim kutusunda, konsolide edilecek verilerin işlenmesi gereken işlevi belirtin ve Aralık listesi alanına kaynak alanlara referansları girin.

Pirinç. 6.55. Konsolidasyon İletişim Kutusu

Konum bazında konsolidasyon durumunda, MS Excel elektronik tablo işlemcisinin veri dizilerinin öğelerini dizideki bir veya başka bir öğenin konumuna karşılık gelen endekslerine göre işlediğini görmek kolaydır. Açıkçası, işlenen tüm dizilerin boyutu aynı olmalıdır.

Kategoriye göre konsolidasyon. Kategori konsolidasyonu, kaynak alan verileri sıralanmadığında ancak aynı başlıklara sahip olduğunda kullanılır. Bu yöntem, farklı yapılara sahip ancak aynı başlıklara sahip listelerdeki verileri birleştirmek için kullanılır. Bu durumda, MS Excel elektronik tablo işlemcisi, sütun başlıklarını ve satır adlarını kullanarak veri işleme sürecinde onlara adlar atar. Bu konsolidasyon yönteminde seçilen ilk sütun, veri gruplama alanını tanımlar. Bunu bir örnekle açıklayalım. Bir firmaya ait iki depo (1 ve 2) olsun, farklı zaman mallar farklı tedarikçilerden geldi (Şekil 6.56). Bu verileri kaynak alan olarak kullanarak elde etmek zor değildir. Çeşitli seçenekler hedef alandaki veriler.

Pirinç. 6.56. Malların alınması hakkında bilgi

Örnek 10. Her iki depoda tüm tedarikçilerden altı ay içinde alınan tüm malların her bir kaleminin toplam maliyetini belirlememiz gerektiğini varsayalım. Bu sorunu çözmek için verileri Ürün adı sütununa göre gruplamalısınız. Bu durumda, kullanıcının Konsolidasyon iletişim kutusunda bunları belirterek gerekli iyileştirmeleri programı bilgilendirmesi gerekir. Bu pencere Şekil 2'de gösterilmektedir. 6.57 ve konsolidasyon sonuçları Şekil 2'de gösterilmektedir. 6.58. Bu durumda programın liste tasarımının hangi öğelerinin veri adları olarak kullanılması gerektiğini belirtmesi gerektiğini görmek kolaydır, çünkü bu durumda adlar formüllerin argümanları olarak kullanılır. gerçeğine dikkat etmelisin

Pirinç. 6.58. Örnek 10 için Konsolidasyon Sonuçları

konsolidasyon listesine dahil edilen aralıkların Öğe Adı sütunuyla başladığını.

Örnek 11. Her iki depoda farklı tedarikçilerden alınan her bir mal kaleminin toplam maliyetinin tahmin edilmesi gerekmektedir. Bu durumda, veriler Satıcı sütununa göre gruplandırılmalıdır, yani veriler, ikinci sütun - Satıcı'dan başlayarak konsolidasyon aralığına dahil edilmelidir. Yeni veri eklemeden önce konsolidasyon aralığından daha önce kullanılmış tüm bağlantıları kaldırmayı unutmayın. Bunu yapmak için, Aralık listesi alanında aralığın bağlantısını seçin ve Sil düğmesini tıklayın. Bu durumda konsolidasyon için gerekli bilgileri gösteren bir iletişim kutusu Şekil 2'de gösterilmektedir. 6.59.

Bu durumda veri konsolidasyonunun sonuçları Şekil 2'de görülebilir. 6.60.

Pirinç. 6.59. Örnek 11 Konsolidasyon Seçenekleri İletişim Kutusu

Pirinç. 6.60. Örnek 11 için Konsolidasyon Sonuçları

Örnek 12. Gerekçelendirme için finansal plan yılın ilk yarısı için arz hacimlerinin aylara göre dağılımını değerlendirmek gerekir. Bu durumda, malın türünü ve tedarikçinin ayrıntılarını dikkate almaya gerek yoktur. Bu soruyu cevaplamak için

Pirinç. 6.61. Örnek 12 için Konsolidasyon Seçenekleri İletişim Kutusu

Pirinç. 6.62. Örnek 12 için Konsolidasyon Sonuçları

Basitçe, verileri gruplamak için Teslimat Tarihi sütununu seçmeniz, yani konsolidasyon aralığına yalnızca iki sağ sütunda bulunan verileri dahil etmeniz gerekir. Bu sorunu çözmek için iletişim kutusu Şekil 2'de gösterilmektedir. 6.61.

Konsolidasyon sonuçları Şekil 2'de gösterilmektedir. 6.62.

Konsolidasyon, iki veya daha fazla form ve/veya verinin birleştirilmesi, birleştirilmesidir. Onlar. farklı yerlerden verileri tek bir yerde toplamanız ve birleştirmeniz gerekiyorsa. Ne işe yarar? Farklı dosyalardan / sayfalardan birden fazla tabloyu analiz edin. Farklı kaynaklardan gelen verileri tek bir tabloda toplayın. Ayrıca, daha ayrıntılı olarak, Excel'de konsolidasyon.

Uygulama yapmadan farklı sayfalardan veri toplamanın her zaman bir yolu vardır. özel aletler... Farklı sayfalarda birkaç tablomuz olduğunu varsayalım (örneğin, dallardaki verilerle). Tüm sayfalardaki veriler ve sonucu ayrı bir sayfada görüntüleme nasıl? En kolay yol bir formül kullanmaktır, "=" koymak ve her sayfadan hücreleri veya formülleri seçmek

Çok sayıda sayfa olduğunda (ancak toplanacak tüm hücrelerin aynı adrese sahip olması durumunda) zor bir tane kullanabilirsiniz.

Excel'de konsolidasyon. Özel alet

Excel'deki elektronik tablolarla olası eylemlerin çoğu için, bir istisna Veri Konsolidasyonu değil, özel bir araç vardır. Farklı tablolar aynı görünüme sahip değilse, fırsatı kullanmak en iyisidir. Veri - Konsolidasyon

Ham verilere bağlantılar oluşturun bu onay kutusu, kaynak tablolarda değişiklik olması durumunda birleştirilmiş verilerin yeniden hesaplanmasını sağlar.

Üst satır başlıkları- tabloların başlıklar tarafından dikkate alındığı, başlıkların hesaplamalara dahil olmadığı anlamına gelir.

Sol Sütun Değerleri- değerler sol sütuna göre gruplandırılmıştır.

Sonuç olarak, tüm veriler sol sütuna göre gruplandırılacaktır: