Excel'de hesaplanan alan nasıl oluşturulur? Transact-SQL'de Hesaplanan Sütunlar Hesaplanan Sütun Nasıl Oluşturulur

Bu yazımızda hesaplanan alanlardan bahsedeceğiz. Erişim sorguları. Tablo gibi bir sorgu da bir veya daha fazla alandaki verileri kullanarak her kayıt için sayısal, dize veya tarih değerleri üzerinde hesaplamalar yapabilir. Hesaplamanın sonucu sorgu tablosunda yeni bir hesaplanan alan oluşturur. Hesaplanan tablo alanlarından farklı olarak kaynak veritabanı tablolarındaki hesaplanan alanlar yeni alanlar oluşturmaz. Sorgu her yürütüldüğünde, hesaplamalar mevcut alan değerlerine göre yapılır.

Hesaplanan alan ifadelerinde alan adlarının yanı sıra sabitler ve işlevler de kullanılabilir. Bir ifadenin işlenmesi sonucunda yalnızca bir değer elde edilebilir.

Görev 1.ÜRÜN tablosunda PRICE ve RATE_VAT alanları bulunur. KDV dahil fiyatı hesaplayın ve bunu KDV dahil Fiyat tablosunun hesaplanan alanında elde edilen fiyatla karşılaştırın.

1. ÜRÜN tablosu için tasarım modunda bir seçme sorgusu oluşturun. NAME_TOV, PRICE, RATE_VAT ve Price with KDV alanlarını talep formuna sürükleyin (Şekil 4.6).
2. KDV dahil fiyatı hesaplamak için Alan satırının boş hücresine [PRICE]+[PRICE]*[RATE_VAT] ifadesini yazarak hesaplanmış bir alan oluşturun.
3. Hesaplanan alanda değeri 5000'den büyük olan kayıtları seçmek için Ölçüt satırına > 5000 girin
4. Bir ifade girdikten sonra, sistem varsayılan olarak hesaplanan İfade 1 alanının adını oluşturur ve bu, sorgu sonuçlarının bulunduğu tabloda sütun başlığı haline gelir. Bu ad, [PRICE]+[PRICE]*[VAT_RATE] ifadesinin önüne eklenecektir. Sorgudaki her yeni hesaplanan alan için ifade numarası bir artar. Hesaplanan alanın adı ifadeden iki nokta üst üste işaretiyle ayrılır. Adı değiştirmek için fare imlecini istek formunun hesaplanan alanına getirin ve farenin sağ tuşuna tıklayın. Bağlama duyarlı menüden, Özellikler(Özellikler) alanına ve Başlık satırına yeni bir alan adı girin - KDV1 dahil fiyat. Artık sorgu sonuçlarının bulunduğu tabloda, bu ad hesaplanan sütunun başlığında görüntülenecektir. Alan adı doğrudan istek formunda da düzeltilebilir.
5. Sorgunun sonucunu görüntülemek için düğmeye tıklayın Uygulamak Grupta (koş) sonuçlar(Sonuçlar). Tablonun hesaplanan alanı ve sorgu aynı değerlere sahiptir.
6. Talep kayıtlarından birinde ürünün fiyatını değiştirin. Hesaplanan her iki alandaki değerler anında yeniden hesaplanacaktır.
7. Hesaplanmış bir alanda veya seçim koşulunda karmaşık bir ifade oluşturmak için ifade oluşturucunun kullanılması önerilir. Oluşturucu, ifadede ihtiyaç duyulan alan adlarını tablolardan, sorgulardan, işlem işaretlerinden ve işlevlerden seçmenize olanak tanır. Hesaplanan alandaki ifadeyi kaldırın ve oluşturmak için oluşturucuyu kullanın.
8. Düğmeye tıklayarak İfade Oluşturucuyu çağırın İnşaatçı(İnşaatçı) grupta Sorgu kurulumu(Sorgu Kurulumu) şerit Tasarımı veya seçerek İnşa etmek Bağlama duyarlı menüde (Oluştur) seçeneğini seçin. Fare imlecinin önceden ifade giriş hücresine konumlandırılmış olması gerekir.
9. Pencerenin sol tarafında İfade Oluşturucu(İfade Oluşturucu) (Şekil 4.7) sorgunun temel aldığı ÜRÜN tablosunu seçin. Alanlarının bir listesi sağda görüntülenecektir. İfadeye çift tıklayarak gerekli alanları ve işleçleri sırayla seçin. İfade pencerenin üst kısmında oluşturulacaktır. Oluşturucunun, ait olduğu tablonun adını alan adından önce belirttiğini ve bunu alan adından bir ünlem işaretiyle ayırdığını lütfen unutmayın.
10. Tamam butonunu tıklayarak hesaplanan alanda ifade oluşturma işlemini tamamlayın.
11. Talebi ― KDV dahil fiyat adı altında kaydedin ve kapatın.
12. Kaydedileni gezinme alanında seçip içerik menüsünden Aç komutunu seçerek yürütün.

Görev 2. Hesaplanan alanlarda ve filtre koşullarında yerleşik işlevleri kullanabilirsiniz. Access'in tanımlanmış 150'den fazla işlevi vardır.
Belirli bir ayda sevkiyatın yapıldığı tüm faturaların seçilmesi gerekli olsun. FATURA'da sevkiyat tarihi, Tarih/Saat veri türüyle DATE_OTG alanında saklanır.

1. FATURA tablosu için tasarım modunda bir seçme sorgusu oluşturun. NOM_NAKL ve CODE_SK alanlarını forma sürükleyin (Şekil 4.8).
2. Boş bir satır hücresinde hesaplanan alan oluşturma Alan(Alan) ifadelerinden birini buraya yazarak: Format([INVOICE]![DATE_DATE];"mmmm") - bu işlev ayın tam adını döndürür
   veya Format([INBOOK]![DISPOSITION_DATE];"mm") - bu işlev ay numarasını döndürür.
3. Belirli bir ayda düzenlenen faturaları seçmek için Seçim Koşulu (Kriter) satırındaki hesaplanan alana ayın adını (örneğin Mart (Şekil 4.8) veya ay numarasını (örneğin 3) aşağıdaki kurallara uygun olarak girin) Format işlevindeki parametre.
4. Butona tıklayarak talebinizi tamamlayın Uygulamak Grupta (koş) sonuçlar(Sonuçlar) şerit sekmesinde İsteklerle çalışma | Oluşturucu(Sorgu Araçları | Tasarım).
5. Hesaplanan alana Month(INVOICE!DATE_OTG) fonksiyonunu yazın ve bu fonksiyonun tarihten çıkarılan ay sayısını döndürdüğünden emin olun.
6. İkinci çeyreğe ilişkin tüm satırları seçmek için Ölçüt satırına, ifadenin değerinin belirtilen aralık dahilinde olup olmadığını belirleyen 4 ile 6 Arasında operatörünü girin.
7. Hesaplanan alana MonthName(Month(INVOICE!DATE_OTG)) ifadesini yazın ve MonthName fonksiyonunun ay numarasını tam adına dönüştürdüğünden emin olun.

Bunu güçlendirmek için video eğitimini izleyin.

PivotTable'ları kullanarak verileri analiz ettiğinizde, genellikle orijinal veri kümesinin dışında gerçekleştirilen hesaplamalardan kaynaklanan değerleri raporunuza dahil etmeniz gerekir. Excel programı kullanıcıya, hesaplanan alanları ve hesaplanan öğeleri kullanarak pivot tablolarda hesaplamalar yapma olanağı sağlar. İncirde. Şekil 1, hesaplanan bir alanın verileri farklı bir perspektiften nasıl sunabileceğini göstermektedir. Pivot tablo, satış hacmini ve her pazarda ekipman satışı için harcanan süreyi (saat cinsinden) görüntüler. Saat başına ortalama geliri hesaplayan hesaplanmış alan, pivot tablodaki verileri analiz etme yeteneğini geliştirir.

Pirinç. 1. Mevcut bir pivot tabloda hesaplanmış bir alan oluşturmanız gerekir Saat başına ortalama gelir Analiz edilen verilere yeni bir anlam kazandıracak

Notu veya formatında indirin, formattaki örnekler

Hesaplanan alan, mevcut PivotTable alanlarını temel alan hesaplamalar sonucunda oluşturulan bir veri alanıdır. Hesaplanan alan veri kümesine sanal sütun olarak eklenir. Bu sütun kaynak verilere dahil edilmez, bir formülle belirlenen değerleri içerir ve PivotTable verileriyle diğer PivotTable alanlarıyla aynı şekilde etkileşime girer.

