ORTALAMA HESAPLAMA FORMÜLÜ HK.

[bcyum]

Excel Versiyonu

Excel 2016

Excel Sürümü

64 Bit

Excel Dili

Türkçe

Selamlar herkese,

Üzerinde çalıştığım bir projede yaklaşık 1000 tane excel dosyasında kullanılmak üzere aşağıdaki dosyada ihtimalleri belirtilen bir ortalama hesaplama formülüne ihtiyacım bulunmakta.

ekteki dosyada bir formül yazdık ama hala hatalar alıyorum. Yardımlarına ihtiyacım var.

Kısaca özetlemek gerekirse ölçüm yapılan parametrelerin ortalamasını alıyoruz. 3 tane ölçümümüz var. Kuralımız LOQ değeri varsa yani "<0,2" gibi küçüktür içeren bir değer, bunu 0,2 olarak görmesi ve yarıya bölerek 0,1 olarak alıp işlem yapması.

Farklı bir formül yada makro vb yapabilirseniz de çok mutlu olurum.

Yardımlarınız için şimdiden çok teşekkür ederim.

 

İç İçe EĞER Kullanma

Senaryo Destek

[Ömer BARAN]

Merhaba Sayın @bcyum .
Evet, dosyada açıklamalar yazmışsınız ancak ben tam olarak anladığımı söyleyemem doğrusu.

Üstteki tabloda, alt taraftaki seçeneklerin herbiri için sayısal kısımları birbirinden farklı en az ikişer veri satırı olup,
H sütununun sağında, sadece =(.../2+....+.../2)/3 gibi basit formüllerle ya da formül kullanmadan olması gereken sonuçları
elle yazarak gösterip, ilgili açıklamayı sonucun hemen yanındaki hücreye yazsaydınız daha iyi olurdu sanırım.
Ayrıca herbir durum farklılığı için D sütunundan küçük/büyük durumuna ait örnek satırlarda olmalı galiba.

.

 

[52779]

Selamlar,
Ben, anladığım kadarıyla, bir şeyler yaptım...
Eki incelersiniz....

