Excel'de Formülde Hücreleri Sabitlemek
Excel’de Hücreler Neden Sabitlenir?
Excel’in asıl alametifarikası, bir matris mantığı ile hücrelerden oluşması, bu sayede de referans hücre bazlı tekrarlı işlemlerde herhangi bir kod bilgisi gerektirmeden sürükle uzat işlemi ile tüm işlemleri tekrarlayabilmesidir. Ancak bu yazının konusu olan ve “hücre sabitlemek” olarak sıkça kullanılan excel ifadesine temelde neden ihtiyaç duyulduğunu bir örnek üzerinden inceleyelim.
Örnek problem: Bir işletmenin 3 farklı ürünü vardır ve 2016 yılı için aylık satış adetleri excel ortamında saklanmaktadır. Ancak işletme ürünlerini dolar kuru üzerinden satmaktadır. 3 farklı ürünün fiyatının yıl boyunca değişmediği ve dolar kurunun da yıl boyunca 3.25 TL’ye sabitlendiği veriler ile birlikte 2016 yılı içn aylık cirolarını TL para cinsinden hesaplayınız.
Benzer problemlerin çözümü için excel kesinlikle en pratik platform olma özelliği taşımaktadır. Ancak köşesinden sürükleyip bırakarak çözüme ulaşmamız bu sefer pek mümkün değil. Fikir vermesi açısından örnek sonuç tablomuz, aşağıdaki gibi bir çıktıya sahip olmalıdır.
Satış sayılarını, ürün fiyatlarını ve sabit dolar kurunu bildiğimiz için sonuç kağıt üstünde oldukça kolay hesaplanabilir olarak görünüyor:
TL Cinsinden Ciro=Dolar Kuru x Satış Sayısı x Fiyat
İyi güzel ama biz bu formülü ilk hücreye yazıp sürükle-uzat yaptığımızda referans değerlerimiz yanlış yerlere kayıyor. Örneğin satış sayıları uzatmaya müsait iken dolar kurunun sabit olduğu yerde kalması gerekiyor.
Örneğin 1. Ürünün 1 ay hesaplamaları gayet güzel, ancak;
Ancak iş 1. Ürünün 2. Ay satışlarına gelince bozuluyor. Burada hatalı olan 2 şey var. Birincisi 1. Ürünün fiyatının G2 hücresinde kalması, ikincisi ise dolar kurunun tamamen J2 hücresinde kalması gerekiyor.
Problemin çözümü de, hücre sabitleme işlemini anlayabilmemiz için bize yol gösterici olacak.
Amaç: Formülü öyle bir güncellemeliyiz ki, biz B18 hücresinde yazdığımız formülü D29 hücresine kadar sürüklediğimizde referanshücreler doğru yerlerde kalsın.
Excel’de Formüldeki Hücreler Nasıl Sabitlenir?
Excel’de sütun sabitleme işlemi $ karakteri ile gerçekleştirilmektedir. Üstelik referans bir hücrenin sürüklenip uzatılması işleminde, sağa/aşağı/her ikisi birden sabit kalıp kalmayacağına bu şekilde izin verebilmek de mümkündür (yazının sonunda daha kolay bir yöntem de paylaşacağım).
Yapılacak işlem: Formülde sabit kalması istenen satır ya da sütununsol tarafına $ işareti koymak.
Klavye kısayolu: F4 tuşu ya da Alt Gr+4
Ezber sevenler için, eğer referans bir hücrenin sağa ya da sola kaymasını istemiyorsanız, harfin sol tarafına $ işareti koyunuz.
A7 -> $A7
Eğer bir hücrenin yukarı aşağı kaymasını istemiyorsanız ise sayının sol tarafına $ işareti koyunuz.
Eğer hücrenin ne sağa-sola, ne de yukarı aşağı kaymasını istemiyorsanız (tamamen sabit) hem harfin hem de sayının sol tarafına $ işaeti koyunuz.
Konuya Excel’de çarpım tablosu yapımı örneği ile devam edelim.
Hücreler bir harf ve bir sayıdan oluşacak şekilde oluşurlar. Örneğin çarpım tablosunda 9x9=81 değeri J sütununun 10. satırı olan J10 hücresinde bulunmaktadır. Referans aldığı 9 ve 9 değerleri ise ilk 9 değeri J1 hücresinde, ikincisi ise A10 hücresinde bulunmaktadır. Dolayısıyla biz çarpım tablosunu hazırlarken referans hücreler olan 1-9 arası değerleri sabitlemeliyiz. Bu alanlardan ilki B1:J1 arasında olan değerlerdir ve aşağı doğru kaymamaları ama sağa doğru kayablmeleri gerekmektedir.
Kolay olan örneğimiz üzerinden hücre sabitleme mantığı anlaşıldıysa, hem pratik yapmak, hem de tamamen sabit hücreler için hücreye isim vererek daha pratik bir yol öğrenmak için işletme örneğimize dönelim.
1. ürün için yukarıda öğrendiğimiz sabitleme işlemleri çok güzel çalışıyor. Ancak ürün fiyatları için ne yapacağız?
Bunun uzun yolu, dolar bazındaki ürün fiyatları tablosunun formunu değiştirmek olabilir. Böylece:
Ancak problemleri, yeni bir şey öğrenebilmek için fırsat olarak görenler için bu çözüm yeterli olmayacaktır. İdeal çözümlerden birisi VLOOKUP ya da HLOOKUP (DÜŞEYARA ya da DİKEYARA) formüllerini kullanmak olabilir. Ancak bir diğer pratik çözüm ise tabloları dikey ya da yatay formdan diğerine çevirebildiğimiz TRANSPOSE işlemi olabilir. Bu formüllerin kullanımını başka bir yazıya saklayarak son bir kolaylıktan bahsetmek istiyorum, hücreye ya da tabloya isim vererek formüllerde kullanmak ve referans gösterebilmek.
Örneğin işletme örneğimizde dolar kurunun nasıl olsa sabit olduğunu biliyoruz. Peki biz neden her dolar işlemi referansında $J$2 hücresini referans gösterdik? Daha büyük projelerde dolar desek de gelseydi olmaz mıydı? Buyurun deneyelim:
Daha önce J2 olarak görüntülenen hücre alanına DOLAR yazdım. Artık hesaplamalarımda da dolar kurunu sadece DOLAR diye çağırabilirim. Üstelik sabitleme derdi de ortadan kalktı.
Excel'de Hücreye Verilen Adın Kaldırılması
Uzun vadeli excel projelerinde bir hücreye ya da tabloya verilen adın değiştirilmesi ya da kaldırılması gerekebilir. Bu işlem için Formulas menüsü altında define name’e tıkladığınızda, daha önce tanımladığınız tüm isimlerin burada olduğunu göreceksiniz.
Bu makale 58943 kez okundu
excel istanbul tarafından 26.10.2017 tarihinde yazıldı
excelin en çok kullandığım fakat en çok unuttuğum işlemi. her seferinde unutuyorum tekrar bakmak zorunda kalıyorum akılda kalıcı olması için bir öneriniz var mı? yoksa sağa mı sola mı ikisine birden mi kısmı beni çok yoruyor. kısa yol koyulsa daha iyi olur sanki.
adamlar önce tabloda formül yazılabileceği fikrini bulmuşlar, yetmemiş bu formülü bir alttaki ya da yandaki hücreye de ekleyebileceğini ve bir kereden daha fazla yazmaya gerek kalmayacağını akıl etmişler. üstüne de yazdığın bir formülü her ne şartta olursa olsun referans hücrelerle birlikte işleme sokabilmeyi akıl etmişler, hatta demişler ki referans hücreler de aynı hizada ya da sırada olmasa da olur, kullanıcı bunu sabitleyebilsin. bence bilgisayar denen cihazı mantıklı ve işlevsel yapan sayılı yazılımdan birisidir excel
Excel'de hücre sabitleme işlemleri üçe ayrılıyor. Bunlardan ilki Relative Reference (Göreceli Referans), ikincisi Absolute Reference (Mutlak referans), üçüncüsü ise Mixed Reference (Karışık Referans). Türkçe karşılıklarını ben çevirdim ancak aşağı yukarı mantık olarak bu şekilde çevirilmesi doğru olur diye düşünüyorum.
1. Relative Reference (Göreceli Referans), Excel'in standart olarak kullandığı hücre sabitleme şekli, çekip uzattığınızda referans hücrelerin de kayması işlemi bu oluyor
2. Örnekteki dolar kuru Absolute Reference (Mutlak referans)'a güzel bir örnek çünkü hem sağdan/soldan; hem de üstten ve alttan sabit. Tam anlamıyla her yerden sabit olan hücre referansları bu şekilde adlandırılıyor.
3. Duruma göre yatay olarak ya da dikey olarak sabitlenmesi ama çekip uzatıldığında tek bir eksene doğru referans hücrelerin de kayabilmesini sağlayan hücre sabitleme tipi ise Mixed Reference (Karışık Referans) olarak adlandırılıyor.
sabitleme işleminin adını da bilmeyip dert anlatmaya çalışmak çok zormuş google a aratmadığım soru cümlesi kalmadı. neyse ki artık her şey daha akıllı bunu mu demek istediniz diyerek beni doğru yola getirdi. artık unutmamak üzere hücre sabitlemesi yapabilirim.
yazar da hücreye ya da tabloya isim verilebileceğinden bahsetmiş zaten, ama son x değerin dikkate alınması gereken durumlarda (o güne kadarki ortalamayı almak mesela) tüm tabloyu seçmek değil sadece bir yeri sabitlenmiş şekilde seçim yapmak gerekiyor
tablosal verilerde tabloya komple olarak isim vermek dolar işaretleri ile uğraşmaktan çok daha kolay, örneğin bir personel tablonuz mu var, hepsini seçin ve sol üstteki alandan bu tabloya personel adı verin olsun bitsin
bazı şeyler denemeden anlaşılamaz, excel uygulamaları da okudukça ne kadar anlamsız görünürse görünsünler ufak bir uygulama ve hücre çekip uzatma işleminden sonra bir anda net olarak anlaşılmış olur
örneklendirerek anlatım çok faydalı oluyor özellikle de örnekler kolay anlaşılabiliyorsa. yazınız bu anlamda çok iyi devamını bekliyorum.
kısayolu da F4 tuşudur ve F4'e her basışta sabitleme şekli değişir, ene ya da boya doğru sabitleme (satır/sütun da denebilir) seçenekleri arasında switch ederek ihtiyacınıza göre nasıl sabitleme yapmak istediğinizi seçebilirsiniz
excel'in en mükemmel özelliğinin stabil olarak çalışabilmesinin arkasında işte bu hücre sabitleme tekniği yatıyor, tutup uzatma deyimi excel'den sonra hayatımıza girdiğine göre tutup uzattığımızda nelerin değişip nelerin sabit kalacağını seçebilmemizi sağlayan özellik, dolar simgeli olmasa daha da güzel olurdu ama olsun