Microsoft Excel'de formül hesaplamayla ilgili sorunlar. Excel'de çalışma sayfasının sayfa modu. Yorumlardaki bilgiler

Excel'in en çok istenen özelliklerinden biri formüllerle çalışmaktır. Bu fonksiyon sayesinde program bağımsız olarak tablolarda çeşitli hesaplamalar yapar. Ancak bazen kullanıcı bir hücreye bir formül girer, ancak doğrudan amacını yerine getirmez - sonucu hesaplamak. Bunun neyle ilgili olabileceğini ve bu sorunun nasıl çözüleceğini bulalım.

Excel'de formüllerin hesaplanmasıyla ilgili sorunların nedenleri tamamen farklı olabilir. Hem belirli bir kitabın ayarlarından hem de ayrı bir hücre aralığından veya sözdizimindeki çeşitli hatalardan kaynaklanabilirler.

Yöntem 1: hücrelerin biçimini değiştirin

Excel'in hiç hesaplamamasının veya formülleri doğru hesaplamamasının en yaygın nedenlerinden biri, yanlış ayarlanmış hücre biçimidir. aralığı varsa Metin formatı, o zaman içindeki ifadelerin hesaplanması hiç yapılmaz, yani sıradan metin olarak görüntülenirler. Diğer durumlarda, biçim hesaplanan verilerin özüne uygun değilse, hücrede görüntülenen sonuç yanlış görüntülenebilir. Bu sorunu nasıl çözeceğimizi öğrenelim.



Şimdi formül, belirtilen hücrede sonucun çıktısı ile standart sırayla hesaplanacaktır.


Yöntem 2: "Formülleri göster" modunu kapatın

Ama belki de hesaplama sonuçları yerine ifadeleri göstermenizin nedeni, programın modu etkinleştirmiş olmasıdır. "Formülleri göster".




Yöntem 3: bir sözdizimi hatasını düzeltin

Formül, sözdiziminde hatalar yapılmışsa, örneğin bir harf eksikse veya değiştirilmişse metin olarak da görüntülenebilir. Manuel olarak girdiyseniz ve aracılığıyla değil İşlev sihirbazı, o zaman bu oldukça olasıdır. Bir ifadeyi metin olarak görüntülerken çok yaygın bir hata, işaretten önce bir boşluk bulunmasıdır. «=» .


Bu gibi durumlarda hatalı görüntülenen bu formüllerin söz dizimlerini dikkatli bir şekilde gözden geçirmeli ve gerekli düzeltmeleri yapmalısınız.

Yöntem 4: formül yeniden hesaplamayı etkinleştir

Ayrıca formülün değeri gösteriyor gibi göründüğü, ancak onunla ilişkili hücreler değiştiğinde kendini değiştirmediği, yani sonuç yeniden hesaplanmadığı da oluyor. Bu, bu kitaptaki hesaplama parametrelerini yanlış yapılandırdığınız anlamına gelir.




Bu çalışma kitabındaki tüm ifadeler, ilişkili herhangi bir değer değiştiğinde artık otomatik olarak yeniden hesaplanacaktır.

Yöntem 5: formülde hata

Program hala hesaplıyorsa, ancak sonuç olarak bir hata gösteriyorsa, kullanıcının ifadeyi girerken bir hata yapması muhtemeldir. Hatalı formüller, hücrede aşağıdaki değerlerin hangilerinin görüneceğini hesaplarken olanlardır:

  • #SAYI!;
  • #DEĞER !;
  • # BOŞ !;
  • # BÖL / 0 !;
  • # Yok

