Raporlama ve veri işlemeyle ilgilenenler için Excel yaşam tüyoları

2024 Yazar: Malcolm Clapton | [email protected]. Son düzenleme: 2024-01-13 03:17

Bu gönderide, Mann, Ivanov ve Ferber Yayınevi'nin Genel Müdür Yardımcısı Renat Shagabutdinov, bazı harika Excel yaşam tüyolarını paylaşıyor. Bu ipuçları, çeşitli raporlama, veri işleme ve sunum oluşturma ile ilgilenen herkes için faydalı olacaktır.

Bu makale, Excel'deki çalışmanızı basitleştirmek için basit teknikler içermektedir. Özellikle yönetim raporlamasıyla uğraşanlar, 1C'den indirmelere ve diğer raporlara dayalı çeşitli analitik raporlar hazırlayanlar, yönetim için sunumlar ve diyagramlar oluşturanlar için faydalıdırlar. Mutlak bir yenilik gibi davranmıyorum - şu veya bu şekilde, bu teknikler muhtemelen forumlarda tartışıldı veya makalelerde bahsedildi.

DÜŞEYARA ve YATAYARA'ya basit alternatifler, istenen değerler tablonun ilk sütununda değilse: ARA, İNDEKS + ARAMA

DÜŞEYARA ve YATAYARA işlevleri yalnızca istenen değerler, veri almayı planladığınız tablonun ilk sütununda veya satırındaysa çalışır.

Aksi takdirde, iki seçenek vardır:

1. ARA işlevini kullanın.

   Aşağıdaki sözdizimine sahiptir: LOOKUP (arama_değeri; arama_vektörü; sonuç_vektörü). Ancak düzgün çalışması için view_vector aralığının değerleri artan düzende sıralanmalıdır:

   

2. KAÇINCI ve İNDEKS işlevlerinin bir kombinasyonunu kullanın.

   KAÇINCI işlevi, dizideki bir öğenin sıra sayısını döndürür (yardımıyla, aranan öğenin tablonun hangi satırında olduğunu bulabilirsiniz) ve INDEX işlevi, belirli bir sayıya sahip bir dizi öğesi döndürür (ki bunu bulacağız). KAÇINCI işlevini kullanarak).

   

   İşlev sözdizimi:

   • ARAMA (arama_değeri; arama_dizisi; eşleşme_türü) - bizim durumumuz için "tam eşleşme" eşleşen bir türe ihtiyacımız var, bu 0 sayısına karşılık geliyor.

   • INDEX (dizi; satır_numarası; [sütun_numarası]). Bu durumda dizi tek satırdan oluştuğu için sütun numarasını belirtmenize gerek yoktur.

Listedeki boş hücreler nasıl hızlı bir şekilde doldurulur

Görev, sütundaki hücreleri en üstteki değerlerle doldurmaktır (böylece konu tablonun her satırındadır ve yalnızca konuyla ilgili kitap bloğunun ilk satırında değil):

"Konu" sütununu seçin, "Ana Sayfa" grubundaki şeride tıklayın, "Bul ve Seç" düğmesi → "Bir hücre grubu seçin" → "Boş hücreler" ve formülü girmeye başlayın (yani, eşit işareti) ve klavyenizdeki yukarı oka tıklayarak en üstteki hücreye bakın. Bundan sonra Ctrl + Enter tuşlarına basın. Bundan sonra, formüllere artık ihtiyaç duyulmadığından, alınan verileri değerler olarak kaydedebilirsiniz:

Bir formülde hatalar nasıl bulunur

Bir formülün ayrı bir bölümünün hesaplanması

Karmaşık bir formülü (diğer işlevlerin işlev argümanları olarak kullanıldığı, yani bazı işlevlerin diğerlerinde iç içe geçtiği) anlamak veya içindeki hataların kaynağını bulmak için genellikle bunun bir kısmını hesaplamanız gerekir. İki kolay yol vardır:

1. Formül çubuğunda formülün bir kısmını hesaplamak için o kısmı seçin ve F9 tuşuna basın:

   

   Bu örnekte, SEARCH işleviyle ilgili bir sorun vardı - içindeki argümanlar değiştirildi. İşlev bölümünün hesaplamasını iptal etmez ve Enter tuşuna basarsanız, hesaplanan bölümün bir sayı olarak kalacağını hatırlamak önemlidir.

2. Şeritteki Formüller grubundaki Formülü Hesapla düğmesini tıklayın:

   

   Açılan pencerede formülü adım adım hesaplayabilir ve (varsa) hangi aşamada ve hangi fonksiyonda hata oluştuğunu belirleyebilirsiniz:

   

