{"id":3557,"date":"2022-03-30T14:56:37","date_gmt":"2022-03-30T14:56:37","guid":{"rendered":"https:\/\/www.turkticaret.net\/blog\/?p=3557"},"modified":"2022-04-08T10:58:41","modified_gmt":"2022-04-08T10:58:41","slug":"yeni-baslayanlar-icin-excel-formulleri","status":"publish","type":"post","link":"https:\/\/www.turkticaret.net\/blog\/yeni-baslayanlar-icin-excel-formulleri\/","title":{"rendered":"Yeni Ba\u015flayanlar \u0130\u00e7in Excel Form\u00fclleri"},"content":{"rendered":"

Excel form\u00fclleri<\/strong> konusuna girmeden \u00f6nce Excel nedir ne i\u015fe yarar <\/strong>sorusunu yan\u0131tlayal\u0131m. Daha sonra da s\u0131k kullan\u0131lan ve i\u015finizi kolayla\u015ft\u0131racak form\u00fclleri anlatal\u0131m.<\/p>\n

Excel Nedir Ne \u0130\u015fe Yarar?<\/h2>\n

MS Excel Program\u0131 <\/strong>default olarak bo\u015f bir \u015fekilde gelen, sat\u0131r ve s\u00fctuna g\u00f6re ayr\u0131lan k\u00fc\u00e7\u00fck kutulardan olu\u015fur. Her bir k\u00fc\u00e7\u00fck kutuya h\u00fccre denir. H\u00fccreler ba\u011fl\u0131 olduklar\u0131 sat\u0131r ve s\u00fctuna g\u00f6re adland\u0131r\u0131l\u0131r. \u00d6rnek verecek olursak; E4 h\u00fccresi, E s\u00fctunu ve 4. sat\u0131r\u0131n bile\u015feninden olu\u015fmaktad\u0131r. Exceli g\u00fcnl\u00fck i\u015flerinizden tutun da \u00e7al\u0131\u015fma hayat\u0131n\u0131zdaki i\u015flerin kolayla\u015ft\u0131r\u0131lmas\u0131na kadar kullanabilirsiniz. Sadece tablo olu\u015fturma olarak d\u00fc\u015f\u00fcnmemek gerekir. \u00c7ekti\u011finiz b\u00fcy\u00fck bir veriyi baz\u0131 form\u00fclleri kullanarak kolayca ay\u0131klayabilirsiniz. Hem Windows hem de Mac i\u015fletim sisteminde bu program\u0131 kullanabilirsiniz. A\u015fa\u011f\u0131da hem s\u0131k kullan\u0131lan hem de excele yeni ba\u015flayanlar\u0131n kullanabilece\u011fi baz\u0131 temel form\u00fclleri verece\u011fiz. Direkt form\u00fclleri vermeden \u00f6nce a\u015fa\u011f\u0131daki maddeleri inceleyerek form\u00fcl\u00fc nas\u0131l olu\u015fturabilece\u011finize y\u00f6nelik ipu\u00e7lar\u0131n\u0131 incelemenizi \u00f6neririz.<\/p>\n

    \n
  • Form\u00fcl olu\u015fturmak i\u00e7in \u00f6ncelikle bir h\u00fccre se\u00e7ip; e\u015fittir”=” ya da art\u0131 “+” i\u015faretlerinden birini se\u00e7in. B\u00fcy\u00fck olas\u0131l\u0131kla e\u015fittir “=” i\u015fareti kullan\u0131l\u0131r, daha sonra i\u015flem yapaca\u011f\u0131n\u0131z h\u00fccreleri se\u00e7ebilir ya da form\u00fcl\u00fc yazabilirsiniz.<\/li>\n
  • Belirli bir h\u00fccre aral\u0131\u011f\u0131nda i\u015flem yapmak istiyorsan\u0131z iki nokta “:” kullanmal\u0131s\u0131n\u0131z. \u00d6rne\u011fin E4 ile E18 aral\u0131\u011f\u0131nda i\u015flem yap\u0131lmas\u0131 i\u00e7in “:” kullan\u0131l\u0131r.<\/li>\n
  • Aral\u0131kl\u0131 iki h\u00fccrede i\u015flem yapmak istiyorsan\u0131z da noktal\u0131 virg\u00fcl “;” kullanmal\u0131s\u0131n\u0131z. \u00d6rne\u011fin sadece A3 ve A38 i\u00e7in i\u015flem yapmak istiyorsan\u0131z “;” kullanman\u0131z gerekir.<\/li>\n
  • Olu\u015fturdu\u011funuz form\u00fclleri men\u00fc k\u0131sm\u0131n\u0131n alt\u0131ndaki f\u00f6rm\u00fcl \u00e7ubu\u011fundan da g\u00f6rebilirsiniz.<\/li>\n
  • Form\u00fcl yazarken altta \u00e7\u0131kan form\u00fclleri TAB tu\u015fuyla ya da \u00e7ift t\u0131klayarak se\u00e7ebilirsiniz.<\/li>\n
  • Bir ya da birka\u00e7 h\u00fccreyi bi\u00e7imlendirmek\/\u00f6zelli\u011fini de\u011fi\u015ftirmek i\u00e7in de ilgili h\u00fccre\/h\u00fccreler se\u00e7ilerek sa\u011f t\u0131klan\u0131r ve H\u00fccreleri Bi\u00e7imlendir se\u00e7ene\u011fi se\u00e7ilir. Ba\u015fka bir yolu da ilgili h\u00fccreleri se\u00e7ip CTRL+1 yap\u0131larak bi\u00e7imlendirme yapabilirsiniz.<\/li>\n<\/ul>\n

    \"Excel<\/p>\n

    Yeni Ba\u015flayanlar \u0130\u00e7in En Faydal\u0131 Excel Form\u00fclleri<\/h2>\n

    A\u015fa\u011f\u0131da s\u0131k kullan\u0131lan ve i\u015flerinizi kolayla\u015ft\u0131racak excel form\u00fclleri bulabilirsiniz.<\/p>\n

    =TOPLA (=SUM) Form\u00fcl\u00fc<\/h3>\n

    Sonucu g\u00f6rmek istedi\u011finiz h\u00fccrenin i\u00e7ine “=TOPLA” ya da “=SUM” yaz\u0131p \u00e7ift t\u0131klayarak veya TAB tu\u015fuyla se\u00e7erek toplama i\u015flemi yapmak istedi\u011finiz h\u00fccreleri se\u00e7ebilirsiniz. H\u00fccre aral\u0131\u011f\u0131nda i\u015flem yapmak istiyorsan\u0131z “:” belirli h\u00fccrelerde toplama yapmak i\u00e7in de “;” kullanmal\u0131s\u0131n\u0131z. Toplama i\u015flemini farkl\u0131 yollarla da yapabilirsiniz. \u00d6rne\u011fin “=A5+B3+C8” gibi. + yerine “-”\u00a0 \u00e7\u0131karma, “\/” b\u00f6lme ve “*” kullanarak da \u00e7arpma i\u015flemi yapabilirsiniz.<\/p>\n

    \"\"<\/p>\n

    =ORTALAMA (=AVERAGE) Form\u00fcl\u00fc<\/h3>\n

    Bo\u015f bir h\u00fccre se\u00e7in ve “=ORTALAMA” yaz\u0131p \u00e7ift t\u0131klayarak ya da TAB tu\u015fuyla se\u00e7in. \u0130\u015flem yapmak istedi\u011finiz h\u00fccre aral\u0131\u011f\u0131n\u0131 “:” kullanarak ya da direkt mouse ile aral\u0131k se\u00e7in. Se\u00e7mek istedi\u011finiz \u00f6zel h\u00fccreler varsa da “;” kullan\u0131n.<\/p>\n

    \"\"<\/p>\n

    =E\u011eER (=IF) Form\u00fcl\u00fc<\/h3>\n

    E\u011fer form\u00fcl\u00fc ile bir \u015farta ba\u011fl\u0131 olarak sonu\u00e7 alabilirsiniz. Mesela sonu\u00e7 istenen h\u00fccreye; =E\u011eER(D4>55;”GE\u00c7T\u0130”;”KALDI) \u015feklinde bir form\u00fcl yazarak D4 h\u00fccresindeki de\u011fer 55’ten b\u00fcy\u00fck ise “GE\u00c7T\u0130” k\u00fc\u00e7\u00fck ise “KALDI” sonucunu verecektir.<\/p>\n

    \"excel<\/p>\n

    =MAK (=MAX) ve =M\u0130N Form\u00fcl\u00fc<\/h3>\n

    Bir say\u0131 dizininde maksimum ve minimum de\u011ferleri bulmak =MAK ve =M\u0130N\u00a0 form\u00fclleriyle \u00e7ok basittir. E4 ve E9 aral\u0131\u011f\u0131ndaki maksimum de\u011feri =MAK(E4:E9)\u00a0 form\u00fcl\u00fc ile bulabilirsiniz.<\/p>\n

    =\u015e\u0130MD\u0130 (=NOW) Form\u00fcl\u00fc<\/span><\/h3>\n

    =\u015e\u0130MD\u0130() form\u00fcl\u00fc ile bug\u00fcn\u00fcn tarihini ve saatini g\u00f6rebilirsiniz. E\u011fer daha \u00f6nceki tarihi de g\u00f6rmek isterseniz “-say\u0131” \u015feklinde g\u00f6rebilirsiniz. (=\u015e\u0130MD\u0130()-7)<\/p>\n

    =B\u0130RLE\u015eT\u0130R (=CONCATENATE) Form\u00fcl\u00fc<\/span><\/h3>\n

    =B\u0130RLE\u015eT\u0130R form\u00fcl\u00fc \u00f6zellikle metin i\u00e7eriklerinde kullan\u0131l\u0131r. =B\u0130RLE\u015eT\u0130R(E4;E9) komutunu bo\u015f bir h\u00fccreye verdi\u011finizde E4 ve E9 h\u00fccreleri o bo\u015f h\u00fccrede birle\u015fir. E\u011fer arada bo\u015fluk olmas\u0131n\u0131 isterseniz de form\u00fclde ” ” kullanman\u0131z gerekir. =B\u0130RLE\u015eT\u0130R(E4;” “;E9) \u015feklinde. A\u015fa\u011f\u0131daki \u00f6rnekte 2 ayr\u0131 h\u00fccrede TURKTICARET ve .Net yaz\u0131yor.\u00a0 Bu iki h\u00fccrenin birle\u015fmesi i\u00e7in =B\u0130RLE\u015eT\u0130R(B2;B3) form\u00fcl\u00fc sayesinde ayr\u0131 bir h\u00fccrede TURKTICARET.Net olarak birle\u015ftirebilirsiniz.<\/p>\n

    \"excel<\/p>\n

    Say Form\u00fclleri<\/span><\/h3>\n

    Say\u0131 form\u00fcllerinden biri olan =BA\u011e_DE\u011e_SAY form\u00fcl\u00fc se\u00e7ilen aral\u0131ktaki i\u00e7erikte say\u0131 i\u00e7eren ka\u00e7 adet h\u00fccre oldu\u011fu bilgisini verir. Di\u011fer bir form\u00fcl olan =BA\u011e_DE\u011e_DOLU_SAY form\u00fcl\u00fc ise say\u0131dan ba\u011f\u0131ms\u0131z olarak se\u00e7ilen h\u00fccre aral\u0131\u011f\u0131nda i\u00e7i dolu olan h\u00fccre adedini verir.<\/p>\n

    \"excel<\/p>\n

    =D\u00dc\u015eEYARA (=VLOOKUP) Form\u00fcl\u00fc<\/span><\/h3>\n

    =D\u00dc\u015eEYARA form\u00fcl\u00fc bir aral\u0131k ile tablodaki verilerin e\u015fle\u015ftirilmesi d\u00fczenlenmesi i\u00e7in kullan\u0131l\u0131r. Bu komutu kullanmak i\u00e7in 4 bilgiye ihtiya\u00e7 vard\u0131r;<\/p>\n

      \n
    1. Arad\u0131\u011f\u0131n\u0131z de\u011fer<\/li>\n
    2. Arad\u0131\u011f\u0131n\u0131z de\u011ferin bulundu\u011fu aral\u0131k<\/li>\n
    3. Arama sonucunu i\u00e7eren s\u00fctun -ka\u00e7\u0131nc\u0131 s\u00fctun oldu\u011fu-<\/li>\n
    4. Arama de\u011ferinin DO\u011eRU mu Yanl\u0131\u015f m\u0131 oldu\u011fu. Veriyi aynen almak i\u00e7in 0(s\u0131f\u0131r) yazabilirsiniz.<\/li>\n<\/ol>\n

      A\u015fa\u011f\u0131daki \u00f6rnekte \u00fcr\u00fcn takip tablosuna di\u011fer tablodaki depo ve renk bilgilerini =D\u00dc\u015eEYARA form\u00fcl\u00fcyle nas\u0131l alabilece\u011finizi a\u00e7\u0131klayal\u0131m;<\/p>\n