H3 hücresindeki formül
=EĞERHATA(ELEMAN(ARA(KAÇINCI(EĞERSAY(E3:G3;"*<*")&EĞERSAY(E3:G3;"-")&EĞERSAY(E3:G3;"<>*");$K$2:$K$10; );{1;4;7};{1;2;3});D3;(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";E3:G3)))*(YERİNEKOY(E3:G3;"<";"")/2))+TOPLA(E3:G3))/3;ORTALAMA(E3:G3));D3).

Not : Sayfanın altında yer alan koşulları bir tabloya dönüştürdüm ve bu tablodaki değerleri sizin ilgili satırlarda arattırdım. Formüldeki $K$2:$K$10 aralık tablodaki Değer kolonudur.

 

[Ömer BARAN]

Önce bundan önceki cevapları okuyunuz.
D sütunundaki değerle karşılaştırma kısmını tam olarak anlayamadım.
Anladığımı düşündüğüm, ORTALAMA alma olayıyla ilgili formül hazırlamıştım onu göndereyim dedim.
Formül dizi formülü olarak uygulanmalıdır (CTRL+SHIFT+ENTER ile formül uygulaması) .
Bu formül sonuçlarıyla D sütunu karşılaştırmasını siz halledersiniz sanırım.

Görüntülemek için giriş yapmanız gerekmektedir.
(1 satır)

.

 

[Ömer BARAN]

Sayın @bcyum , önce bundan önceki cevapları okuyunuz.

İstediğiniz sonuca (ortalama ve D sütunuyla karşılaştırma dahil) aşağıdaki formül ile ulaşılıyor sanırım.
Deneyiniz. Formül dizi formülü olduğundan hücreye CTRL+SHIFT+ENTER ile uygulanmalıdır.
Dizi formülüne dönüştürme işlemini doğru yapmışsanız formül, kendiliğinden {....} gibi köşeli parantez arasına alınır.

Görüntülemek için giriş yapmanız gerekmektedir.
(1 satır)

.

 

[52779]

Selamlar,
@Ömer BARAN ın ortalamaya yönelik vermiş olduğu formül,
gönderdiğim dokumanda, 1 adet "<", 1 adet "-" ve 1 adet Rakam yazılması durumunda HATA verip LOQ değerini alacağına işaret etti..
Teşekkürler @Ömer BARAN

Bu nedenle de formülü
=EĞERHATA(ELEMAN(ARA(KAÇINCI(--(EĞERSAY(E3:G3;"*<*")&EĞERSAY(E3:G3;"-")&EĞERSAY(E3:G3;"<>*"));$K$2:$K$10; );{1;4;7};{1;2;3});D3;(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));ORTALAMA(E3:G3));D3)

şeklinde güncelledim. Listenizde olmayan diğer durumları da kontrol ettim. Eki güncelledim. İncelersiniz....

Lütfen bu düzeltmeyi dikkate alınız....

Ek

 

[bcyum]

Öncelikle tüm forum yöneticilerine ve üyelerine ilgilerinden, desteklerinden dolayı teşekkür etmek istiyorum.

Ancak maalesef verilen formüller ile istediğim sonucu alamıyorum. Burada benim de hatam var, konuyu tekrar okuduğum da sizlerin de yazdığı gibi yeterince açık olamamışım. Dolayısıyla bu eksikliğim için sizlerden özür diliyorum.

Konuya dönecek olursak; amacımız daha öncede dediğim gibi ortalama almak. Ancak farklı senaryolar mevcut. LOQ dediğimiz "<10" gibi ifadeler en büyük problemimiz.

3 değerinde sayı olması durumu; normal ortalama işlemi
2 değerin sayı - 1 değerin LOQ olması durumu:

ÖRN: 6 5 <5 değerlerinin ortalamasını almak için (6+5+(5/2))/3 işlemini yapıyoruz. Yani LOQ değerlerinin yarısını alıyoruz.

Bunlardan yola çıkarak öngördüğüm 10 farklı senaryo var.


3 LOQ Durumu : LOQ Sonuç
2 LOQ 1 (-) Durumu : LOQ Sonuç
2 LOQ 1 rakam Durumu : LOQ sayılarının ikiye bölünmesi ve 3 değerin ortalamasının alınması
1 LOQ 1 rakam 1 (-) Durumu : LOQ değerinin ikiye bölünmesi ve 2 değerin ortalamasının alınması
1 LOQ 2 rakam durumu : LOQ değerinin doğrudan ikiye bölünmesi ve 3 değerin ortalamasının alınması
1 LOQ 2 (-) Durumu : LOQ Sonuç
3 Sayı Durumu : 3 Sayının ortalaması
2 Sayı 1 (-) Durumu: 2 Sayının Ortalaması
1 Sayı 2 (-) Durumu: 1 Sayının Ortalaması

Son önermem ise LOQ değeri ile alakalı. Diyelim ki tüm bunlar gerçekleşti ve LOQ değerim <5 ancak ortalamamız 4 çıktı. LOQ'nun altında bir değer olduğu için bunu da LOQ yani <5 basması gerekmekte.

Ben baya bir çabaladım, Aşırı uzun bir formül yazdım. Formül yazdığım yerde direk çalışıyor. Ancak esas excel dosyalarına geçtiğimde de hala oradaki hücreleri hedef alıyor. Yani akıllı bir şekilde yapıştırıldığı yerin yanındaki üç hücreyi seçmiyor.

Destekleriniz için sonsuz teşekkürler tekrardan.

Nokta veya virgülleri kontrol ediniz. Bilgisayar ayarına göre ondalık ayracının doğru olduğuna emin olunuz.
Düzeltemezseniz ve Kendi belgenizi paylaşmanızda sakınca varsa Kendi belgenizi farklı kaydedip hata veren satır hariç bütün satırları silip paylaşırsanız yardımcı olmaya çalışırım.

Sayın @bcyum , bende düzgün çalışıyor.
Sorun excel versiyonundan kaynaklanıyor olabilir.
Fonsiyonları kendi versiyonunza göre düzenleyin.
Ekran fotosu ve dosya ektedir.

Selamlar,
@Ömer BARAN ın ortalamaya yönelik vermiş olduğu formül,
gönderdiğim dokumanda, 1 adet "<", 1 adet "-" ve 1 adet Rakam yazılması durumunda HATA verip LOQ değerini alacağına işaret etti..
Teşekkürler @Ömer BARAN

Bu nedenle de formülü
=EĞERHATA(ELEMAN(ARA(KAÇINCI(--(EĞERSAY(E3:G3;"*<*")&EĞERSAY(E3:G3;"-")&EĞERSAY(E3:G3;"<>*"));$K$2:$K$10; );{1;4;7};{1;2;3});D3;(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));ORTALAMA(E3:G3));D3)

şeklinde güncelledim. Listenizde olmayan diğer durumları da kontrol ettim. Eki güncelledim. İncelersiniz....

Lütfen bu düzeltmeyi dikkate alınız....

Ek

 

[52779]

Selam,

Deneyiniz.....

H3 hücresine

=EĞERHATA(EĞER(--YERİNEKOY(YERİNEKOY(D3;"-";0);"<";"")<(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));D3);D3)

formülünü yazıp aşağıya doğru kopyalayın...

Not : E-F-G kolonlarına 3 değer girilmeli ve bu girilen değerler "<x", "-" ya da rakam şeklinde olmalı....Aksi halde HATA verecektir. Ve de HATA durumunda direkt olarak LOQ değerini yazacaktır. LOQ yazmayıp, HATA versin derseniz EĞERHATA(formül;D3) şeklindeki bu formülde KIRMIZI işaretlileri silersiniz..

Dosyanız ektedir.

 

[cakarem]

Kullanıcı Tanımlı Fonksiyon yöntemi ile bir kod yazdım. Ekli dosyayı inceleyiniz