Hesaplanan alanlarla PivotTable'a formül ekleyerek kendi alanınızı oluşturabilirsiniz. Yeni oluşturulan veriler, mevcut verilerle etkileşime girerek pivot tablonun parçası haline gelecektir. Pivot tabloları güncellediğinizde hesaplanan alanlar yeniden hesaplanır ve pivot tablonun kendisi orijinal veri kümesinde olmayan bilgilerle doldurulur.

Şimdi şek. 5.1 ve kendinize şunu sorun: Hesaplanan alanları neden eklemeniz gerekti? İhtiyacınız olan bilgiyi elde etmek için neden normal hücre formüllerini kullanmıyor veya gerekli hesaplamaları doğrudan kaynak tablo üzerinde yapmıyorsunuz?” Bu soruları cevaplamak için Şekil 1'de gösterilen hesaplanan alanı oluşturmak için kullanabileceğiniz farklı yöntemlere bakalım.

Yöntem 1: Veri kaynağına hesaplanan alan ekleme

Şekilde gösterildiği gibi, veri kaynağınıza hesaplanan bir alan ekleyebilirsiniz. 2, PivotTable'ın bu alanı standart bir veri alanı olarak kullanmasına olanak tanır. Veri kaynağı sayfasındaki bilgilerin düzenlenmesi bunun yapılmasına izin veriyorsa en iyi çözüm ne olabilir? Bu arada, yeni bir sütun eklemek yalnızca pivot tablonun güncellenmesini değil, aynı zamanda pivotun temel aldığı kapsamın da değiştirilmesini gerektirecektir. Bunu yapmak için özete tıklayın ve menüye göz atın Analiz → Veri Kaynağı → Veri Kaynağı.

