{"id":205,"date":"2019-03-27T23:50:37","date_gmt":"2019-03-27T20:50:37","guid":{"rendered":"http:\/\/www.cuneytbayrak.com\/?p=205"},"modified":"2025-02-20T23:51:53","modified_gmt":"2025-02-20T20:51:53","slug":"sirasiz-listede-yakin-degeri-bulma","status":"publish","type":"post","link":"http:\/\/www.cuneytbayrak.com\/?p=205","title":{"rendered":"S\u0131ras\u0131z Listede Yak\u0131n De\u011feri Bulma"},"content":{"rendered":"

Elimizde birbirine yak\u0131n de\u011ferlere sahip\u00a0 bir liste oldu\u011funu varsayal\u0131m ve bu listede belli bir sapma ile en yak\u0131n de\u011feri bulmak isteyelim. Burada ARA form\u00fcl\u00fcn\u00fc kullanmak isteyebilirsiniz. Ancak Ara form\u00fcl\u00fc sadece s\u0131ralanm\u0131\u015f listede arama yapar ve e\u011fer aranan de\u011fer bulunamaz ise en yak\u0131n d\u00fc\u015f\u00fck de\u011feri d\u00f6nd\u00fcr\u00fcr. Ben burada e\u011fer arad\u0131\u011f\u0131m de\u011fer tam olarak yoksa belli bir sapma oran\u0131na g\u00f6re en yak\u0131n de\u011feri bulmak istiyorum ve yerine g\u00f6re d\u00fc\u015f\u00fck, b\u00fcy\u00fck ve en yak\u0131n de\u011feri d\u00f6nd\u00fcrmek amac\u0131nday\u0131m.\u00a0 \u00d6rnek olarak a\u015fa\u011f\u0131daki tabloyu ele alal\u0131m:<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\u015eimdi bu tabloda s\u0131ras\u0131 ile verdi\u011fimiz tolerans de\u011ferine g\u00f6re tabloda g\u00f6sterdi\u011fim en yak\u0131n de\u011ferleri bulmak isteyelim. Ben tabloyu B2 h\u00fccresinden itibaren yap\u0131\u015ft\u0131rd\u0131m. Siz farkl\u0131 bir yere ald\u0131 iseniz form\u00fcllerde ba\u015fvuru adreslerini buna g\u00f6re de\u011fi\u015ftirin l\u00fctfen.<\/p>\n

\n
\n
1.En Yak\u0131n De\u011feri Bulma<\/strong><\/h5>\n<\/div>\n<\/div>\n
\n
\n
\n

En yak\u0131n de\u011feri bulma i\u015flemi g\u00f6receli olarak kolay bir i\u015flem. \u0130lk olarak aranan de\u011fer ile verilmi\u015f de\u011ferler aras\u0131ndaki fark\u0131 bulaca\u011f\u0131m:<\/p>\n

=$B$3:$B$7-$C$3<\/p>\n

Bu form\u00fcl ile t\u00fcm verilen de\u011ferler ile arad\u0131\u011f\u0131m\u0131z de\u011fer aras\u0131ndaki farklar\u0131 \u00e7\u0131karak {0,01; 0,03; -0,03;\u2026} bir dizin olu\u015fturaca\u011f\u0131z. Bizim arad\u0131\u011f\u0131m\u0131z k\u00fc\u00e7\u00fck veya b\u00fcy\u00fck olmas\u0131na bakmadan en d\u00fc\u015f\u00fck bulmak. Bunun i\u00e7in \u2013 olan de\u011ferleri + olacak \u015fekilde d\u00f6nd\u00fcrmemiz laz\u0131m. Bunun i\u00e7in MUTLAK form\u00fcl\u00fcn\u00fc kullanaca\u011f\u0131m:<\/p>\n

=MUTLAK($B$3:$B$7-$C$3)<\/p>\n

Bu form\u00fcl ile elde etti\u011fimiz de\u011ferleri bir dizin olarak i\u015fleyecek form\u00fcl\u00fc yazal\u0131m. Bildi\u011finiz gibi bunu \u0130ND\u0130S ile yap\u0131yoruz:<\/p>\n

=\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;)<\/p>\n

Art\u0131k elimde verilen de\u011ferlerle aranan de\u011fer aras\u0131ndaki farklar\u0131 sadece + say\u0131lardan olu\u015fan\u00a0{0,01; 0,03; 0,03;\u2026} \u015feklinde\u00a0bir dizin var. Bu dizin i\u00e7inde en d\u00fc\u015f\u00fck de\u011feri arayaca\u011f\u0131z. \u00c7\u00fcnk\u00fc en d\u00fc\u015f\u00fck fark arad\u0131\u011f\u0131m\u0131z de\u011fere en yak\u0131n de\u011ferdir. Bunun i\u00e7in M\u0130N form\u00fcl\u00fcn\u00fc kullanaca\u011f\u0131m:<\/p>\n

=M\u0130N(\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;))<\/p>\n

Evet art\u0131k elimizde en d\u00fc\u015f\u00fck fark de\u011feri var. Form\u00fcl\u00fc buraya kadar eksiksiz yazd\u0131 iseniz form\u00fcl 0,01 de\u011ferini d\u00f6nd\u00fcrecektir. \u015eimdi elde etti\u011fimiz bu de\u011fer acaba dizinin hangi eleman\u0131. Bunu bulacak form\u00fcl de KA\u00c7INCI:<\/p>\n

=KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;));\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;);0)<\/p>\n

Bu de\u011fer buldu\u011fumuz dizinin 1. eleman\u0131nda. Zaten bizim elde etti\u011fimiz dizin de elimizdeki rakamlar ile ayn\u0131 s\u0131ralamaya sahip. Bu da arad\u0131\u011f\u0131m\u0131z de\u011ferin asl\u0131nda verilen de\u011ferlerin ilki oldu\u011funu ifade ediyor. Hemen bu de\u011feri \u00e7ekelim:<\/p>\n

=\u0130ND\u0130S($B$3:$B$7;KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;));\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;); 0);)<\/p>\n

Bu sayede B2:B7 aral\u0131\u011f\u0131ndaki ilk de\u011feri geri d\u00f6nd\u00fcr\u00fcyoruz. Bu da 100,01. Ancak burada bir eksikli\u011fimiz var bu da buldu\u011fumuz en k\u00fc\u00e7\u00fck fark acaba bizim tan\u0131mlad\u0131\u011f\u0131m\u0131z tolerans de\u011ferinden k\u00fc\u00e7\u00fck m\u00fc? Hemen bir kontrol ekleyerek bunu denetleyelim ve e\u011fer k\u00fc\u00e7\u00fck ise form\u00fcl\u00fcm\u00fcz\u00fcn sonucunu geri d\u00f6nd\u00fcrelim, yok de\u011fil ise bu sefer \u201cDe\u011fer Bulunamad\u0131!\u201d yazal\u0131m:<\/p>\n

=E\u011eER(M\u0130N(\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;))<=$D$3;\u0130ND\u0130S($B$3:$B$7;KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(MUTLAK( $B$3:$B$7-$C$3);;));\u0130ND\u0130S(MUTLAK($B$3:$B$7-$C$3);;);0););\u201dDe\u011fer Bulunamad\u0131!\u201d)<\/p>\n

\n
\n
2.En Yak\u0131n B\u00fcy\u00fck De\u011feri Bulma<\/strong><\/h5>\n<\/div>\n<\/div>\n
\n
\n
\n

\u015eimdi i\u015fleri biraz daha zor bir hale getirece\u011fiz. Yine i\u015fe \u00f6nce arad\u0131\u011f\u0131m\u0131z de\u011fer ile verilen de\u011ferler aras\u0131ndaki fark\u0131 bulmakla ba\u015flayaca\u011f\u0131z:<\/p>\n

=$C$3-$B$3:$B$7<\/p>\n

Ancak bu sefer elde etti\u011fimiz de\u011fer s\u0131f\u0131rdan b\u00fcy\u00fck m\u00fc bunu kontrol etmeliyiz.<\/p>\n

=$C$3-$B$3:$B$7>0<\/p>\n

Bu form\u00fcl ile sadece fark\u0131n + oldu\u011fu de\u011ferler yani arad\u0131\u011f\u0131m\u0131z de\u011ferden k\u00fc\u00e7\u00fck olan de\u011ferlerin \u201cDO\u011eRU\u201d oldu\u011fu bir dizin elde ediyoruz. Ben sonraki a\u015famada en d\u00fc\u015f\u00fck fark\u0131 bulaca\u011f\u0131mdan bu de\u011ferlerin en d\u00fc\u015f\u00fck de\u011fer olarak \u00e7\u0131kmamas\u0131 i\u00e7in elde etti\u011fim de\u011ferleri tolerans\u0131n 10 kat\u0131 ile \u00e7arp\u0131yorum:<\/p>\n

=($C$3-$B$3:$B$7>0)*10*$D$3<\/p>\n