Formül N sütununa yazılmıştır.
İlgili formül yapısı :
= exceldestek ( hücre, hücre, hücre )

İlgili kod yapısı:

Görüntülemek için giriş yapmanız gerekmektedir.
(105 satır)

 

[bcyum]

Selam,

Deneyiniz.....

H3 hücresine

=EĞERHATA(EĞER(--YERİNEKOY(YERİNEKOY(D3;"-";0);"<";"")<(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));D3);D3)

formülünü yazıp aşağıya doğru kopyalayın...

Not : E-F-G kolonlarına 3 değer girilmeli ve bu girilen değerler "<x", "-" ya da rakam şeklinde olmalı....Aksi halde HATA verecektir. Ve de HATA durumunda direkt olarak LOQ değerini yazacaktır. LOQ yazmayıp, HATA versin derseniz EĞERHATA(formül;D3) şeklindeki bu formülde KIRMIZI işaretlileri silersiniz..

Dosyanız ektedir.

Selamlar,

Birkaç dosyamda denedim ve şimdilik görebildiğim kadarıyla çalışmadığı bir senaryo olmadı. Tabi tüm gün uğraşmanın verdiği yorgunlukla ve verilerin miktarının çok çok fazla olması sebebiyle bazı şeyleri gözden kaçırıyor olabilirim. Ancak hiç zannetmiyorum.

Yarın çok daha detaylı bir şekilde inceleyeceğim.

Bu kadar kısa ve çalışan bir formül yazabildiğiniz için size minnettarım. Diğer dosyalarımda da denedikten sonra tekrar gelip teşekkür edeceğim size.

 

[52779]

Bu arada bir şey eklemem gerekiyor diye düşünüyorum,
Örnek dosyanıza göre, örnek verecek olursam
4. satır (Parametre Renk) E4 : <5, F4 : <5, G4 : <5 ve LOQ : <5 değerleri var.
E4 : <50 gibi bir yazılım olmayacağını düşündüm. Daha doğrusu, <50 yerine rakam yazılacağını düşündüm (LOQ : <5 ise, E - F - G olsa olsa <5 olur diye yorumladım...)
Aksi halde, verilen formüle bir koşul eklemek gerekir. O koşul da

=EĞERHATA(EĞER(EĞERSAY(E3:G3;"<>*")=0;D3;EĞER(--YERİNEKOY(YERİNEKOY(D3;"-";0);"<";"")<(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));D3));D3)

Kırmızı ile belirtilen şekildedir...

 

[bcyum]

Kullanıcı Tanımlı Fonksiyon yöntemi ile bir kod yazdım. Ekli dosyayı inceleyiniz
Ekli dosyayı görüntüle 8660
Formül N sütununa yazılmıştır.
İlgili formül yapısı :
= exceldestek ( hücre, hücre, hücre )

İlgili kod yapısı:

Görüntülemek için giriş yapmanız gerekmektedir.
(105 satır)

Selamlar,

Sizin yazdığınız bu kodları çalıştırmayı başarabilirsem keyfime diyecek yok. Çalıştığımız örnek dosya üzerinde tüm fonksiyonlar çalışıyor olarak gözüküyor. Ancak esas dosyalara aktarmayı şu an için gerçekleştiremedim. Yarın bunların hepsini deneyeceğim ve tekrar teşekkür için buraya geleceğim.

Yardımlarınız için çok çok teşekkür ederim.

 

[bcyum]

Bu arada bir şey eklemem gerekiyor diye düşünüyorum,
Örnek dosyanıza göre, örnek verecek olursam
4. satır (Parametre Renk) E4 : <5, F4 : <5, G4 : <5 ve LOQ : <5 değerleri var.
E4 : <50 gibi bir yazılım olmayacağını düşündüm. Daha doğrusu, <50 yerine rakam yazılacağını düşündüm (LOQ : <5 ise, E - F - G olsa olsa <5 olur diye yorumladım...)
Aksi halde, verilen formüle bir koşul eklemek gerekir. O koşul da

=EĞERHATA(EĞER(EĞERSAY(E3:G3;"<>*")=0;D3;EĞER(--YERİNEKOY(YERİNEKOY(D3;"-";0);"<";"")<(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));(TOPLA.ÇARPIM((ESAYIYSA(BUL("<";YERİNEKOY(E3:G3;"-";0))))*(YERİNEKOY(YERİNEKOY(E3:G3;"-";0);"<";"")/2))+TOPLA(E3:G3))/(3-EĞERSAY(E3:G3;"-"));D3));D3)

Kırmızı ile belirtilen şekildedir...

Yorumunuz son derece doğru. Eğer ki LOQ <5 ise onun haricinde bir değer gelmemesi gerekiyor. Geliyorsa bu zaten sistemsel bir hata olduğunu gösteriyor ve orada işlem yapmamız gerektiğine işaret ediyor. Yani eğer ki içerde D4 dışında bir LOQ değerimiz varsa hata vermesi bizim için daha iyi olacaktır.