Orijinal verileri bir Tabloya dönüştürmeye çalışıyorum (bu, Şekil 2'de ve ekteki Excel dosyasında yapılmıştır). Bu durumda, öncelikle başlıklar vurgulanır, ikinci olarak, genellikle veri araştırması için yararlı olan filtreli düğmeler görünür, üçüncü olarak, satırlar birer birer renklendirilir ve son olarak en önemli şey, satır ve satır eklerken sütunlarında pivot tablonun veri alanını değiştirmek gerekli değildir. Bu şekilde belirtilen alan dikdörtgen bir aralık değil, Tablonun adıdır. Bizim durumumuzda - Kaynak(Şek. 3).

Pirinç. 3. Özel bir Excel aracına dayalı bir özetin oluşturulması – Tablo

Ancak bu yöntemin dezavantajları da vardır: birincisi, kaynak verilere bir sütun eklemek her zaman uygun değildir ve ikincisi, kaynak verilerin yapısı değişirse olanaklar sınırlıdır (örneğin, 1C'den veri aktarıyorsunuz ve yeni dışa aktarmada bir sütun daha var; bu yeni sütun, hesaplama sütununuzun üzerine yazılacaktır).

Yöntem 2: Hesaplanan alan oluşturmak için PivotTable'ın dışında bir formül kullanma

Pivot tablonun yanına hesaplanmış bir alan ekleyebilirsiniz. İncirde. Bir sütundaki her hücrede 4 adet Saat başına ortalama gelir bir pivot tabloya başvuran bir formülle sağlanır. Bu arada, bir pivot tablo hücresine başvurmaya çalıştığınızda formülünüzde GET.PIOTTABLE.DATA işlevi açılırsa, formülü "çekemezsiniz". Bu zorluğun üstesinden gelmek için notu okuyun.

Pirinç. 4. PivotTable oluşturduktan sonra bir formül girdiğinizde, aslında PivotTable güncellendiğinde değişen hesaplanmış bir alan eklemiş olursunuz.

Bu yöntem, PivotTable yenilendiğinde güncelleştirilen hesaplanmış bir alan eklese de PivotTable yapısındaki herhangi bir değişiklik, formülü kullanılamaz hale getirebilir. Örneğin, Satış pazarı alanını pivot tablodaki alanlar listesinin FİLTRELER alanına sürüklemenin bir sonucu olarak, raporun yapısı değişir ve bunun sonucunda hesaplanan alanda bir hata mesajı görünür (Şek. .5). Pivot tablonun SÜTUNLAR alanına başka bir alan eklerseniz sütun Saat başına ortalama gelirüzerine yazılacaktır.

Yöntem 3: Hesaplanan alanı PivotTable'a doğrudan ekleme

Hesaplanan alanı doğrudan PivotTable'a eklemek çoğu zaman en iyi çözümdür. Bu yöntemi kullanırsanız formüllerde değişiklik yapmanıza gerek kalmaz. Ayrıca tablonun değişen veri kaynağına göre otomatik olarak ayarlanması da mümkün olacaktır. Ölçü birimlerinde değişiklik olması durumunda alanların otomatik olarak yeniden hesaplanmasını sağlamak için gerekli esneklik derecesi sağlanacaktır.

Bu yöntemin bir diğer avantajı ise eğer kullanırsanız pivot tablonun yapısını ve hatta hesaplanan alanlarda kullanılan farklı ölçü birimleri için veri alanlarını bile değiştirebiliyorsunuz. Aynı zamanda formüllerde hata olmayacağından ve hücre referanslarının kaybolmayacağından emin olabilirsiniz.

Şekilde gösterilen PivotTable raporu Şekil 6, Şekil 2'de gösterilen bir özet tabloyu sunmaktadır. 1, ancak her hizmet ve her pazar için saat başına ortalama geliri gösterecek şekilde yeniden yapılandırılmıştır.

Pirinç. 6. Daha önce oluşturulan hesaplanan alan, pivot tablonun yapısı değiştirilse bile "geçerli" kalır; bu, her hizmet ve her satış pazarı için saat başına ortalama gelirin görüntülenmesiyle sonuçlanır.

Hesaplanan alan oluşturma

Hesaplanmış bir alan oluşturabilmeniz için önce bir PivotTable açmanız veya oluşturmanız gerekir. Örneğimizde Şekil 2'de gösterilen pivot tablo kullanılmaktadır. 1, ancak D sütunu olmadan. Hesaplanan bir alan oluşturmak için iletişim kutusunu etkinleştirin Hesaplanan alan ekleme. Bunu yapmak için bağlamsal sekmeler grubunu etkinleştirmek üzere pivot tabloya tıklayın Pivot Tablolarla Çalışmak. Sekmeye git Analiz, gruba Hesaplamalar, düğmeye tıklayın Alanlar, öğeler ve kümeler ve menüden Hesaplanan alan komutunu seçin (Şek. 7).

Ekranda bir iletişim kutusu görünecektir Hesaplanan alan ekleme(Şekil 8). İletişim kutusunun üst kısmında iki metin alanı vardır: İsim Ve Formül. Bu alanlarda hesaplanan alanın adını belirtmeli ve gerekli veri alanlarını ve matematiksel operatörleri belirterek formül oluşturmalısınız. Hesaplanan alana açıklayıcı bir ad verilir Saat başına ortalama gelir. Bu ad, gerçekleştirilen matematiksel işlemin türünü doğru bir şekilde tanımlayacak şekilde seçilmelidir. Varsayılan metin alanı Formül iletişim kutusu Hesaplanan alan ekleme= 0 ifadesini içerir. Formüle girmeden önce sıfırı kaldırmalısınız. Alandaki alanlardan birini seçin Alanlar; bizim durumumuzda - Satış hacmi ve tıklayın Alan ekle. Alan adı satırda görünecek Formül. Bölme işaretini / girin ve ardından ikinci alanı seçin – Satış dönemi (saat olarak). Tıklamak Eklemek, ve daha sonra TAMAM Yeni hesaplanan alanı etkinleştirmek için.

PivotTable'da yeni bir hesaplanan alan görünecektir Saat başına ortalama gelir(bkz. Şekil 1). PivotTable'a yeni bir hesaplanan alan başarıyla eklendi. Artık bu yeni alanın ayarlarını, diğer herhangi bir alanın (örneğin, alan adı, sayı biçimi veya renk) ayarlarını değiştirdiğiniz gibi değiştirebilirsiniz.

Bu, sütunu veri kaynağına eklediğiniz anlamına mı geliyor? HAYIR. Hesaplanan alanlar öğeler gibidir ara toplam Ve toplam tutar PivotTable işlevleri varsayılandır çünkü bunların tümü PivotTable değiştirildiğinde veya güncelleştirildiğinde verileri yeniden hesaplayan matematiksel işlevlerdir. Hesaplanan alanlar, veri kaynağındaki açıkça tanımlanmış alanları taklit eder. Bunları sürükleyebilir, alan ayarlarını değiştirebilir ve hesaplanan diğer alanlarla birlikte kullanabilirsiniz.

Lütfen şek. 8 Girdiğiniz formülün formatı standart satırda kullanılana benzer Excel formülleri. Temel fark, katı hücre başvuruları veya sayısal değerler kullanmak yerine, hesaplamalarda kullanılan bağımsız değişkenlere değer atamak için PivotTable veri alanlarına başvuruda bulunmanızdır.

Hesaplanmış Öğeler Oluşturma

Hesaplanan öğe, aslında aynı alandaki diğer satırlar üzerinde hesaplamaların yapılması sonucunda eklenen sanal bir veri satırıdır. Çoğu zaman, hesaplanmış bir öğe oluştururken elde edilene benzer bir sonuç, verilerin gruplandırılmasıyla elde edilebilir. Aslında birçok durumda gruplandırma, hesaplanmış üyelere mükemmel bir alternatif sağlar.

Şekil 2'de gösterilen özet tabloda. Şekil 9'da belirli raporlama dönemleri için toplam satış hacmi belirtilmektedir (alan Raporlama dönemi). Son altı dönemin ortalama satış hacmini önceki yedi dönemin ortalama satış hacmiyle karşılaştırmak istediğinizi düşünün. Daha kesin olmak gerekirse, P01–P07 dönemlerinin ortalamasını belirleyip bunu P08–P13 dönemlerinin ortalamasıyla karşılaştırmamız gerekiyor.

İşaretçiyi alanın herhangi bir hücresine yerleştirin Raporlama dönemi bağlamsal şerit sekmesine git Analiz gruba Bilgi işlem, ve düğmeye tıklayın Alanlar, öğeler ve kümeler. Açılan menüde komutu seçin Hesaplanan nesne(Şekil 10).

Bir iletişim kutusu açılacaktır (Şekil 11). İletişim kutusunun üst kısmında hangi alanla çalıştığınızın belirtildiğine dikkat edin. Bu durumda bu alan Raporlama dönemi. Ayrıca liste alanın tüm öğelerini içerir Raporlama dönemi. Hesaplanan öğeye bir ad verin ve ardından doğru sonucu üretecek gerekli veri öğeleri ve işleç birleşimini içeren bir formül oluşturun. Formül alana girilir Formül ve içinde kullanılan gerekli veri öğeleri listeden seçilir Elementler. Düğmeye bas TAMAM ve hesaplanan öğe pivot tabloya eklenecektir (Şekil 12).

Pirinç. 11. İletişim kutusu Hesaplanmış Öğe Ekleme

Benzer şekilde, P08–P13 dönemleri için ortalama satışları temsil edecek hesaplanmış bir öğe oluşturun. Daha sonra yalnızca iki hesaplanan öğe bırakarak bireysel raporlama dönemlerini gizleyin. Küçük bir biçimlendirmeyle hesaplanan öğelerimiz önceki ve sonraki altı aydaki ortalama satışları karşılaştırmak için kullanılacaktır (Şekil 13).

Hesaplanan öğelerin hesaplanmasında kullanılan veri öğelerini gizlemezseniz alt toplamlar ve genel toplamlar hatalı olabilir.

PivotTable Hesaplamalarının Avantajları ve Dezavantajları

Hesaplanan alanları ve öğeleri kullanmanın bazı dezavantajları da vardır. PivotTable'larda verileri hesapladığınızda ne olacağını anlamak önemlidir, ancak verilerinizi analiz ederken hataların önlenmesine yardımcı olmak için hesaplanan alanların ve hesaplanan öğelerin sınırlamalarını anlamak daha önemlidir.

İşlemlerin önceliği. Elektronik tablolarda olduğu gibi, hesaplanan öğeler ve alanlar için formüllerde herhangi bir operatör kullanılabilir: +, –, *, /, %, ^ vb. Ayrıca elektronik tablolar gibi pivot tablolardaki hesaplamalar da operatör önceliğine göre yapılır. (2+3) *4/%50 gibi birden çok operatörü birleştiren bir hesaplama gerçekleştirdiğinizde Excel, ifadeyi değerlendirir ve hesaplamayı belirli bir sırayla gerçekleştirir. Bu sırayı bilmek sizi birçok hatadan kurtaracaktır.

Yani Excel'deki işlem sırası aşağıdaki gibidir:

• parantez içindeki ifadelerin işlenmesi;
• aralık işleme(:);
• kavşakların (alanların) işlenmesi;
• birleşimleri tanıtıcı (;);
• bir olumsuzlama işlemi gerçekleştirmek;
• yüzde değerlerinin dönüştürülmesi (örneğin, %50, 0,50'ye dönüştürülür);
• üs (^);
• Çarpma ve bölme (/); bu işlemler eşit önceliğe sahiptir;
• toplama (+) ve çıkarma (-); bu işlemler eşit önceliğe sahiptir;
• metin verilerini birleştirme (&) bu işleme birleştirme de denir;
• karşılaştırma işlemlerinin gerçekleştirilmesi (=,<>, <=, >=).

Aynı ifadedeki eşdeğer operatörler her zaman sırayla (soldan sağa) yürütülür.

Basit bir örneğe bakalım. Bildiğiniz gibi (2+3)*4 ifadesi 20 sonucunu verir. Parantezleri kaldırıp 2+3*4 ifadesini bırakırsanız Excel şunu hesaplayacaktır: 3*4 = 12 + 2 = 14.

Başka bir örneğe bakalım. Formül olarak 10^2 ifadesini girerseniz; 10 sayısının karesini belirtirseniz program cevap olarak 100 değerini döndürür. –10^2 ifadesini girerseniz değerin –100 döndürmesini bekleyebilirsiniz. Ancak Excel yine 100 değerini döndürecektir. Bunun nedeni programın olumsuzlama işlemini üs alma işleminden önce gerçekleştirmesidir. 10 değeri önce -10'a dönüştürülür ve -10*-10'un sonucu aslında 100'dür. -(10^2) formülünde parantezlerin kullanılması, programın önce üstelleştirmeyi, ardından olumsuzlamayı gerçekleştirmesini ve beklenen değeri döndürmesini sağlar. sonuç - 100.

Hücre başvuruları ve adlandırılmış aralıklar. Hesaplanan alanlar ve öğeler oluşturulurken hücre başvuruları veya adlandırılmış aralıklar kullanılamaz çünkü PivotTable'da hesaplanan nesneler oluşturduğunuzda, aslında nesne alanının dışında çalışıyorsunuz demektir. Kullanıcılara sunulan tek veri pivot tablo önbelleğindedir. Önbelleğin dışına çıkamayacağınız için oluşturduğunuz formülde hücrelere veya adlandırılmış aralıklara referans veremezsiniz.

Çalışma Sayfası İşlevleri. Bağımsız değişken olarak hücre başvurularını veya adlandırılmış nesneleri kullanmayan herhangi bir çalışma sayfası işlevini kullanabilirsiniz. Aslında, hücre başvuruları veya adlandırılmış nesneler gerektirmeyen herhangi bir çalışma sayfası işlevini kullanabilirsiniz. COUNT, AVERAGE, IF, AND, NO ve OR gibi işlevler bu kategoriye girer.

Sabitler. Pivot tablolarda gerçekleştirilen hesaplamalarda herhangi bir sabiti kullanabilirsiniz. Sabitler zamanla değişmeyen statik değerlerdir. Örneğin, aşağıdaki formülde 5 sayısı bir sabittir: [Satılan birim sayısı] *5. Her ne kadar anlamı Satılan birimler Eldeki verilere göre değişebilir, 5 sayısı her zaman aynı olacaktır.

Toplamlara bağlantılar. Hesaplamaları gerçekleştiren formüller PivotTable alt toplamlarına veya toplamlarına başvuramaz. Başka bir deyişle, devam eden veya toplam hesaplamanın sonucunu, hesaplanan alanda değişken veya bağımsız değişken olarak kullanamazsınız.

Hesaplanan alanlar için özel kurallar. Hesaplanan alanlardaki hesaplamalar, bireysel veri öğelerine göre değil, her zaman toplamlara göre gerçekleştirilir. Basitçe söylemek gerekirse Excel, hesaplanan bir alanı değerlendirmeden önce her zaman veri alanlarını, alt toplamları ve toplamları hesaplar. Bu, hesaplanan alanınızın her zaman kaynak verilerin toplamlarına uygulanacağı anlamına gelir. Şekil 2'de gösterilen örnek. Şekil 14 bunun veri analizini nasıl etkileyebileceğini göstermektedir.

Pirinç. 14. Hesaplanan alan bireysel veri öğeleri için geçerli olsa da alt toplamlar için kullanılması matematiksel olarak doğru değildir.

Her çeyrekte, her bir ürünün nihai satış hacimlerini, satılan adet sayısı ile o ürünün birim fiyatı çarpılarak elde etmeniz gerekir. Önce ilk çeyrek verilerine bakarsanız sorunu hemen tespit edersiniz. 220+150+220+594 toplamı yani 1184 hesaplamak yerine adet sayısı toplamı mal fiyatları toplamı ile çarpılarak hatalı değer elde edilmektedir. Ne yazık ki bu sorun çözülemez ancak çözülebilir. PivotTable'dan alt toplamları ve alt toplamları kaldırın ve ardından PivotTable'ın alt kısmında yeni bir toplam hesaplayın.

Hesaplanan üyelerin kullanımına ilişkin özel kurallar. PivotTable ortalamaları, standart sapmaları veya sapmaları hesaplayan hesaplanmış öğeleri kullanamaz. Bunun tersine, hesaplanan bir öğeyi içeren PivotTable'da ortalamaları, standart sapmaları veya varyansları kullanamazsınız. Hesaplanan bir öğe oluşturmak için sayfa alanını kullanamayacaksınız veya hesaplanan öğeleri rapor filtre alanına taşıyamayacaksınız. Gruplandırılmış bir alan içeren bir rapora hesaplanmış bir öğe ekleyemezsiniz ve bir PivotTable'da hesaplanmış bir öğe içeren herhangi bir alanı gruplandıramazsınız. Hesaplanan bir öğe formülü oluşturduğunuzda, bir öğeye harici alandan başvuruda bulunamazsınız.

Pivot tablolarda hesaplamaları yönetin ve destekleyin

Pivot tablolarla çalışırken, pivot tabloyu bireysel değerleri kopyalamak için gerekenden daha uzun süre saklamanın bir anlamı olmadığı durumlar sıklıkla ortaya çıkar. Bununla birlikte, pivot tabloyu ve tüm öğelerini değiştirmeden saklamanın daha karlı olduğu durumlar da vardır. Gereksinimleri değiştirerek ve kaynak veri hacmini artırarak bir PivotTable'ı sürdürür ve yönetirseniz, hesaplanan alanları ve hesaplanan üyeleri yönetmeniz gerekir.

Pivot tablolarda hesaplamaları düzenleme ve silme. Hesaplama ayarlarını değiştirirseniz veya artık hesaplanmış bir alana veya hesaplanmış üyeye ihtiyacınız yoksa hesaplamayı düzenlemek veya silmek için ilgili iletişim kutusunu açabilirsiniz. İletişim kutusunu etkinleştir Hesaplanan alan ekleme veya Hesaplanmış Öğe Ekleme(Şekil 7 ve 10'daki açıklamalara bakın) ve içindeki açılır listeyi seçin İsim(Şekil 15).

Hesaplanan alanlarda hesaplamaların gerçekleştirilme sırasını değiştirin. PivotTable'daki bir hücrenin değeri, birden fazla hesaplanan öğenin hesaplanmasının sonucuna bağlıysa, hesaplanan alanlardaki işlemlerin gerçekleştirilme sırasını değiştirebilirsiniz. Başka bir deyişle, bireysel hesaplamaların gerçekleştirileceği sırayı belirleyebilirsiniz. İletişim kutusunu açmak için , işaretçiyi pivot tablonun herhangi bir yerine konumlandırın ve simgeyi tıklayın Alanlar, öğeler ve kümeler. Açılır menüde Hesaplama sırası komutunu seçin (Şek. 16).

Pirinç. 16. İletişim kutusunu açın Hesaplama sırası

İletişim kutusunda Hesaplama sırası(Şekil 17), pivot tabloya eklenen tüm hesaplanmış öğeler görüntülenir. Listeden hesaplanan herhangi bir öğeyi seçin ve düğmeleri kullanın Yukarı, Aşağı Ve Silmek. Formüllerin listede görünme sırası, PivotTable'da hesaplamaların gerçekleştirilme sırasını tam olarak belirler.

Formüllerin belgelenmesi. Excel, PivotTable'da kullanılan hesaplanan alanları ve hesaplanan öğelerin yanı sıra hesaplamaların nasıl gerçekleştirileceğini ve formüllerin nasıl uygulanacağını görüntüleyen mükemmel bir araç sağlar. Bu araç, üçüncü taraf PivotTable'ı analiz etmek için veya burada hangi hesaplamaların kullanıldığını ve bunların hangi alanları ve öğeleri etkilediğini hızlı bir şekilde belirlemeniz gerektiğinde çok kullanışlıdır. PivotTable hesaplamalarına ilişkin bir rapor oluşturmak için işaretçinizi PivotTable'da herhangi bir yere getirin ve Alanlar, öğeler ve kümeler ve açılır menüden komutu seçin Formülleri türetin. Excel, hesaplanan alanlar ve öğeler hakkında ayrı bir sayfada bir rapor oluşturacaktır (Şekil 18).

Pirinç. 18. Takım Formülleri türet hesaplamaları hızlı ve kolay bir şekilde pivot tabloda belgelemenize olanak tanır

Not Jelen, Alexander'ın kitabına dayanarak yazılmıştır. . Bölüm 5.

Tablo sözcüğü büyük harfle yazılmıştır çünkü bu yalnızca bir tablo değil, ayrı bir Excel aracıdır.

Hesaplanan alan oluşturmanın ilk yolu, formun hesaplanan sütunları içeren bir sorguyu temel almasına dayanır. Form, veri kaynağı olarak hesaplanan sorgu sütununu belirten bir metin alanı oluşturur. Kullanıcının içindeki verileri değiştirememesi için alan kilitlenir. Veriler sorgu yürütme sırasında hesaplanır. Bir alanı modda kilitlemek için Tasarımcı pencerede Özellikler Veri sekmesinde arka arkaya hesaplanan alan Kilit girmek EVET.

İkinci durumda form bir tablo esas alınarak oluşturulur. Formda, veri kaynağı olarak bir ifadenin belirtildiği yeni bir alan oluşturulur, örneğin = [Birim Fiyat]*Miktar. İfadelerde alanlara atıfta bulunurken alan adının birden fazla kelimeden oluşması durumunda köşeli parantez kullanılması zorunludur. Bir ifadenin doğrudan bir kontrole (alan) girilmesine, eğer kısaysa ve alanın uzunluğu buna izin veriyorsa izin verilir. Aksi takdirde ifade özellik hücresine girilir. Veri pencere Özellikler. Bunu yapmak için ifade oluşturucuyu kullanabilirsiniz. İfade özellik hücresine sığmıyorsa + tuşlarına basıp pencereyi açmanız gerekir. Giriş alanı. Gerekirse, ekranda görüntülendiğinde alan formatını ayarlamanız gerekir.

6. Düğme Formu Oluşturma

Menü komutunu seç Servis/Yardımcı Programlar/Butonlu Form Yöneticisi.

Düğme formu oluşturmak isteyip istemediğinizi soran iletişim kutusunda, EVET.

Ekranda bir pencere görünecek Düğme Form Yöneticisi. Bu pencere düğme formu sayfalarının bir listesini içerir. İşin başında pencere bir sayfadır - Ana düğme formu. Pencerede düğmeye tıklayın Değiştirmek.

Bir iletişim kutusu açılır Bir düğme formu sayfasını değiştirme. Formun adını düzenleyin.

Düğmeye bas Yaratmak. Bir pencere belirir Düğme form öğesini değiştirme.

Tarlada Metin Oluşturulacak düğmenin adını girin. Düğmenin adı, kullanıcı tarafından çalışmayı planladığı nesnelerle ilişkilendirilmelidir. İkinci alan Takım Bu düğmeye tıkladıktan sonra gerçekleştirilecek eylemi tanımlamanıza olanak tanır. Açılır listeden uygulamanız için gereken komutu seçin.

Tüm formlar ve raporlar için bunları türe göre yapılandırarak düğmeler ekleyin.

Uygulamadan çıkmak için bir düğmenin yanı sıra, hızlı erişimİle Düğme form yöneticisi.

Uygulamayı şu şekilde ayarlayın: Ana düğme formu Veritabanını açarken açıldı. Bunu yapmak için menü komutunu kullanın Araçlar/Başlatma Seçenekleri.

ÖRNEK:

Pirinç. 28. Üst düzey düğme formu

Lab 3 Sorgu Oluşturma

İşin amacı - Veritabanı sorguları geliştirmede pratik beceriler kazanın.

Egzersiz yapmak:

Tipik sorguları kullanarak veritabanı şemasını test edin. Aşağıda açıklanan her türden en az bir istek oluşturmalısınız. Raporda sorgular doğal dilde, SQL'de sunulmalı ve ayrıca sorgunun yürütülmesinin sonucunun bir örneğini de sunmalıdır.

İş sırası

1.Sorgu Oluşturucu'yu kullanarak tek tablo seçme sorgusu oluşturma

İÇİNDE veritabanı penceresi yer imine tıklayın İstekler.

Düğmeye bas Yaratmak.

Pencerede Yeni istek seçme Oluşturucu ve düğmeye basın TAMAM.

Pencerede Tablo ekleme liste penceresinden sorgunun temel alınacağı tabloyu (veya sorguyu) seçin ve düğmesine tıklayın Eklemek. Geçerli pencereyi kapatın. Daha sonra ekranda bir pencere görünecektir Sorgu oluşturucu.

İsteğe bir alan ekleyin. Bunu yapmak için kaynak tablosu, pencerenin üst kısmında bulunur örnek istek, istediğiniz alanı seçin. Vurgulanan alan üzerinde farenin sol tuşuna çift tıklayın. Aynı zamanda pencerenin alt kısmında İstekler istek formunda seçilen alana karşılık gelen bir sütun görünecektir.

Ayrıca bir isteğe aşağıdaki şekilde alan ekleyebilirsiniz: sol düğme fareyi seçilen alanın üzerine getirin ve alanı bırakmadan, alanı istek formunda istediğiniz yere sürükleyin; Alanların açılır listesini görüntülemek ve gerekli alanı buradan seçmek için istek formundaki bir hücre alanına tıklayın; Talep formundaki hücre alanına tıklayın ve alan adını girin.

Tüm tablo alanlarını bir sorguya eklemek için yıldız işaretiyle (*) belirtilen alanı seçin. Yıldız işareti sorgu oluşturma işini kolaylaştırır, ancak alanların seçilmesine ilişkin koşulların sıralanmasını ve girilmesini daha karmaşık hale getirir.

Varsayılan olarak tüm istek alanları sıralanmaz. Gerekirse satırda belirtebilirsiniz Sıralama sorgu formu, sütun değerlerinin nasıl görüntüleneceği: artan veya azalan. Birden çok alanda bir sıralama düzeni belirttiğinizde, kayıtlar önce en soldaki sütuna göre, ardından soldan sağa sonraki her sütuna göre sıralanır.

Çizgide Ekrandaki çıktı sorguyu çalıştırdıktan sonra kayıt kümesinde görmek istediğiniz kutuları işaretleyin. Varsayılan olarak istek formunda yer alan tüm alanlar görüntülenir.

Satırdaki istek formunda Seçim koşulu ve çizgide Veya Veritabanından örnekleme koşullarını belirtin. Koşullar mantıksal ifadelerdir.

Tek satırda bulunan seçim koşulları üzerinde mantıksal bir işlem gerçekleştirilir VE. Bir alan için çeşitli seçim koşulları iki yoldan biriyle ayarlanabilir: tüm koşulları satırın bir hücresine girin Seçim koşulu bunları mantıksal bir operatörle bağlayarak VEYA veya her koşulu satırın ayrı bir hücresine girin Veya. Hücre alanının sonraki satırına git VEYA anahtarla<>. İstek formunun bir hücresindeki ifadeler klavyeden girilir veya bunları oluşturmak için kullanılır. İfade Oluşturucu komutu tarafından başlatılan İnşa etmek satırla ilişkili içerik menüsünden Seçim koşulu(veya düğmeye basabilirsiniz) İnşa etmek araç çubuğunda Erişim).

Düğmeye tıklayarak sorgu sonuçlarını görüntüleyin Öğle yemeği(ünlem işaretli düğme) veya düğme Görüş araç çubuğunda.

Bir sorgu özelliğini değiştirmeniz gereken durumlarda pencerenin üst kısmındaki boş alana çift tıklayın. Yapıcıyı iste - bir pencere açılacak Özellikleri Talep Et.

Modlarda oluşturulan sorgular Tasarımcı Ve Ustalar, SQL dilinde komutlar oluşturun. Bu komutları menüden seçerek görüntüleyebilirsiniz. Görüş seçenek modSQL.

ÖRNEK:

100'den büyük, ancak 300'den az ve maliyeti 100 ruble'den az olan ürünler hakkında bilgi seçin.

Pirinç. 10. Koşullu sorgu yapıcısı

Pirinç. 11. Koşullu sorgu sonucu

2. Bir alanda iç birleştirme

Bir sekme seçin İstekler veritabanı penceresinde düğmeye tıklayın Yaratmak. Bir iletişim kutusu açılacaktır Yeni istek. Bir seçenek seçin Oluşturucu.

Pencerede Tablo ekleme sorgunun oluşturulacağı birkaç ilgili tabloyu sırayla seçin. Tepede Sorgu oluşturucu ilgili tablolar görüntülenir.

Talep formunu doldurun.

Düğmeye bas Öğle yemeği veya Görüş Sorgunun sonuçlarını görüntülemek için.

ÖRNEK:

Tedarik sözleşmesi yapılan ürünlerin adlarını, üreticilerinin adlarını ve tedarik hacmini belirterek seçin.

Pirinç. 12. Çok tablolu sorgu tasarımcısı

Pirinç. 13. Çok tablolu bir sorgunun sonucu

3. Harici bağlantılar

Bölümün 1-3. adımlarını izleyin Bir alanda dahili birleştirme.

Daha önce kaydedilmemişse tablolar arasında ilişki kurun Şema Veritabanı.

Vurgulamak için alan birleştirme satırına tıklayın.

Menüden komutları seçin Görüş seçenek Birleştirme seçenekleri. Bir iletişim kutusu belirir Birleştirme Seçenekleri.

Birleştirme türünü seçin, düğmeye tıklayın TAMAM. Bu işlemden sonra birleştirme satırında bağlantı türünü belirten bir ok görünecektir.

4. Talep formuna grup işlemi ekleme

Düğmeye bas Grup Operasyonları, imza  araç çubuğunda Sorgu oluşturucu. Satırın üstündeki talep formunda Sıralamaçizgi görünecek Grup işlemi.

Sırayla değiştirin Grup işlemi kurulum Gruplama gerekli olanlar için grup operasyonu açılan kutudan seçerek: Toplam, Ort., Saymak, Miçinde, Maksimum, StDev, Var, Birinci, Son, İfade(ifade),Nerede(durum),Grup ile(gruplama).

İfade hücreye alan adı yerine ifade girmenizi sağlar , örneğin belirli bir sütunun değer aralığını hesaplamak için X:Maksimum([ X]) – Min.([ X]).

Durum bu alanın kayıt seçme koşullarını ayarlamak için kullanılacağını belirtir. Seçim koşulunun kendisi satır hücresine yazılır Seçim koşulu.

3. İsteği tamamlayın.

ÖRNEK:

Her üreticinin toplam ürün sayısını adını belirterek seçin.

Pirinç. 14. Kayıtların gruplandırılmasıyla sorgu oluşturucu

Pirinç. 15. Kayıtların gruplandırılmasıyla ilgili sorgunun sonucu

5. Hesaplanan alanları istek formuna dahil etme

Sorgu oluşturucu .

Satıra tıklayın Alan istek formunun boş sütununa göre. Sütun adını, ardından iki nokta üst üste işaretini ve ardından bir ifadeyi girin. Bir ifade girmek için düğmeye tıklayın İnşa etmek, avantaj sağlamak İfade Oluşturucu.

Tepede İfade Oluşturucu Giriş alanında bir ifade oluşturun.

Hesaplanan alan sütununda imleci satıra taşıyın Grup işlemi ve açılır listeden seçim yapın İfade(eğer seçeneği seçmezseniz İfade, yürütüldüğünde istek bir iletişim kutusu açar Parametre değerini girin veya bir hata mesajı döndürür).

İsteği çalıştırın.

ÖRNEK:

Her sözleşme için toplam sipariş tutarını hesaplayın (mal miktarı ile fiyatın artı nakliye masraflarının çarpımı olarak tanımlanır).

Pirinç. 16. Hesaplanan alana sahip sorgu oluşturucu

Pirinç. 17. Hesaplanan alanla yapılan sorgunun sonucu

6.Parametrik Sorgu Oluşturma

Modunda seçme sorgusu oluşturma Sorgu oluşturucu.

Hücrelerden birine dizeleri girin Seçim koşulu bir değer değil parametredir. Örneğin, kayıtları soyadına göre seçerseniz parametrik sorgu sahada Soyadı Seçim koşullarında köşeli parantez içinde [soyadı giriniz] ibaresi yazılabilir.

Menüden seç Rica etmek seçenek Seçenekler. İletişim kutusunda Parametreleri talep et sol sütuna, parametreyle eşleşen parametreyi köşeli parantez içine girin. istek formu Sorgu oluşturucu(parametre istek formundan kopyalanabilir). Sağ sütunda veri türünü belirtin: tuşuna basın listeden bir veri türü seçin.

Yürütme isteğini çalıştırın. Erişim pencereyi gösterecek Parametre değerini girinİstediğiniz parametre değerini girin ve düğmesine basın TAMAM.

5. Yürütme isteğini çalıştırın.

ÖRNEK:

Son kullanma tarihi belirtilen değeri aşan ürünlerin listesini seçin.

Pirinç. 18. Parametrik Sorgu Oluşturucu

Pirinç. 19. Ürünün son kullanma tarihinin girilmesi

Pirinç. 20. Parametrik sorgunun sonucu

7. Çapraz Sorgular Oluşturma

1. ile oluşturun Tasarımcı Yeni istek ve gerekli tabloları içine ekleyin.

2. Dinamik tablonun satır ve sütunlarını oluşturmak için değerleri kullanılacak alanları seçin ve bunları sorgu formuna sürükleyin.

3. Menüden bir komut seçin Sorgu/Çapraz. Başlık Tasarımcı itibaren değişecek Sorgu 1: örnek başına Açık Sorgu 1: Çapraz Sorgu . Talep formunda şu satır görüntülenecektir: Çapraz tablo .

4. Çapraz satır olacak alanlar için Satır açılır listesini açın Çapraz tablo seçeneği seç Satır başlıkları. Satır başlığı olan her alanın, satırda bir Grup işlemi ayarı olması gerekir Gruplandırma.

5. Çapraz tablo sütunları olacak alanlar için sorguya bağlı olarak aşağıdaki eylemler mümkündür:

Çizgide Çapraz tablo seçeneği seç Sütun başlıkları ve sırada Grup işlemi kurulum gruplama, veya sırada Grup işlemi – Durum, hatta Seçim koşulu Ve VEYA yaz mantıksal ifade.

6. Toplam değerin oluşturulacağı alanı ayarlayın. Çizgide Çapraz tablo seçmek Anlam.Çizgide Grup işlemi son işlevi seçin veya İfade.İkinci durumda hücrede Alan ifadesini yazınız.

7. Yürütme isteğini çalıştırın.

ÖRNEK:

Her üretici tarafından üretilen her türden sipariş edilen ürünlerin toplam miktarını seçin. Sonucu, satırların ürün türlerinin adları ve sütunların üreticilerin adları olduğu bir tablo biçiminde sunun.

Pirinç. 21. Çapraz Sorgu Oluşturucu

Pirinç. 22. Çapraz sorgunun sonucu

8.Tablo oluşturmak için sorgu

İlgili tablolardaki kayıtları seçmek için kullanılan sorgunun sonuçlarından tablo oluşturmak için şu adımları izleyin:

Tablolardan kayıt seçmek için sorgunun bir kopyasını oluşturun (veya yeni bir tane oluşturun).

Oluşturulan isteği modunda aç tasarımcı, veritabanı penceresindeki sorgular listesinde adını vurgulayıp düğmesine tıklayarak Oluşturucu.

Menüden bir komut seçin Sorgula/Tablo Oluştur.Bir iletişim kutusu açılacaktır Tablo oluşturma. Yeni tablonun adını girin, radyo düğmesini yerinde bırakın Mevcut veritabanında.

Düğmeye bas TAMAM. Seçme sorgusu tablo oluşturma sorgusuna dönüştürülür.

Veritabanı penceresindeki sorgu adına çift tıklayarak sorguyu çalıştırın. Sorgu çalıştırılmadan önce, yeni tabloda (henüz oluşturulmamış olsa bile) değişiklik yapılacağı konusunda sizi uyaran bir mesaj görüntülenir.

Düğmeye bas Evet. Sorgu sonucunda yeni tabloya yerleştirilecek kayıt sayısı hakkında ikinci bir uyarı mesajı görüntülenir. Düğmeye bas Evet.

Veritabanı penceresinde Tablolar sekmesini genişletin; Listede yeni bir tablo görünmelidir.

9.Kayıtları güncelleme isteği

Yeni bir sorgu oluşturun ve tablonun oluşturulan kopyasını buna ekleyin.

Menüden bir komut seçin Sorgulama/Güncelleme. Hat talep formunda Sıralama Ve Ekrandaki çıktı dizeyle değiştirilecek Güncelleme.

Çizgide Güncelleme geçerli alanın yeni değerini temsil eden bir ifade belirtin.

Bilgiler tablonun tüm kayıtlarında güncellenmiyor ancak yalnızca bir kısmında güncelleniyorsa, kayıtların seçilmesi için bir koşul girin.

İsteği tamamlayın. Kayıtların doğru şekilde güncellendiğini kontrol edin.

ÖRNEK:

Miktarı 500 birimi aşan ürünlerin maliyetini orijinal maliyetin %90'ına ayarlayarak güncelleyin.

Pirinç. 23. İstek yapıcısını güncelleyin

10.Kayıtların silinmesini talep etme

Kayıtların güncellenmesi gereken tablonun bir kopyasını oluşturun.

Yeni bir sorgu oluşturun ve buna uygun tabloyu ekleyin.

Değerleri alanları seçmek için kullanılacak alanları istek formuna sürükleyip bırakın. Çizgide Seçim koşulları seçim kriterlerini belirtin.

Silinecek kayıtları görüntülemek için bir seçme sorgusu çalıştırın.

Açık veritabanı penceresi. Bir sekme seçin Tablolar.

Oluşturulan seçim isteğini etkinleştirin ve moda gidin Yapıcı iste.

Menüden bir komut seçin Talep Et/Sil. Hat talep formunda Sıralama Ve Ekrandaki çıktı dizeyle değiştirilecek Kaldırma.

Düğmeye bas Öğle yemeği araç çubukları. Girişleri silmek için onay isteyen bir mesaj kutusu görüntülenir.

Tablodaki kayıtlar siliniyor” bir", bunun için “tabloda birçok" ilgili kayıtların bulunması veri bütünlüğü kuralını ihlal etmektedir. Bu nedenle tablolar arasındaki ilişkide ayarlanan veri bütünlüğü seçeneği kayıtların silinmesini engelleyebilir. Kademeli silme uygulanmalıdır.

ÖRNEK:

Pirinç. 24. İstek yapıcısını silin

11. Kayıt ekleme isteği

Ekleme sorgusu genellikle verileri bir tablodan diğerine kopyalamak için kullanılır.

Bir sekme seçin İstekler veritabanı penceresinde düğmeye tıklayın Yaratmak. Bir iletişim kutusu açılacaktır Yeni istek. Bir seçenek seçin Yapıcı.

Pencerede Tablo ekleme verilerin alınacağı tabloyu seçin.

Satıra taşı Alan Değerleri başka bir tabloda yer alacak alanlar için talep formu.

Menü komutlarını seçin Talep Et/Ekle. Bir pencere görünecek Ek, veri eklemeniz gereken tablonun adını ve hangi veritabanına ait olduğunu belirtmeniz gerekir. Veritabanı geçerli veritabanı değil de başka bir veritabanı ise, veritabanını içeren dosyanın tam adını belirtmeniz gerekecektir.

Satırdan sonraki istek formunda Sıralama satır dahil edilecek Ek, verilerin eklendiği tablo alanlarını belirtmeniz gerekir. Alan adları aynıysa Aİleson otomatik olarak satıra eklenecektir Ek alan adları.

Yürütme isteğini çalıştırın. Yeni satırların eklenmesiyle tablonun nasıl değiştiğini görün.

Eğer bir talepte bulunursanız Ek gibi alanlar var Tezgah, bu tür alanlarla iki şekilde çalışabilirsiniz:

tür alanlarını dahil etmeyin Tezgah Eklenmesi gereken alanlarda; daha sonra verilerin eklendiği tabloda veri dizisinin numaralandırılması devam edecektir, yani. 1'den 100'e kadar sayıların bulunduğu bir çalışan tablosu varsa ve ona ekleyin yeni girişler, numaralandırmaları 101 numaradan başlayacak;

tür alanlarını dahil et Tezgah istek listesine eklenen alanlar olarak eklenirse, orijinal tabloda sahip oldukları değerler eklenen kayıtların alanlarına kaydedilecektir; kayıtların ekleneceği tablonun alanlarında bu tür değerler zaten mevcutsa bu durum hataya neden olabilir. İstek yürütülmeden önce ekranda bir hata mesajı belirir. Bir hata varsa düğmeyi seçin İptal etmek ve hatayı düzeltin.

Yöntem 2: ΣValues ​​alanında istediğiniz alanın değerlerine sağ tıklayın ve şunu seçin:

Ayrıca önerilen listeden farklı bir işlev seçmek için alan formatını yapılandırın ve alan adını değiştirin.

Etkilerinden yararlanmak için. Bugün size öğreteceğiz. Çizmek istediğiniz hücreleri işaretleyin. Ders 3: Tablolar ve Özet Grafikler. Diyelim ki analiz edecek bir setimiz var. PivotTable kullanmak için çalışma sayfasında herhangi bir hücreyi seçin ve Ekle menüsünden PivotTable'ı seçin.

Daha sonra bilgiyi alabilirsiniz. Grafikler oluştururken tablonun kenarlık veya başka biçimlendirme öğelerine sahip olması önemli değildir. Formüllere ve Fonksiyonlara Giriş İster basit ister karmaşık olsun, hesaplamaları gerçekleştirmek sıkıcı ve zaman alıcı olabilir.

 Ek hesaplamalar ve ardından istediğiniz hesaplama seçeneğini seçin.

PivotTable raporunda orijinal alanı yeniden kullanma

Aynı kaynak alanı kullanarak birden fazla hesaplama yapabilirsiniz. Bunu yapmak için, pivot tablodaki alanlar listesinden orijinal alanı değer aralığına eklemeniz gerekir Σ Değerler [Σ

Değerler]'i birkaç kez seçin, ardından alan parametrelerini ayarlayın. Bu, hem hesaplamanın kendisini hem de buna göre yapılan hesaplamayı aynı anda görmenizi sağlayacaktır.

Hesaplanan alanları PivotTable'a ekleme

Formül eklemek veya ek hesaplamalar eklemek için PivotTable raporuna yeni satırlar veya sütunlar ekleyemezsiniz, ancak mevcut verilere dayalı olarak yeni verileri hesaplamak için formüller girmenize olanak tanıyan hesaplanmış bir alan ekleyebilirsiniz.

Veritabanları Çalışma Sayfası 1 Veritabanları Çalışma Sayfası 1 Tüm kurs verilerini depolamak için bir “Veritabanları” klasörü oluşturun. Başlıkları ve açıklamaları görüntüleyen bir sorgu oluşturur. Veritabanını Yönet iletişim kutusu görünür. Kullanıcı bu pencereyi kullanarak veritabanını ve tüm öğelerini yönetir.

Elektronik tablo nedir? Elektronik tablo, matematiksel hesaplamalar yapmak için kullanılan bir programdır. Onun yardımıyla formda okunaklı hesaplamalar da yapabiliriz. Belirli eylemlerden yararlanmak için. En emek yoğun kısım veri hazırlamadır. Problem 1 Matematik Grubu 2 Bu adımları sırasıyla takip edin. Masayı çalıştırın.

Hesaplanan alanı doğrudan PivotTable'a eklemek en iyi çözüm. Veri kaynağı büyüdükçe veya düzenlendikçe formülleri yönetmeye veya tablonun genişletilebilirliği konusunda endişelenmenize gerek kalmaz ve pivot tablo, alan tanımlarını değiştirecek kadar esnek kalır.

Bu yöntemin bir diğer büyük avantajı, PivotTable'ın yapısını değiştirebilmeniz ve hatta formül hataları veya bozuk hücre referansları konusunda endişelenmeden hesaplanan alanlar için diğer veri alanlarını hesaplayabilmenizdir.

Szczecin'deki Batı Pomeranya Teknoloji Üniversitesi Deniz Teknolojisi ve Ulaştırma Fakültesi Yapılar, Mekanik ve Teknoloji Bölümü operasyonel araştırma laboratuvarında gemi. Pivot tablolar verileri hızlı bir şekilde analiz etmek için kullanılır. Pivot tabloları kullanabilmek için verilerin başlıklarla uygun şekilde hazırlanmış olması gerekir.

Bu gibi durumlarla karşılaşmamak için verilerimizi tablo halinde formatlamalısınız. Verileri tablo olarak biçimlendirmek için. Bu, tüm başlıkları seçecektir. . Bir sonraki seçenek pivot tablonun konumudur ve verilerin okunabilirliğini korumak için eklenecek Yeni Sayfa seçeneğini bırakmalısınız. yeni yaprak masaya.

Microsoft Excel 2010. Düzey 2. Gelişmiş özellikler

Dolayısıyla, kendi hesaplanan alanlarınızı PivotTable'a eklemenin faydaları açıktır:

 Formüllerdeki ve hücre referanslarındaki olası hataları ortadan kaldırın.

 Orijinal hesaplamaları değiştirmeden pivot tabloya veri ekleme ve çıkarma yeteneği.

Boş olan yeni bir PivotTable sayfası var. Sağ tarafta tablomuzdaki tüm başlıkların yer aldığı alanların listesi ve alanlarıyla birlikte bir plan bulunmaktadır. Diyelim ki en çok satanın kim olduğunu görmek istiyoruz. Bu durumda Satıcı alanını ve Tutar alanını seçin çünkü satıştan sonra kimin en iyi olduğunu kontrol edeceğiz. Rapor bu şekilde görünecek.

Varsayılan sütundur. Pivot tablolar kolayca değiştirilebilir ve en çok satan ülkeyi kontrol etmek istiyorsanız satın alma işlemini iptal edip satıcıyla işaretlemeniz yeterlidir. Hemen sonucu okuyabileceğiniz yeni bir rapor alıyoruz.

 Bir pivot tabloyu değiştirirken veya güncellerken verileri otomatik olarak yeniden hesaplama yeteneği.

 Eleman tanımları değiştiğinde hesaplamaları değiştirme esnekliği sağlar.

 Hesaplamaları etkili bir şekilde yönetme becerisi.

Hesaplanan bir alan oluşturun:

1. Bir pivot tablo hücresi seçin;

Bu kutuyu işaretleyerek Satıcıya bir alt kategori de ekleyebiliriz. Satış raporu, gösterdiği değerleri otomatik olarak özetler ancak bu toplamlar çoğu zaman analizi zorlaştırır. Ayrıca toplamdan kurtulup rapor düzenini de değiştirebiliriz.

Raporu hızlı bir şekilde değiştirmek ve Dobrovalsky'yi diğer ülkelere nasıl sattığını görmek için, Satıcıyı yakalayıp eyalete sürükleyerek tarlalardaki koşulların sırasını değiştirmeniz yeterlidir. İrlanda ve Almanya dışında satış sonuçlarının pek de iyi olmadığı açık. İrlanda'da sat'a tıkladığınızda Dobrovalsky'nin İrlanda'daki tüm işlemlerini içeren bir tablo içeren yeni bir sayfa göreceksiniz.

2. Seçenekler sekmesinin Hesaplamalar grubunda Alanlar listesini genişletin.

elemanlar ve kümeler ve seçin Hesaplanan alan.

3. Pencerede Hesaplanan alan ekleme:

 Ad alanına yeni alanın adını girin.

 Formül alanında = işaretiyle başlayıp aşağıdakileri seçerek bir formül oluşturun:

Alanlar listesindeki mevcut alanlar (eklemek için bir alanı çift tıklayabilir veya bir alan seçip Alan Ekle'yi tıklayabilirsiniz).

Detaylarımızı yazdırmak istiyorsak Satırlar alanından Satış Elemanı alanını alıp Sütun alanına taşıyacağız. Bu, tablonun tüm sayfaya yayılmasına neden olacaktır. Bu pencerede alanın tüm hücrelerinde görüntülenecek sayı biçimini de belirleyebiliriz. Örneğin "Sayı Formatı" düğmesini kullanın ve uygun para birimini seçin.

Filtreler de faydalıdır. Bu alana eklendiğinde, bir veya daha fazla seçeneği seçebileceğiniz açılır listenin sol üst köşesinde tedarikçi alanları görünür. Bakalım en iyi satış hangi ay oldu? Aşağıda verileri gruplamak istediğimiz kategoriyi seçiyoruz.

 Ekle'ye tıklayın.

Hesaplanan alanın görünümünün ayarlanması, diğer alanlar gibi pencerede gerçekleşir

Değer alanı parametreleri.

Hesaplanan alanı düzenleme

1. Seçenekler sekmesinin Hesaplamalar grubunda Alanlar listesini genişletin.

elemanlar ve kümeler, seçin Hesaplanan alan.

Pivot tabloların nasıl kullanılacağını hepimiz biliyoruz. Pek çok avantajları var ama aynı zamanda pek çok dezavantajları da var. Birçok kişi pivot tablolarla ilgili asıl sorunun hesaplama yapmanın bir yolu olmaması olduğuna inanıyor; bu bir yanılgıdır. Bu neden gerekli olabilir? Pivot tabloları kullanarak bir bütçe oluşturduğunuzu varsayalım. Orijinal tablodaki veriler üçer aylık dönemler itibarıyla sunulmaktadır, bütçe amacıyla bu verilerin yıllık tutara eklenmesi gerekecektir.

Ayları kutluyoruz ve onları kucaklıyoruz. Aylara göre sıralanmış bir rapor alacağız. Ayrıca verileri birkaç kategoriye ayırabilirsiniz. Yapılandırıcıların gruplandırılmasına dönüp "Çeyrek ve ay" seçeneğini seçerek raporun görünümünü alıyoruz. Gruplandırma aynı zamanda sayılar için de kullanılabilir.

Açtığınızda içerik menüsü, Satırlar alanı ve Grup seçeneği, tarihleri ​​gruplandırdığınızdan farklı bir pencere gösterir. Sayılar, aralığın başlangıcına, sonuna ve hacmine göre tanımladığımız aralıklar halinde gruplandırılmıştır. Bu durumda bunu 500 olarak ayarladık, böylece ilk grup 0 ile 500 zloti arasındaki tüm işlemleri içerecek.

Aslında böyle bir ihtimal var ve üç yoldan biriyle gerçekleşiyor.

Pivot tabloların temelleri hakkındaki bilgilerinizi tazeleyebilirsiniz.

İlk yol. Orijinal verilerle bir tabloda hesaplamalar yapın. Açıkçası yöntem, yanlış yerden geçen bir çeşme değildir.

İkinci yol. Hesaplamaları bir pivot tablonun yanında oluşturmak yine en iyi yöntem değildir. Pivot tablo değişirse (ve tam olarak tabloda değişiklik beklediğimizde pivot tabloları kullanmaya değer).

Üçüncü yol. Hesaplanan alanları pivot tablonun kendisine gömün. Ama bu iyi bir yol. Bu şu şekilde yapılır. Pivot tablo zaten oluşturulduğunda, imleci pivot tablonun üzerine getirin (şeritte bu pivot tabloyla ilgili iki sekme görünür) ve görünen sekmeye tıklayın Seçenekler, Daha sonra Alanlar,Öğeler, &Setler Hesaplamalar bloğundan açılır menüden seçim yapın Hesaplanmışalan. Office 2007'de bu düğme aynı Seçenekler sekmesindeydi, yalnızca düğme ayrıydı.

Açılan pencerede, alanda İsimİstediğiniz alan adını girin. Tarlada Formül Aşağıdaki listeden alanları ekleyip butonuna tıklayarak ihtiyacınız olan formülü girin Sokmakalan.

Yeni bir hesaplama alanına sahip bir tablo alacaksınız. Masa hazır!

Excel dosyasını buradan indirebilirsiniz.

İlginç gerçek.Üçüncü yöntemi kullanırken, program aslında her satır için hesaplamayı yapar ve ardından gerekli parametreyi uygular: toplam, ortalama vb.

Son haberler

• Gelişmiş Excel: V işlevini kullanmayı neden bıraktım?

  DÜŞEYARA fonksiyonunun (DÜŞEYARA) muhtemelen basit aritmetik işlemlerden sonra en kullanışlı fonksiyon olduğunu yazmıştım...

• Excel bir çalışma kitabını nasıl yeniden hesaplar ve değişkenlikten neden kaçınılmalıdır?

  Birbirine bağlı çok sayıda hesaplama gerçekleştiren büyük dosyalarla sık sık çalışıyorsanız...

• Hızlı bir şekilde Şelale grafiği nasıl oluşturulur?

  Excel'de standart Şelale grafikleri yoktur. Bu nedenle, bu tür diyagramları oluşturmak için genellikle gis kullanılır...

• Sonuçları izlemek için kontrol penceresi

  Sonuçta 1-2 göstergeyi hesaplayan büyük tablolarla çalıştığımız sıklıkla olur, ancak...

• Planlanan göstergeler ile gerçekleşen göstergeleri karşılaştırmak için madde işareti grafiği...

  Çoğu zaman ana mali ve ekonomik göstergeleri planlananlarla karşılaştırmaya ihtiyaç vardır, örneğin...

• Tatsuo Horiuchi, Excel'de çizim yapan 73 yaşında bir sanatçı.

  "İşyerinde Excel'i hiç kullanmadım ama başka insanların Excel'de oldukça güzel çizelgeler ve grafikler hazırladığını gördüm...

• Bir sayının üssü nasıl yükseltilir ve kökü nasıl çıkarılır

  Excel'de bir sayının üssünü yükseltmek için bir yöntem olduğunu biliyor muydunuz? özel karakter^ (İngilizce'de altıda...

Hesaplanan alanlar, raporlarda kaynak verilere dayalı olarak ifadelerin değerlerini görüntülemek için tasarlanmıştır. Raporlardaki hesaplanan alanlar, toplamları elde etmek veya özel hesaplamalar yapmak için kullanılabilir; örneğin, bir kalemin miktarı ile bir kalemin fiyatının çarpımını hesaplayarak bir siparişin toplam fiyatını görüntülemek için kullanılabilir. Daha önce oluşturulan "Depodaki Öğeler" raporu örneğini kullanarak hesaplanan alanlar oluşturma prosedürüne bakalım.

Bir rapora hesaplanmış bir "Tutar" alanı eklemek için:

1. Depodaki Öğeler raporunu Tasarım modunda açın.
2. Araç Kutusu'nda düğmeye tıklayın Yazıt(Etiket) ve yeni metni başlık bölümündeki "Stokta Olan Birimler"in sağına yerleştirin. Etiket metni olarak "Toplam" kelimesini girin.
3. Hesaplanan toplam alanı oluşturmak için düğmeye tıklayın Alan(Metin Kutusu) öğesini seçin ve veri bölümündeki UnitsInStock metin kutusunun sağına bir metin kutusu ekleyin. Yeni metin alanının etiketini kaldırın.
4. Metin alanının içine tıklayın ve değer olarak aşağıdaki ifadeyi girin:

   = [Stokta Mevcut]* [Fiyat] (= * ).

   Özellikler penceresini aç alanlar üzerine çift tıklayarak ve sekmede Düzen(Biçim) özelliği açılır kutusunda Alan Formatı(Biçim) bir değer seçin Parasal(Para birimi).

Yorum

Metin alanına bir ifade girmenin birkaç yolu vardır: onu seçin ve tuş kombinasyonuna basın +alanın içeriği için düzenleme moduna girmek veya bir iletişim kutusu görüntülemek için alana çift tıklayın Özellikler(Özellikler) ve ardından ifadeyi özellik değeri olarak girin Veri(Kaynak).

Pirinç. 6.27.

1. Kategori grubunun yorumlar bölümünde bir metin alanı oluşturmak için 3. ve 4. adımları tekrarlayın, ancak alt toplam ifadesi olarak aşağıdakini girin: =Sum([InStock] * [Fiyat]) (=Sum( * )). Düğmeye bas Gözü pek Yeni alandaki metni kalın olarak ayarlamak için biçim çubuğundaki (Kalın) seçeneğini seçin. İletişim kutusunu aç Özellikler(Özellikler) ve içindeki sekmeyi açın Diğer(Diğer), sahada İsim(Ad) Toplam metin kutusu için bir ad girin. Ardından sekmeyi genişletin Düzen(Biçim) ve özellikler açılır kutusunda Alan Formatı(Biçim) bir değer seçin Parasal(Para birimi).
2. Raporumuzun veri alanının genişliği arttığından, tüm alanların tek sayfaya sığması için sayfa kenar boşluklarının genişliğini azaltın. Bunu yapmak için komutu seçin Dosya, Sayfa Yapısı(Dosya, Sayfa Yapısı) ve varsayılan sol ve sağ kenar boşluğu genişliklerini 10 mm olarak değiştirin (Bu bölümdeki "Raporları Görüntüleme ve Yazdırma" konusuna bakın.)
3. Düğmeye bas Görüş(Görünüm) araç çubuğunda Rapor tasarımcısı(Rapor Tasarımı) çalışmanızın sonuçlarını değerlendirmek için. Rapor Şekil 2'de gösterildiği gibi görünecektir. 6.27. Önceki verilere, depodaki mevcut miktardaki her ürünün maliyetinin değerleri ve her kategori için mal maliyetinin alt toplamları ile en sağdaki sütun eklendi.