\u015eimdi bu form\u00fcl\u00fcn sonucunda {0,0,0,5,0,0] gibi bir dizin elde edece\u011fim. Arad\u0131\u011f\u0131m de\u011fer verilen de\u011fere en yak\u0131n b\u00fcy\u00fck de\u011fer oldu\u011fu i\u00e7in verilen de\u011ferler ile aranan de\u011fer aras\u0131ndaki fark\u0131 mutlak olarak bulaca\u011f\u0131m ve bu dizine ekleyece\u011fim. Bu sayede arad\u0131\u011f\u0131m de\u011ferden k\u00fc\u00e7\u00fck olan de\u011ferlerin form\u00fcl taraf\u0131ndan %100 elenmesini sa\u011flayabilirim. Tabii bir de bu t\u00fcm form\u00fclleri dizin olarak ele alacak \u0130ND\u0130S form\u00fcl\u00fc ile de birle\u015ftirmem gerek:<\/p>\n

=\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;)<\/p>\n

Evet art\u0131k elimde {0,01;0,03;0,53;0,09;0,06} \u015feklinde bir dizin var. Art\u0131k bu dizindeki en k\u00fc\u00e7\u00fck de\u011feri arayabilirim:<\/p>\n

=M\u0130N(\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;))<\/p>\n

Form\u00fcl\u00fcn sonucu bir \u00f6nceki \u00f6rnek gibi 0,01 olacakt\u0131r. Hemen bu eleman\u0131n dizinin ka\u00e7\u0131nc\u0131 eleman\u0131 oldu\u011funu bulal\u0131m:<\/p>\n

=KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;));\u0130ND\u0130S(($C$3-$B$3: $B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0)<\/p>\n

G\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi yine ilk elemanda bulduk. Hemen bu eleman\u0131 \u00e7ekelim:<\/p>\n

=\u0130ND\u0130S($B$3:$B$7;KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;)); \u0130ND\u0130S(($C$3-$B$3: $B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0);)<\/p>\n

Tabii buldu\u011fumuz de\u011ferin girdi\u011fimiz tolerans de\u011ferinden b\u00fcy\u00fck\u00a0 olup olmad\u0131\u011f\u0131n\u0131 da kontrol etmemiz gerekiyor. E\u011fer k\u00fc\u00e7\u00fck ise bu de\u011feri geri d\u00f6nd\u00fcrs\u00fcn ama de\u011filse hata mesaj\u0131 verelim:<\/p>\n

=E\u011eER(M\u0130N(\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;))<=$D$3;\u0130ND\u0130S($B$3:$B$7; KA\u00c7INCI(M\u0130N(\u0130ND\u0130S(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;));\u0130ND\u0130S(($C$3-$B$3: $B$7 >0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0););\u201dDe\u011fer Bulunamad\u0131!\u201d)<\/p>\n

\n
\n
3.En Yak\u0131n K\u00fc\u00e7\u00fck De\u011feri Bulma<\/strong><\/h5>\n<\/div>\n<\/div>\n
\n
\n
\n

En yak\u0131n de\u011feri bulma i\u015flevi bir \u00f6nceki ba\u015fl\u0131kta anlatt\u0131\u011f\u0131m En Yak\u0131n B\u00fcy\u00fck De\u011feri Bulma form\u00fcl\u00fc ile benzer. Tek fark\u0131 en ba\u015fta yapt\u0131\u011f\u0131m \u00e7\u0131karma i\u015flemini ters yapmak. Yani arad\u0131\u011f\u0131m\u0131z de\u011ferden verilen de\u011ferleri de\u011fil, verilen de\u011ferlerden aranan de\u011feri \u00e7\u0131karmak. Bu form\u00fcl\u00fc yazma i\u015fini size b\u0131rak\u0131yorum. Hem iyi bir uygulama olur hem de kafan\u0131zda daha rahat canland\u0131rabilirsiniz.<\/p>\n

Kolay gele…<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Elimizde birbirine yak\u0131n de\u011ferlere sahip\u00a0 bir liste oldu\u011funu varsayal\u0131m ve bu listede belli bir sapma ile en yak\u0131n de\u011feri bulmak isteyelim. Burada ARA form\u00fcl\u00fcn\u00fc kullanmak isteyebilirsiniz. Ancak Ara form\u00fcl\u00fc sadece…<\/p>\n

Devam\u0131n\u0131 OkuS\u0131ras\u0131z Listede Yak\u0131n De\u011feri Bulma<\/span><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":0,"footnotes":""},"categories":[4],"tags":[61],"class_list":["post-205","post","type-post","status-publish","format-standard","hentry","category-excel","tag-closestvalue","excerpt"],"_links":{"self":[{"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/posts\/205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=205"}],"version-history":[{"count":1,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/posts\/205\/revisions"}],"predecessor-version":[{"id":206,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=\/wp\/v2\/posts\/205\/revisions\/206"}],"wp:attachment":[{"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=205"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.cuneytbayrak.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}