Bir formülün neye bağlı olduğu veya neye atıfta bulunduğu nasıl belirlenir

Bir formülün hangi hücrelere bağlı olduğunu belirlemek için şeritteki Formüller grubunda Hücreleri Etkileyen düğmesini tıklayın:

Hesaplama sonucunun neye bağlı olduğunu gösteren oklar görünür.

Resimde kırmızıyla vurgulanan sembol görüntüleniyorsa, formül diğer sayfalardaki veya diğer kitaplardaki hücrelere bağlıdır:

Üzerine tıklayarak, etkileyen hücrelerin veya aralıkların tam olarak nerede olduğunu görebiliriz:

"Hücreleri Etkilemek" düğmesinin yanında, aynı şekilde çalışan "Bağımlı Hücreler" düğmesi bulunur: etkin hücreden, ona bağlı olan hücrelere bir formülle oklar görüntüler.

Aynı blokta bulunan "Okları kaldır" düğmesi, etkileyen hücrelere giden okları, bağımlı hücrelere giden okları veya her iki ok türünü aynı anda kaldırmanıza olanak tanır:

Birden çok sayfadan hücre değerlerinin toplamı (sayı, ortalama) nasıl bulunur?

Diyelim ki, eklemek, saymak veya başka bir şekilde işlemek istediğiniz verilerle aynı türde birkaç sayfanız var:

Bunu yapmak için, sonucu görmek istediğiniz hücreye standart bir formül girin, örneğin SUM (SUM) ve işlemeniz gereken sayfalar listesinden ilk ve son sayfaların adını belirtin. iki nokta üst üste ile ayrılmış argüman:

"Data1", "Data2", "Data3" sayfalarından B3 adresli hücrelerin toplamını alacaksınız:

Bu adresleme, bulunan sayfalar için çalışır. sürekli … Sözdizimi aşağıdaki gibidir: = FUNCTION (first_list: last_list! Aralık referansı).

Şablon ifadeleri otomatik olarak nasıl oluşturulur

Excel'de metinle çalışmanın temel ilkelerini ve birkaç basit işlevi kullanarak raporlar için şablon ifadeler hazırlayabilirsiniz. Metinle çalışmanın birkaç ilkesi:

• Metni & işaretini kullanarak birleştiriyoruz (bunu BİRLEŞTİR işleviyle değiştirebilirsiniz, ancak bu pek mantıklı değil).
• Metin her zaman tırnak içinde yazılır, metin içeren hücrelere yapılan referanslar her zaman yoktur.
• "Tırnak işaretleri" hizmet karakterini almak için, 32 argümanıyla CHAR işlevini kullanın.

Formülleri kullanarak bir şablon cümlesi oluşturmaya bir örnek:

Sonuç:

Bu durumda CHAR işlevine (tırnakları görüntülemek için) ek olarak, olumlu bir satış trendi olup olmadığına bağlı olarak metni değiştirmenizi sağlayan EĞER işlevi ve METİN işlevi kullanılır. herhangi bir biçimde sayı. Sözdizimi aşağıda açıklanmıştır:

METİN (değer; biçim)

Biçim, Hücreleri Biçimlendir penceresine özel bir biçim giriyormuşsunuz gibi, tırnak işaretleri içinde belirtilir.

Daha karmaşık metinler de otomatikleştirilebilir. Uygulamamda, “GÖSTERGE XX tarafından plana göre düştü / yükseldi, esas olarak FAKTÖR1'in XX tarafından büyümesi / azalması nedeniyle, FAKTÖR2'nin büyümesi / azalması nedeniyle, yönetim raporlamasına uzun ama rutin yorumların otomasyonu vardı. YY …” değişen bir faktör listesi ile. Bu tür yorumları sık sık yazıyorsanız ve bunları yazma süreci algoritmalaştırılabiliyorsa, sizi en azından işin bir kısmından kurtaracak bir formül veya makro oluşturmak için bir kez kafa karıştırmakta fayda var.

Birleştirmeden sonra her hücrede veri nasıl saklanır

Hücreleri birleştirdiğinizde yalnızca bir değer korunur. Excel, hücreleri birleştirmeye çalışırken bu konuda uyarır:

Buna göre, her hücreye bağlı bir formülünüz varsa, bunları birleştirdikten sonra çalışmayı durduracaktır (örneğin, 3-4 satırlarında # N / A hatası):

Hücreleri birleştirmek ve her birindeki verileri korumaya devam etmek için (belki bu soyut örnekteki gibi bir formülünüz vardır; belki hücreleri birleştirmek istiyorsunuz, ancak tüm verileri gelecek için saklamak veya kasıtlı olarak gizlemek istiyorsunuz), sayfadaki herhangi bir hücreyi birleştirin, onları seçin ve ardından biçimlendirmeyi birleştirmeniz gereken hücrelere aktarmak için Biçim Boyacısı komutunu kullanın:

Birden çok veri kaynağından bir pivot nasıl oluşturulur

Aynı anda birkaç veri kaynağından bir özet oluşturmanız gerekiyorsa, böyle bir seçeneğe sahip olan şerit veya hızlı erişim paneline "Özet Tablo ve Grafik Sihirbazı" eklemeniz gerekecektir.

Bunu şu şekilde yapabilirsiniz: "Dosya" → "Seçenekler" → "Hızlı Erişim Araç Çubuğu" → "Tüm Komutlar" → "Özet Tablo ve Grafik Sihirbazı" → "Ekle":

Bundan sonra, aynı sihirbazı çağıran üzerine tıklayarak şeritte ilgili bir simge görünecektir:

Üzerine tıkladığınızda bir iletişim kutusu belirir:

İçinde "Birkaç konsolidasyon aralığında" öğesini seçmeniz ve "İleri" ye tıklamanız gerekir. Bir sonraki adımda, "Bir sayfa alanı oluştur" veya "Sayfa alanları oluştur"u seçebilirsiniz. Veri kaynaklarının her biri için bağımsız olarak bir ad bulmak istiyorsanız, ikinci öğeyi seçin:

Bir sonraki pencerede, pivotun oluşturulacağı tüm aralıkları ekleyin ve onlara ad verin:

Bundan sonra, son iletişim kutusunda, pivot tablo raporunun mevcut veya yeni bir sayfada nereye yerleştirileceğini belirtin:

Pivot tablo raporu hazır. "Sayfa 1" filtresinde, gerekirse veri kaynaklarından yalnızca birini seçebilirsiniz:

Metin B'de A metninin oluşum sayısı nasıl hesaplanır ("MTS SuperMTS tarifesi" - MTS kısaltmasının iki tekrarı)

Bu örnekte, A sütunu birkaç metin satırı içeriyor ve görevimiz, her birinin E1 hücresinde bulunan arama metnini kaç kez içerdiğini bulmak:

Bu sorunu çözmek için aşağıdaki işlevlerden oluşan karmaşık bir formül kullanabilirsiniz:

1. DLSTR (LEN) - metnin uzunluğunu hesaplar, tek argüman metindir. Örnek: DLSTR ("makine") = 6.
2. SUBSTITUTE - bir metin dizesindeki belirli bir metni başka bir metinle değiştirir. Sözdizimi: SUBSTITUTE (metin; eski_metin; yeni_metin). Örnek: SUBSTITUTE (“araba”; “oto”; “”) = “mobil”.
3. BÜYÜK - bir dizedeki tüm karakterleri büyük harfle değiştirir. Tek argüman metindir. Örnek: UPPER (“makine”) = “CAR”. Büyük/küçük harfe duyarsız aramalar yapmak için bu fonksiyona ihtiyacımız var. Sonuçta, ÜST ("araba") = YUKARI ("Makine")

Belirli bir metin dizesinin bir diğerinde oluşumunu bulmak için, orijinal metindeki tüm oluşumlarını silmeniz ve elde edilen dizenin uzunluğunu orijinal dizeyle karşılaştırmanız gerekir:

DLSTR (“Tarife MTS Süper MTS”) - DLSTR (“Süper Tarife”) = 6

Ve sonra bu farkı aradığımız dizenin uzunluğuna bölün:

6 / DLSTR (“MTS”) = 2

"MTS" satırının orijinaline dahil edilmesinin tam iki katıdır.

Geriye bu algoritmayı formüller dilinde yazmak kalıyor (içinde oluşumları aradığımız metni “metin” ve “arama” ile - oluşum sayısıyla ilgilendiğimiz metni gösterelim):

= (DLSTR (metin) -LSTR (YEDEK (BÜYÜK (metin); YUKARI (arama), ""))) / DLSTR (arama)

Örneğimizde formül şöyle görünür:

= (DLSTR (A2) -LSTR (YEDEK (ÜST (A2), ÜST ($ E $ 1), “”))) / DLSTR ($ E $ 1)