1.5 Hücre Sabitleme ve Farklı Çalışma Sayfası/Kitabından Veri Getirme
Excel’de bir hücrede yazılmış olan formül diğer satırlara doldurma tutamacının kaydırılmasıyla kopyalanırken her satır inişte hücre başvurularının numaraları birer artırır. Örneğin yukarıdaki tabloda C2 hücresine yazılan formül =EĞER(B2>=2000;B2*5%;””) şeklindeydi. Bu formül bir alt satıra kopyalandığında C3 hücresindeki formül =EĞER(B3>=2000;B3*5%;””) olur. Kopyalama sonrası B2 yerine B3 geldiğine dikkat edin.
Excel’de bir formül kopyalanırken bazen hücre başvuru numarasının değişmeden sabit kalması istenir. Böyle bir durumda hücrenin sabitlenmesi gerekir. Excel’de bir hücreyi sabitlemek için $ simgesi kullanılır. Örneğin B2 hücresini sabitlemek için formülde B2 yerine $B$2 yazılır. Diğer bir yolla, hücre adı formüle yazıldığında F4 tuşuna basılırsa sütun harfinin başına ve satır numarasının başına otomatik olarak $ simgesi gelir. Yani, formül yazarken fare ile B2 hücresine tıkladıktan sonra F4 tuşuna tıklanırsa formüle otomatik olarak $B$2 ifadesi eklenmiş olur.
Bir üniversite, farklı bölümlerde okuyan öğrencilere başarı durumlarına göre burs veriyor. Burs oranları, öğrencinin not ortalamasına (GPA) göre belirleniyor ve bu oranlar başka bir sayfada saklanıyor.
• Öğrenci bilgileri ve not ortalamaları bir Excel dosyasında Sayfa 1’de aşağıdaki gibi verilmiştir.
- Şekil 1.1.53: Öğrenci Bilgileri ve Not Ortalaması Tablo
• Not aralıklarına göre burs oranları ise Sayfa 2’de aşağıdaki gibi verilmiştir.
- Şekil 1.1.54: Öğrenci Not Aralıklarına Göre Burs Oranları
Bu tabloya göre notu 3,5 ve üzeri olan %100 burs alır, 3,0 ve üzeri olanlar %75, 2,5 ve üzeri olanlar %50 burs alır, 2,5 altı ise burs alamaz.
Şimdi, Sayfa 1’deki C sütununa öğrencilerin hangi oranda burs alacaklarını belirleyen formülü oluşturalım:
1. Önce Excel dosyanızda yukarıda Sayfa1 ve Sayfa 2’de verilen tabloları iki ayrı sayfada oluşturun.
Sayfa 1’in adını ÖğrenciVerileri olarak, Sayfa 2’nin adını ise BursOranı olarak değiştirin.
2. Sonra C2 hücresine
=EĞER(B2>=BursOranı!$A$2;”%100″;EĞER(B2>=BursOranı!$A$3;”%75″;EĞER(B2>=BursOranı!$A$4;”%50″;”%0″)))
formülünü yazın.
Bu formülü oluştururken, =EĞER(B2>= yazdıktan sonra BursOranı sayfasına geçin, A2 hücresine ardından F4 tuşuna tıklayın. Noktalı virgül işretine tıkladıktan sonra formülün son durumu =EĞER(B2>=BursOranı!$A$2; biçiminde olacaktır. Burada BursOranı sayfasına geçmemizin sebebi, bu sayfadaki veriyi ÖğrenciVerileri sayfasındaki formülde kullanmaktır. Formülün devamını oluştururken de aynı işlemleri uygulamalısınız.
3. Formülü C3:C8 aralığına kopyalayın. Hesapladığınız burs oranları aşağıdaki tablodaki gibi olmalıdır.
- Şekil 1.1.55: Burs Oranları Hesaplama Ekranı
İkinci aşamada formülü yazarken F4 tuşuna basarak hücre sabitleme yapmasaydık ne olurdu?
• C2 hücresine yazdığımız formülümüz
=EĞER(B2>=BursOranı!A2;”%100″;EĞER(B2>=BursOranı!A3;”%75″;EĞER(B2>=BursOranı!A4;”%50″;”%0″)))
biçiminde olurdu.
• Formülde A2, A3 ve A4 hücrelerini sabitlemediğimiz için Burs oranı sayfasından kullandığımız veriler formül C3:C8 aralığına kopyalanırken her satır inişte bir alt satıdaki veri kullanılacağı için aşağıdaki gibi yanlış sonuçların olduğu bir tabloyla karşılaşırdık.
- Şekil 1.1.56: İkinci Aşamada Hücre Sabitleme Yapılmamış Hatalı İşlem Sonucu
C2 hücresindeki formülü aşağıdaki hücrelere (C3:C8 aralığına) kopyaladığımızda, örneğin C6 hücresindeki formül =EĞER(B6>=BursOranı!A6;”%100″;EĞER(B6>=BursOranı!A7;”%75″;EĞER(B6>=BursOranı!A8;”%50″;”%0″)))
biçiminde olur. BursOranı!A6 yani BursOranı sayfasındaki A6 hücresindeki değer sıfır olduğundan formülde yazdığımız ilk koşul olan B6>=BursOranı!A6 koşulu hemen sağlanır. (Çünkü B6 da yazan 2 değeri 0’dan büyüktür.) Dolayısıyla burs kazanamamış olan Orhan Avcı bu durumda %100 burs kazanmış görünmektedir.
Excel dosyamızda yer alan BursOranı sayfasındaki veriler, başka bir Excel çalışma sayfasından da alınabilirdi. Örneğin yeni bir Excel çalışma kitabı açın. BursOranı adıyla kaydedin. Yeni çalışma kitabına önceki çalışma sayfanızda yer alan burs oranı verilerini A1:B5 aralığına kopyalayın. Önceki Excel dosyasına geçip C sütununda hesapladığınız oranları silin ve C2 hücresinde aşağıdaki formülü oluşturun.
=EĞER(B2>=[BursOranı.xlsx]Sayfa1!$A$2;”%100″;EĞER(B2>=[BursOranı.xlsx]Sayfa1!$A$3;”%75″;EĞER(B2>=[BursOranı.xlsx]Sayfa1!$A$4;”%50″;”%0″)))
Formülü oluştururken =EĞER(B2>= yazdıktan sonra BursOranı dosyasına geçip A2 hücresine tıkladığınızda formül =EĞER(B2>=[BursOranı.xlsx]Sayfa1!$A$2 biçimini alır. Burada hücre sabitleme otomatik olarak gerçekleşir, F4 tuşuna basılması gerekmez.
Aynı işlemlerle ilerleyerek formülü tamamladıktan sonra Enter’a tıklayın. Ardından formülü C3:C8 aralığına kopyaladığınızda burs oranları düzgün bir şekilde hesaplanır.
Önceki çalışma kitabınızda ya da yeni oluşturduğunuz BursOranı adlı çalışma kitabındaki burs oranları değiştiğinde hesapladığınız burs oranları da otomatik olarak değişir.