Bu durumda, ifadenin başvurduğu hücrelerdeki verilerin doğru yazılıp yazılmadığını, herhangi bir sözdizimi hatası olup olmadığını veya formülün kendisinde herhangi bir yanlış işlem (örneğin 0'a bölme) içerip içermediğini kontrol etmeniz gerekir.


İşlev, çok sayıda bağlantılı hücre ile karmaşıksa, özel bir araç kullanarak hesaplamaları izlemek daha kolaydır.




Gördüğünüz gibi, Excel'in formülleri dikkate almamasının veya doğru hesaplamamasının nedenleri tamamen farklı olabilir. Kullanıcı, hesaplamak yerine işlevin kendisini görüntülerse, bu durumda, büyük olasılıkla, hücre metin için biçimlendirilir veya ifade görünümü modu açılır. Ayrıca, bir sözdizimi hatası mümkündür (örneğin, işaretten önce bir boşluk bulunması «=» ). Bağlantılı hücrelerdeki verileri değiştirdikten sonra sonuç güncellenmezse, kitap parametrelerinde otomatik güncellemenin nasıl yapılandırıldığını görmeniz gerekir. Ayrıca, oldukça sık, doğru sonuç yerine hücrede bir hata görüntülenir. Burada fonksiyonun atıfta bulunduğu tüm değerleri görüntülemeniz gerekiyor. Bir hata bulunursa, ortadan kaldırılmalıdır.

Lifehacker okuyucuları zaten aşinadır Denis Batyanov kim bizimle paylaştı. Bugün Denis, genellikle kendimiz için yarattığımız Excel ile ilgili en yaygın sorunlardan nasıl kaçınılacağı hakkında konuşacak.

Makale materyalinin yeni başlayan Excel kullanıcıları için tasarlandığını hemen ayırtacağım. Deneyimli kullanıcılar bu tırmıkta bir kereden fazla kışkırtıcı bir şekilde dans ettiler, bu yüzden benim görevim genç ve deneyimsiz "dansçıları" bundan korumak.

Tablo sütunlarına başlık vermiyorsunuz

Sıralama, filtreleme, akıllı tablolar, pivot tablolar gibi birçok Excel aracı, verilerinizin sütun başlıkları içerdiğini varsayar. Aksi takdirde, onları ya hiç kullanamazsınız ya da tam olarak düzgün çalışmazlar. Tablolarınızın her zaman sütun başlıkları içerdiğinden emin olun.

Tablolarınızın içindeki boş sütunlar ve satırlar

Bu, Excel için kafa karıştırıcıdır. Tablonuzun içinde boş bir satır veya sütunla karşılaştığında, bir değil 2 tablonuz olduğunu düşünmeye başlar. Sürekli düzeltmeniz gerekecek. Ayrıca ihtiyacınız olmayan satırları/sütunları tablonun içinde saklamayın, silmek daha iyidir.

Bir sayfada birkaç tablo bulunur

Bunlar referans değerleri içeren küçük tablolar değilse, bunu yapmamalısınız.

Yaprak başına birden fazla masa ile tam olarak çalışmanız elverişsiz olacaktır. Örneğin, bir tablo solda ve ikincisi sağdaysa, bir tablodaki filtreleme diğerini etkiler. Tablolar alt alta yerleştirilmişse, alanların sabitlenmesi mümkün değildir ve tablolardan birinin üzerinde bir tablo imleci ile durmak için sürekli araması ve gereksiz manipülasyonlar yapması gerekecektir. Ona ihtiyacın var mı?

Aynı türden veriler yapay olarak farklı sütunlarda düzenlenir

Çoğu zaman, Excel'i oldukça yüzeysel olarak bilen kullanıcılar bu tablo biçimini tercih eder:

Acentelerin satışları ve cezaları hakkında bilgi toplamak için zararsız bir formatla karşı karşıyayız gibi görünüyor. Böyle bir masa düzeni, kompakt olduğu için bir kişi tarafından görsel olarak iyi algılanır. Ancak, inanın bana, bu tür tablolardan veri çıkarmaya ve alt toplamları (toplu bilgiler) almaya çalışmak gerçek bir kabustur.

Gerçek şu ki, bu format 2 boyut içeriyor: böylece bir şube, bir grup ve bir temsilci ayırarak hatta karar vermelisiniz. Doğru stoğu bulduğunuzda, burada birçoğu olduğu için gerekli sütunu aramanız gerekecektir. Ve bu "iki boyutluluk", standart Excel araçları - formüller ve pivot tablolar için böyle bir tabloyla çalışmayı büyük ölçüde karmaşıklaştırır.

Bir pivot tablo oluşturursanız, göstergeler farklı alanlara bölündüğünden, verileri yıl veya çeyrek bazında kolayca almanın bir yolunun olmadığını göreceksiniz. Kolayca manipüle edilebilecek bir satış alanınız yok, ancak 12 ayrı alan var. Her ne kadar hepsi tek bir sütunda olsa da, çeyrekler ve yıllar için elle ayrı hesaplanmış alanlar oluşturmanız gerekecek, Pivot tablo senin için yapardı.

SUMIF, SUMIFS, SUMPRODUCT gibi standart toplama formüllerini uygulamak isterseniz, bu tablo düzeni ile verimli çalışmadıklarını da göreceksiniz.

"Kolaylık için" kitabın farklı sayfalarında bilgi dağılımı

Bir başka yaygın hata, bir tür standart tablo formatına sahip olmak ve bu verilere dayalı analizlere ihtiyaç duymak, onu Excel çalışma kitabının ayrı sayfalarına dağıtmaktır. Örneğin, genellikle her ay veya yıl için ayrı sayfalar oluştururlar. Sonuç olarak, veri analizi çalışmasının miktarı aslında oluşturulan sayfa sayısıyla çarpılır. Bunu yapma. TEK sayfada bilgi toplayın.

Yorumlardaki bilgiler

Genellikle kullanıcılar ekler önemli bilgi hücre yorumunda ihtiyaç duyabilirler. Unutmayın ki yorumlarda ne var, sadece bakabilirsiniz (eğer bulursanız). Onu hücreye çekmek zordur. Yorumlar için ayrı bir sütuna sahip olmanızı tavsiye ederim.

Biçimlendirmeyle ilgili bir karışıklık

Kesinlikle sofranıza iyi bir şey katmaz. Bu, e-tablolarınızı kullanan insanlara itici geliyor. En iyi ihtimalle, buna önem vermeyecekler, en kötüsü - iş dünyasında organize ve özensiz olmadığınızı düşünecekler. Aşağıdakiler için çaba gösterin:

Hücreleri birleştirme

Hücre birleştirmeyi yalnızca onsuz yapamayacağınız durumlarda kullanın. Birleştirilmiş hücreler, düştükleri aralıkları manipüle etmeyi çok zorlaştırır. Hücreleri taşırken, hücre yerleştirirken vb. sorunlar oluşur.

Metin ve sayıları bir hücrede birleştirme

Arkada "RUB" metin sabiti ile doldurulmuş bir sayı içeren bir hücre tarafından acı verici bir izlenim üretilir. veya "USD" manuel olarak girilir. Özellikle basılı bir form değil, normal bir tablo ise. Bu tür hücrelerle aritmetik işlemler doğal olarak imkansızdır.

Hücrede metin olarak sayılar

Sayısal verileri bir hücrede metin biçiminde depolamaktan kaçının. Zamanla, bu tür bir sütundaki hücrelerin bazıları metin biçimine, bazıları ise normal biçime sahip olacaktır. Bu nedenle formüllerle ilgili sorunlar olacaktır.

Masanız bir LCD projektör aracılığıyla sunulacaksa

En zıt renk ve arka plan kombinasyonlarını seçin. Bir projektörde iyi görünüyor koyu arka plan ve hafif harfler. En korkunç izlenim, siyah üzerine kırmızı ve tam tersi şekilde yapılır. Bu kombinasyon projektörde son derece düşük kontrastlı görünüyor - bundan kaçının.

Excel'de sayfa modu çalışma sayfası

Bu, Excel'in yazdırıldığında sayfanın nasıl sayfalandırılacağını gösterdiği modla aynıdır. Sayfa kenarlıkları mavi renkle vurgulanır. Çoğu kişinin yaptığı bu modda sürekli çalışmayı önermiyorum, çünkü yazıcı sürücüsü ekranda veri görüntüleme sürecine dahil oluyor ve bu, birçok nedene bağlı olarak (örneğin, şu anda bir ağ yazıcısı mevcut değil) ), oluşturma işleminde ve formülleri yeniden hesaplamada donmalarla doludur. Her zamanki gibi çalışın.

Excel hakkında daha da yararlı bilgiler için, adresini ziyaret edin.

Yeterince sık üçüncü taraf programlardan gelen veriler Excel'e yüklenir daha fazla işlenmesi için ve genellikle bu verilerin formüllerde daha fazla kullanılması öngörülemeyen bir sonuç verir: sayılar toplanmaz, tarihler arasındaki gün sayısını hesaplamak imkansızdır, vb.

Bu makale, bu tür sorunların nedenlerini tartışıyor ve Farklı yollar onların ortadan kaldırılması

neden bir ... Metin olarak kaydedilen numara

Bu durumda, sayıların veya tarihlerin hücrenin sol kenarına (metin olarak) basıldığını ve kural olarak hücrenin sol üst köşesinde bir hata işareti (yeşil üçgen) ve bir etiket olduğunu görebilirsiniz. bu, fareyi üzerine getirdiğinizde, sayının metin olarak kaydedildiğini açıklar.

Sayısal, Genel veya Tarih biçiminde hiçbir değişiklik durumu düzeltmez, ancak formül çubuğuna tıklarsanız (veya F2'ye basarsanız) ve ardından Enter tuşuna basarsanız, sayı bir sayı olur ve tarih bir tarih olur. Bu tür çok sayıda sayı ile, gördüğünüz seçenek kabul edilemez.

Bu sorunu çözmenin birkaç yolu vardır.

  • Bir hata işaretçisi ve etiketi kullanma... Bir hata işareti görürseniz ( yeşil üçgen) ve etiketleyin, ardından hücreleri seçin, etikete tıklayın ve seçeneği belirleyin Sayıya dönüştür
  • Bul / Değiştir işlemini kullanma... Tabloda metin olarak saklanan ondalık basamaklı sayılar olduğunu varsayalım. Rakamlarla bir aralık seçin - tıklayın Ctrl + h(veya sekmede bulun ev veya menüde Düzenlemek 2007'den önceki sürümler için komut Yer değiştirmek) -- sahada Bulmak tanıtmak , (virgül) - alanda İle ikame edilmiş biz de tanıtıyoruz , (virgül) - Hepsini değiştir... Böylece, virgül yerine virgül koyarak hücre düzenlemeyi aynı şekilde simüle ederiz. F2 - Girin


Benzer bir işlem tarihlerle de yapılabilir, tek fark nokta nokta değiştirmeniz gerekir.

Ek olarak, üçüncü taraf programlar, ondalık ayırıcı olarak noktalı sayıları kaldırabilir, ardından noktayı virgülle değiştirmek yardımcı olacaktır.

SUBSTITUTE () işlevi kullanılarak formül (aşağıya bakın) ile benzer bir değiştirme yapılabilir.

  • Özel Yapıştır'ı Kullanma... Bu yöntem, kesirli sayılar, tam sayılar ve tarihlerle çalıştığı için daha çok yönlüdür. Herhangi bir boş hücre seçin - komutu yürütün kopyala- sorunlu numaraları olan aralığı seçin - Özel yapıştır -- Katlamak için-- Tamam... Böylece, değerlerini hiçbir şekilde etkilemeyen, ancak sayı biçimine dönüştüren sayılara (veya tarihlere) 0 ekleriz.


Bu tekniğin bir varyasyonu, aralığı 1 ile çarpmak olabilir.

  • Sütunlara Göre Metin aracını kullanma... Bu teknik, bir sütunu dönüştürmeniz gerekirse kullanmak için uygundur, çünkü birkaç sütun varsa, adımların her sütun için ayrı ayrı tekrarlanması gerekecektir. Bu nedenle, metin olarak kaydedilmiş sayılar veya tarihler içeren sütunu seçin, hücre biçimini ayarlayın Genel(sayılar için, örneğin, Sayısal veya Parasal). Ardından, komutu yürütüyoruz Veri-- Sütun Metni -- Hazır


  • formülleri kullanma... Tablo ek sütunlara izin veriyorsa, sayıya dönüştürmek için formülleri kullanabilirsiniz. Bir metin değerini sayıya dönüştürmek için çift eksi, sıfırla toplama, birle çarpma, DEĞER (), DEĞER () işlevlerini kullanabilirsiniz. Daha detaylı okuyabilirsiniz. Dönüştürmeden sonra ortaya çıkan sütun, orijinal verilerin yerine değerler olarak kopyalanıp yapıştırılabilir.


  • Kullanarak... Aslında, listelenen yöntemlerden herhangi biri bir makro ile gerçekleştirilebilir. Böyle bir dönüştürmeyi sık sık yapmanız gerekiyorsa, bir makro yazıp gerektiği gibi çalıştırmanız mantıklı olur.

İşte iki makro örneği:

1) 1 ile çarp

Sub conv () Dim c As Aralık Seçimdeki Her c İçin Numeric ise (c.Value) O zaman c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End If Next End Sub

2) sütunlara göre metin

Sub conv1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" End Sub

İkinci sebep ... Numara yabancı karakterler içeriyor.

Çoğu zaman, bu yabancı karakterler boşluklardır. Hem sayının içinde basamak ayırıcı olarak hem de sayıdan önce / sonra yer alabilirler. Bu durumda, doğal olarak, sayı metne dönüşür.

Bırakmak ekstra boşluklar işlemi kullanılarak da yapılabilir. Bul / Değiştir... alanında Bulmak bir boşluk girin ve alan İle ikame edilmiş boş bırak o zaman Hepsini değiştir... Sayıda sıradan boşluklar varsa, bu eylemler yeterli olacaktır. Ancak sayı, bölünemez boşluklar (160 kodlu sembol) içerebilir. Böyle bir boşluğun doğrudan hücreden kopyalanması ve ardından alana yapıştırılması gerekir. Bulmak iletişim kutusu Bul / Değiştir... Veya sahada yapabilirsiniz Bulmak klavye kısayoluna basın Alt + 0160(sayılar sayısal tuş takımında yazılır).

Boşluklar bir formülle de kaldırılabilir. Seçenekler şunlardır:

Düzenli boşluklar için: = - İKAME (B4; ""; "")

Bölünemez boşluklar için:= - İKAME (B4, SEMBOL (160), "")

Bunlar ve diğer alanlar için hemen: = - İKAME (YEDEK (B4; SEMBOL (160); ""); ""; "")

Bazen istenen sonucu elde etmek için listelenen yöntemleri birleştirmeniz gerekir. Örneğin, önce boşlukları kaldırın ve ardından hücre biçimini dönüştürün



okumanız önerilir