Excel'de bir aralıktaki birimleri olan sayıları nasıl doğru bir şekilde toplayabilirsiniz?
Excel ile çalışırken, "5 kg", "12 lbs" veya "10 m" gibi birim içeren sayıları toplamak zorunda kalabileceğiniz durumlarla karşılaşabilirsiniz. Örneğin, ağırlıkları, uzunlukları veya miktarları takip ederken—özellikle satın alma listelerinde, mühendislik ölçümlerinde, laboratuvar kayıtlarında ve lojistik verilerinde her değer bir ölçüm birimiyle birlikte gösterilir. Ancak, bu tür veriler üzerinde Excel'in standart TOPLA formülünü kullanmayı denerseniz, beklenen şekilde çalışmayacaktır. Bunun nedeni, Excel'in metin (birimler gibi) içeren değerleri sayısal değerler yerine metin dizisi olarak yorumlamasıdır; bu da TOPLA ve diğer temel matematik fonksiyonlarının bu değerleri görmezden gelmesine veya hata döndürmesine neden olur. Bu değerleri doğru bir şekilde toplamak için, hesaplama gerçekleştirmeden önce sayısal kısmın çıkarılması gerekmektedir. Bu eğitim, size formüller, yerleşik özellikler ve Kutools for Excel gibi pratik eklentiler de dahil olmak üzere farklı gerçek dünya senaryolarında birimli sayıları doğru bir şekilde toplamanıza yardımcı olacak birkaç yöntem sunacaktır.
➤ Bir formül kullanarak birimli sayıları toplama
➤ Kutools for Excel kullanarak birimli sayıları toplama
➤ Kutools for Excel kullanarak aynı birime göre sayıları toplama
➤ Yinelemeli satırları birleştirme ve karşılık gelen sayıları Kutools for Excel ile toplama
➤ VBA makrosu: Metin dizelerinden birimli sayıları otomatik olarak çıkarma ve toplama
➤ Power Query kullanarak birimli sayıları çıkarma ve toplama
Bir formül kullanarak birimli sayıları toplama
Tüm değerleriniz aynı birimi paylaşıyorsa ve hızlı, formül tabanlı bir çözüme ihtiyacınız varsa, Excel dizisi formülleri sayısal kısmı çıkarmaya ve toplamayı hesaplamaya yardımcı olabilir. Bu yöntem, her hücrede bir sayıdan hemen sonra tam olarak aynı birimin geldiği aralıklar için uygundur, örneğin "8 kg", "15 kg" vb.
Sonucunuzu almak için boş bir hücre seçin ve aşağıdaki formülü girin:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)Formülü yazdıktan sonra, Excel'in bunu bir dizi işlemi olarak tanıması için Ctrl + Shift + Enter tuşlarına birlikte basın (bazı sistemlerde, bu dizi formülleri için gereklidir). Ardından, seçtiğiniz hücrede sayısal kısımların toplamını göreceksiniz.
İpucu: Yukarıdaki formülde B2:B7, toplamak istediğiniz birimli değerleri içeren veri aralığına atıfta bulunur ve "kg" verilerinizde bulunan birimdir. Aralıktaki tüm girişlerin birim açısından tutarlı olduğundan ve fazladan boşluk veya yazım hataları olmadığından emin olun, yoksa formül değerleri doğru bir şekilde çıkarmayacaktır. Biriminiz veya aralığınız farklıysa, her ikisini de buna göre ayarlayın. Bu formül, birden çok birim türünün bulunduğu aralık senaryolarını işlememektedir.
Kutools for Excel kullanarak birimli sayıları toplama
Formülleri ezberlemek istemeyen veya daha görsel bir yaklaşımı tercih eden kullanıcılar için Kutools for Excel, sayısal değerleri temizlemenin doğrudan bir yolunu sunar. Belirli Karakterleri Sil özelliği, birimleri kolayca ayırarak sayıları doğrudan toplamanıza olanak tanır. Bu, verilerinizdeki bir sütunda hem sayılar hem birimler bulunuyorsa ve sayıları hızlıca hesaplamalar için kullanılabilir hale getirmeniz gerekiyorsa idealdir.
1. Birimli sayıları içeren hücreleri vurgulayın. Kutools sekmesine gidin, Metin'e tıklayın ve Belirli Karakterleri Sil'i seçin. Ekran görüntüsüne bakın:
2. Belirli Karakterleri Sil iletişim kutusunda yalnızca Sayısal Olmayan seçeneğini işaretleyin. Bu ayar, tüm karakterleri sayılar dışında siler. Ekran görüntüsüne bakın:
3. Tamam'a veya Uygula'ya tıklayın. Artık seçili hücrelerde yalnızca sayısal içerikler kalır, bu sayıyı basit bir formülle toplayabilirsiniz. Boş bir hücre seçin ve sonucunuzu almak için aşağıdaki formülü girin:
=SUM(B2:B7)Bu işlemden sonra temizlenmiş sütun ve toplamın nasıl görüneceği aşağıdaki resimde gösterilmiştir.
Kutools for Excel - Excel'i 300'den fazla temel araçla güçlendirin. Sürekli ücretsiz AI özelliklerinden yararlanın! Hemen Edinin
Not: Kutools, seçili hücrelerde doğrudan değişiklik yapar, bu nedenle orijinal metni korumanız gerekiyorsa verilerinizin bir kopyasında çalışmanız önerilir. Bu çözüm, tek tip birimler için en iyi şekilde çalışır. Bu özelliği kullandıktan sonra, toplama işleminden önce tüm temizlenmiş değerlerin sayısal olduğundan emin olun.
Kutools for Excel ve TOPLA işlevini kullanarak aynı birime dayalı sayıları toplama
Kutools for Excel kullanarak aynı birime dayalı sayıları toplama
Tablonuzda, örneğin hem "Kg" hem de "Pound" cinsinden ağırlıklar gibi farklı birimler içeren değerler varsa ve her bir birim için ayrı ayrı toplam almak istiyorsanız (örneğin toplam Kg ve toplam Pound miktarlarını elde etmek), Kutools'un Formül Yardımcısı özelliği bu işlemi kolaylaştırır. Bu özellik, özet tablolar, envanter raporları veya verileri daha fazla karşılaştırma için hazırlarken faydalıdır.
Excel için Kutools - Excel için 300 adet temel araç içeren bir paket. Sürekli ücretsiz AI özellikleriyle çalışın! Hemen indirin!
1. Toplanan sonucun görünmesini istediğiniz hücreyi seçin. Kutools > Formül Yardımcısı > İstatistiksel > Aynı metne göre toplamaya gidin.
2. Formül Yardımcısı iletişim penceresinde hesaplamayı ayarlayın: Argüman Girişi altında, tam veri aralığını Aralık olarak seçin ve Metin kutusuna belirli birimi (örneğin "Kg" veya "Pound") yazın veya seçin. Ardından Tamam'a tıklayın.
İpucu: Varsayılan formül mutlak referanslar kullanır (örneğin $B$2:$B$7). Eğer bu işlevi birden fazla birim veya satır için otomatik doldurmayı planlıyorsanız, gerekirse referansları manuel olarak göreceliye çevirin.
Şimdi, sonuç hücresi seçilen birim için toplamı görüntüler. Her birim için bu işlemi tekrarlayarak tüm grup toplamlarını elde edin.
Not: Bu yöntem, açıkça etiketlenmiş birim türlerine sahip tablolar için iyi çalışır ve elle gruplama hatasını en aza indirerek çoklu birim toplamayı otomatikleştirir.
Kutools for Excel kullanarak yinelemeli satırı birleştirme ve karşılık gelen sayıları toplama
Bazı veri setlerinde, meyve gibi öğeler veya varlıklar farklı birimlerde ağırlık girdileriyle birden fazla kez listelenir. Diyelim ki, öğe ve birime göre değerleri gruplandırarak yinelemeli satırları birleştirmek ve toplamı birleştirmek istiyorsunuz. Kutools for Excel'in Gelişmiş Satırları Birleştir özelliği tam da bu tür senaryolar için tasarlanmıştır, toplamayı hızlı ve kolaylaştırır.
1. Yinelemeli öğeler ve karışık birimler içeren tablonuzu seçin, ardından Kutools > Birleştir ve Böl > Gelişmiş Satırları Birleştir'e gidin.
2. Sütun Bazında Satırları Birleştir iletişim kutusunda, önce birimleri içeren sütunu seçin ve Anahtar olarak işaretleyin. Bu, Kutools'un verilerinizi birime göre gruplamasına olanak tanır. Ekran görüntüsüne bakın:
3. Ardından, sayısal değerleri içeren sütunu (örneğin "Ağırlık") vurgulayın, ardından Hesapla'yı seçin ve her grup için değerleri toplamak üzere Topla'yı seçin.
4. Tamam'a tıklayın ve tablonuz birimlere göre özetlenecek, sayılar gerektiği gibi birleştirilecektir. Bu özellikle, eşleşen birime göre birden fazla girdinin toplanması gereken envanter ve satış verileri için faydalıdır.
Kutools for Excel - Excel'i 300'den fazla temel araçla güçlendirin. Sürekli ücretsiz AI özelliklerinden yararlanın! Hemen Edinin
İpucu: Daha derin birleştirme için birden fazla anahtara (örneğin öğe ve birim) göre gruplayabilirsiniz.
Yinelenenleri Birleştir ve Sayıları Topla
VBA makrosu: Birim içeren metin dizelerinden sayıları çıkar ve otomatik olarak topla
Tam otomasyon arayan veya birim dizelerinde karmaşık veya çeşitli desenlerle çalışan ileri düzey kullanıcılar için, bir VBA makrosu çıkarma ve toplamayı bir adımda halledebilir. Bu yöntem, özellikle aralığınızda farklı birimlerle biten sayılar veya tutarsız veri yapıları bulunduğunda ve yerleşik Excel formüllerinin her durum için ayarlanması çok sınırlı veya zahmetli olduğunda faydalıdır.
1. Geliştirici > Visual Basic'e tıklayarak VBA Düzenleyicisini açın. Microsoft Visual Basic for Applications penceresinde Ekle > Modül'e tıklayın ve aşağıdaki kodu modüle yapıştırın:
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. Kodu çalıştırmak için makro içinde herhangi bir yere tıklayın ve F5tuşuna basın veya 
kod düzenleyici penceresinin üzerindeki Çalıştır düğmesine tıklayın. İçinde birimli sayılar bulunan aralığı seçmenizi isteyen bir iletişim kutusu açılacaktır. Betik, her metin hücresinden baştaki sayısal değeri otomatik olarak çıkaracak ve tüm sonuçları toplayıp toplamı bir açılır pencerede gösterecektir.
İpuçları ve kullanım notları:
- Bu makro, her dizedeki başlangıçtan itibaren sayısal karakterleri çıkarır ve bir sayısal olmayan karakter bulana kadar devam eder, bu da "5kg", "12 lbs" veya "20.5 m" gibi çoğu formatta çalıştığı anlamına gelir.
- Sayıların biçimi tutarsızsa veya dizenin içinde gömülü ise, daha fazla özelleştirme gerekebilir.
- İsterseniz birimleri ayrı ayrı toplamak veya işlemek için VBA kodunu değiştirebilirsiniz.
- Orijinal verilerin kaybolmasını önlemek için her zaman çalışma sayfanızın bir kopyası veya yedeği üzerinde makroları kullanın.
- Excel ayarlarınızda makro işlevselliğinin etkinleştirilmesi gerekmektedir.
Bu VBA çözümü, verilerinizdeki varyasyonlarla uğraşırken çok yönlüdür ve büyük veri setlerine uygulandığında önemli ölçüde zamandan tasarruf sağlar.
Power Query kullanarak birimli sayıları çıkarma ve toplama
Power Query, Excel'in yerleşik veri dönüştürme aracıdır ve birimli sayılar içeren daha büyük veya daha karmaşık veri setlerini işlemek için idealdir.
Veri yapıları tutarsız olduğunda veya tekrarlayan görevler için otomasyon gerektiğinde özellikle iyi çalışır.
1. Verilerinizi içeren aralığı seçin (birimli sayılar da dahil), ardından Veri sekmesine gidin ve Tablo/Aralıktan'a tıklayın. İstenirse, verilerinizin başlıkları olduğundan emin olun.
2. Power Query Editörü'nde, metin değerlerinizi içeren sütunu seçin. Kullanın Sütun Ekle sekmesi > Çıkar > Ayraçtan Önce Metin veya Ayracılar Arasındaki Metin.
Veri formatınıza göre seçin:
- Veri "12 kg" gibi ise boşluk ayracı kullanın
- Veri "12kg" gibi ise harf tabanlı ayracı kullanın
3. Yeni sütunun veri türünü Ondalık Sayı veya Tam Sayı olarak değiştirmek için başlığını sağ tıklayın ve Türü Değiştir'i seçin.
4. (Opsiyonel) Eğer birime göre toplamak istiyorsanız, Ayraçtan Sonra Metni Çıkar ile birimi daha fazla ayırın, ardından Grupla'ya (Ana sekmede) tıklayın ve yeni sayısal alan için Topla'yı seçin.
5. İşlenmiş verileri ve toplamları doğrudan Excel'e yeni bir sayfa veya tablo olarak aktarmak için Kapat ve Yükle'ye tıklayın.
Avantajlar:
- Karmaşık, tutarsız veya büyük verileri temiz ve hızlı bir şekilde işler.
- Adımlar kaydedilebilir ve gelecekteki veya güncellenmiş verilere yeniden uygulanabilir—bu yaklaşıma oldukça tekrarlanabilir yapar.
- Basit çıkarma işlemlerinden öteye geçerek dönüşüm mantığını özelleştirebilirsiniz, önekler, sonekler, ondalıklar ve farklı birimlerle gruplama işlemleri de dahil olmak üzere.
Sınırlamalar:
- Power Query'ye yeni başlayan kullanıcılar için bir öğrenme eğrisi vardır.
- En iyi şekilde yapılandırılmış verilere veya tablolarla çalışırken değil, doğrudan ad-hoc hücre girişlerinde uygulanır.
Sorun Giderme ve Öneriler:
- En iyi sonuçlar için, birimlerin veya sayıların biçimi genişçe değişiyorsa özellikle, çıkarılan veya dönüştürülen sayıları her zaman anormallikler için gözden geçirin.
- Herhangi bir toplu işlem (Kutools ve VBA dahil) öncesinde kaynak verilerinizi yedekleyin.
- Formüller kullanırken, verilerde ve birimlerde gizli boşluklar veya yazım hatalarını iki kez kontrol edin.
- Formüller başarısız olursa veya hata (
#DEĞER!) dönerse, tüm başvurulan hücrelerin beklenen biçimler içerdiğinden emin olun ve aralığı veya birim kriterlerini buna göre ayarlayın. - VBA kullanırken, makroların etkin olduğundan emin olun ve kurtarmayı kolaylaştırmak için çalıştırmadan önce kaydedin.
- Power Query dönüşüm adımları, verileriniz değişmişse Uygulanan Adımlar bölmesi üzerinden önizlemeden sonra ayarlanabilir.
İlgili Makaleler:
- Excel'de sıfırlarla başlayan birden fazla hücreyi nasıl bir hücrede birleştirirsiniz?
- Farklı çalışma sayfalarından/çalışma kitabından aralıkları tek bir aralığa nasıl birleştirirsiniz?
- Excel'de bir sütun değerine göre satırları nasıl hızlı bir şekilde birleştirir ve bazı hesaplamalar yaparsınız?
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin