Formüller Ve Hücre Sabitleme

WEBVTT 00:00:01.104 --> 00:00:10.813 Jetzt hier, Freunde, 10 Stück Kugelschreiber, Stückpreis 1 TL. 00:00:12.195 --> 00:00:14.697 Ich möchte Ihnen auch zeigen, wie ich das gemacht habe. 00:00:14.817 --> 00:00:17.300 Sie sehen den Stückpreis, dieses TRL-Symbol. 00:00:17.340 --> 00:00:19.122 Ich habe es nicht manuell eingegeben. 00:00:20.663 --> 00:00:21.664 Ich habe es in Excel definiert. 00:00:21.724 --> 00:00:24.407 Wenn Sie die Zellen formatieren, wählen Sie die Währung. 00:00:25.683 --> 00:00:29.145 Sie können dann das TRL-Symbol als Symbol suchen. 00:00:29.365 --> 00:00:32.507 Sie können auch die Dezimalstellen angeben. 00:00:36.510 --> 00:00:38.151 Wir werden den Gesamtumsatz finden. 00:00:38.211 --> 00:00:40.693 Inklusive Bleistift und Radiergummi. 00:00:40.833 --> 00:00:49.778 Dann berechnen wir in einem Setup, in dem wir davon ausgehen, dass die MwSt. 18% beträgt, wie viel die MwSt. in TRL beträgt. 00:00:51.440 --> 00:00:58.246 Wenn Sie in Excel arbeiten oder eine mathematische Operation durchführen, wissen Sie, dass Einheiten sehr wichtig sind. 00:00:58.306 --> 00:01:06.374 Deshalb habe ich Sie in den Spalten D und E nicht mit dem türkischen Lira-Symbol versehen. 00:01:06.734 --> 00:01:12.980 Ich füge es als Einheit hinzu, damit unsere Erwartungen bezüglich des Ergebnisses genauer werden. 00:01:14.929 --> 00:01:19.632 Jetzt, wenn es 1 TRL und 10 Stück sind, werden wir 10 mal 1 machen, nicht wahr? 00:01:19.691 --> 00:01:21.033 Was machen wir dann hier? 00:01:21.093 --> 00:01:23.675 Wir werden eine Multiplikationsoperation durchführen, Freunde. 00:01:23.775 --> 00:01:24.696 Gleich ist mal. 00:01:24.736 --> 00:01:25.757 Wir haben eine Klammer gesetzt. 00:01:25.937 --> 00:01:27.197 Den ersten Wert ausgewählt. 00:01:27.978 --> 00:01:28.859 Ein Komma eingefügt. 00:01:29.099 --> 00:01:30.580 Unseren zweiten Wert ausgewählt. 00:01:31.541 --> 00:01:32.581 Die Klammer geschlossen. 00:01:32.700 --> 00:01:38.525 Enter gedrückt und wie Sie sehen, hat Excel mir jetzt hier 10 Euro gegeben, nicht wahr? 00:01:39.325 --> 00:01:53.317 Eine der Aktionen, die wir durchführen können, ist die Verwendung des Formatpinsels. Wie Sie sehen, habe ich auf TRL-Formatpinsel geklickt und es mir ermöglicht, dies hier in 10 TRL zu ändern. 00:01:53.357 --> 00:01:53.537 Chance hatte. 00:01:53.557 --> 00:02:06.592 Andererseits, bevor wir die Aktion durchführen, wenn wir die Zellen auswählen und sagen, dass wir die Zellen formatieren möchten, wenn wir die Währung sagen, wie wir es gerade gezeigt haben, TRL 00:02:06.652 --> 00:02:11.115 schrieb ich, wie Sie sehen, TR auf der physischen Tastatur und TRL erschien vor mir. 00:02:11.895 --> 00:02:20.323 Ich sagte OK und jetzt ist das Format dieser Zellen hier oben bereits als türkische Lira festgelegt, wie Sie bereits als Währung sehen können. 00:02:21.913 --> 00:02:26.717 Die Aktion, die ich gerade gemacht habe, zeigt hier ein Pluszeichen. 00:02:26.857 --> 00:02:32.481 Ich ziehe am Ende des Pluszeichens, weil alle Aktionen, die ich durchführen werde, Multiplikationen sind. 00:02:34.442 --> 00:02:40.606 Andererseits, wenn wir jetzt zur Mehrwertsteuer kommen, wird sich diese Aktion ein wenig ändern. 00:02:40.907 --> 00:02:41.207 Warum? 00:02:42.877 --> 00:02:54.481 TRL 1520-295 sind die Gesamtwerte, die wir hier gemacht haben, ja, alles passt wirklich zusammen, aber was haben wir getan, wir haben die Aktion nach unten gezogen. 00:02:55.161 --> 00:03:04.002 Wenn wir den Vorgang nach unten ziehen, ändern sich automatisch alle erforderlichen Zeilenwerte für den Vorgang, wenn wir ihn nach unten ziehen. 00:03:04.043 --> 00:03:11.825 Das bedeutet, das Produkt von A2 und C2, das Produkt von A3 und C3, das Produkt von A4 und C4. 00:03:13.550 --> 00:03:26.199 Wenn wir beispielsweise möchten, dass eine Zahl konstant bleibt, z. B. in einem Setup, in dem der Mehrwertsteuerwert konstant ist, um zu ermitteln, wie viel TRL die Mehrwertsteuer aus dem Gesamtverkauf beträgt, 00:03:26.899 --> 00:03:40.269 eine Möglichkeit, dies zu tun, besteht natürlich darin, das Produkt zu schreiben, 10 TRL auszuwählen und als zweiten Wert 18% hinzuzufügen. 00:03:43.260 --> 00:03:49.765 Und wenn wir Enter drücken, wird 1,80 wieder als Euro angezeigt, und wir konvertieren es in TLT. 00:03:50.265 --> 00:04:01.693 Dann, wie Sie sehen, wenn ich nach unten ziehe, wird es als C9 und C10 übernommen. 00:04:01.913 --> 00:04:03.254 Der zweite Wert hier, oder? 00:04:03.614 --> 00:04:04.435 Sie sehen. 00:04:05.696 --> 00:04:08.297 Also war %18 hier C8. 00:04:09.198 --> 00:04:11.600 C8 haben wir in E2 verwendet. 00:04:13.672 --> 00:04:16.937 Was passiert, wenn wir zu E3 und E4 kommen? 00:04:17.377 --> 00:04:20.422 Der Wert von C8 wird zu C9 und C10 verschoben. 00:04:20.462 --> 00:04:22.385 Es gibt also keinen festen Wert hier. 00:04:22.505 --> 00:04:23.968 Was wollen wir also tun? 00:04:24.267 --> 00:04:26.371 Wir wollen C8 fixieren. 00:04:27.401 --> 00:04:34.966 Jetzt, Freunde, um diesen Wert konstant zu halten, werden wir Dollarzeichen setzen. 00:04:36.527 --> 00:04:48.936 Warum fixieren wir also diese Zelle, die C-Spalte, die C-Spalte und 8, damit, wenn wir sie nach unten ziehen, in allen Operationen C8 00:04:48.996 --> 00:04:50.777 Zelle fixiert bleibt, also der Wert von 18 Prozent. 00:04:52.383 --> 00:05:02.747 Eine Möglichkeit, dies zu tun, besteht natürlich darin, manuell mit dem Dollarzeichen in diese Zelle einzugeben, eine andere Möglichkeit besteht darin, wenn Sie zu Zelle C8 gehen, die Maus zu nehmen und C8 00:05:02.787 --> 00:05:10.010 wo sich die Zelle befindet, also wenn Sie sie in einen benachbarten Bereich setzen, und dann F4 drücken, wird dieser Vorgang automatisch von Excel durchgeführt. 00:05:10.570 --> 00:05:23.510 Dann ziehen wir nach unten und wie Sie sehen können, werden die Mehrwertsteuerwerte als 0,94, 0,53 berechnet. 00:05:27.071 --> 00:05:30.413 Drücken Sie die Strg-S-Taste, um zu speichern. 00:05:30.793 --> 00:05:34.314 Wir fahren mit Zahlen und Formeln fort.

WEBVTT 00:00:01.104 --> 00:00:10.813 Now here, friends, 10 pieces of ballpoint pens, unit price is 1 TL. 00:00:12.195 --> 00:00:14.697 I also want to show you how I did this. 00:00:14.817 --> 00:00:17.300 There is this TRL sign you see for the unit price. 00:00:17.340 --> 00:00:19.122 I didn't type it manually. 00:00:20.663 --> 00:00:21.664 I defined it with Excel. 00:00:21.724 --> 00:00:24.407 When you come to format the cells, choose currency. 00:00:25.683 --> 00:00:29.145 and then you can search for TRL as a symbol. 00:00:29.365 --> 00:00:32.507 You can also specify the number of decimal places. 00:00:36.510 --> 00:00:38.151 We will find the total sales. 00:00:38.211 --> 00:00:40.693 Including the pencil and eraser. 00:00:40.833 --> 00:00:49.778 Then, let's calculate how much the VAT corresponds to in TRL in a setup where we assume the VAT is 18%. 00:00:51.440 --> 00:00:58.246 When working in Excel or performing any mathematical operation, you know units are very important. 00:00:58.306 --> 00:01:06.374 Therefore, to remind you, I did not put the Turkish lira sign in columns D and E. 00:01:06.734 --> 00:01:12.980 I put it as a unit so that our expectations about the result become more precise. 00:01:14.929 --> 00:01:19.632 Now, if it's 1 TRL and 10 pieces, we will multiply 10 by 1, right? 00:01:19.691 --> 00:01:21.033 So, what are we going to do here? 00:01:21.093 --> 00:01:23.675 We will perform a multiplication, friends. 00:01:23.775 --> 00:01:24.696 Equals multiplication. 00:01:24.736 --> 00:01:25.757 We put parentheses. 00:01:25.937 --> 00:01:27.197 We selected the first value. 00:01:27.978 --> 00:01:28.859 We placed the comma. 00:01:29.099 --> 00:01:30.580 We selected our second value. 00:01:31.541 --> 00:01:32.581 We closed the parentheses. 00:01:32.700 --> 00:01:38.525 We pressed Enter and as you can see, Excel has given me 10 euros here, right? 00:01:39.325 --> 00:01:53.317 One of the operations we can do is using the format painter. As you can see, I clicked on TRL in the format painter and changed it to 10 TRL. 00:01:53.357 --> 00:01:53.537 I had the chance. 00:01:53.557 --> 00:02:06.592 On the other hand, before performing the operation, when we select the cells and format the cells, when we say currency, as I showed you earlier, TRL 00:02:06.652 --> 00:02:11.115 I wrote as you can see TR with the physical keyboard and TRL came up, I selected TRL. 00:02:11.895 --> 00:02:20.323 I said okay and now the format of these cells is determined as Turkish lira as you can already see above as the currency. 00:02:21.913 --> 00:02:26.717 The operation I did earlier, you see this plus sign here. 00:02:26.857 --> 00:02:32.481 I'm dragging from the end of the plus sign because all the operations I will do are multiplication operations. 00:02:34.442 --> 00:02:40.606 On the other hand, now when we come to VAT, this operation will change a bit. 00:02:40.907 --> 00:02:41.207 Why? 00:02:42.877 --> 00:02:54.481 TRL 1520-295 are the total values we have here, yes, everything really adds up, but what did we do? We dragged the operation downwards. 00:02:55.161 --> 00:03:04.002 When we drag the operation downwards, all the necessary row values for the operation change automatically as we drag down. 00:03:04.043 --> 00:03:11.825 That is, the multiplication of A2 and C2, the multiplication of A3 and C3, the multiplication of A4 and C4. 00:03:13.550 --> 00:03:26.199 If we want a number to remain constant, for example, in a setup where the VAT value is constant, to find out how much VAT is in the total sales 00:03:26.899 --> 00:03:40.269 one way to do this, of course, is to write the multiplication, select 10 TRL, and add 18% as the second value. 00:03:43.260 --> 00:03:49.765 And when we press enter, if 1.80 comes as euro, we convert it to TLT. 00:03:50.265 --> 00:04:01.693 Then, as you noticed when I dragged down, it took the values as C9 and C10. 00:04:01.913 --> 00:04:03.254 The second value, right? 00:04:03.614 --> 00:04:04.435 You see. 00:04:05.696 --> 00:04:08.297 So, %18 was C8 here. 00:04:09.198 --> 00:04:11.600 We used C8 in E2. 00:04:13.672 --> 00:04:16.937 What happened when we got to E3 and E4? 00:04:17.377 --> 00:04:20.422 The value of C8 shifted to C9 and C10. 00:04:20.462 --> 00:04:22.385 So, there is no written value here. 00:04:22.505 --> 00:04:23.968 Then what do we want to do? 00:04:24.267 --> 00:04:26.371 We want to fix C8. 00:04:27.401 --> 00:04:34.966 Now, friends, in order for this value to remain constant, that is, for our VAT value to remain constant, we will put dollar signs. 00:04:36.527 --> 00:04:48.936 Why are we fixing this to column C, column C, and 8, so that when we drag it down, in all operations, C8 00:04:48.996 --> 00:04:50.777 cell remains fixed, meaning the value of 18 percent. 00:04:52.383 --> 00:05:02.747 One way to do this, of course, is to manually enter this dollar sign, and another is when you get to the C8 area, you can click on the mouse and when you put it in a neighboring area, 00:05:02.787 --> 00:05:10.010 when you press F4, this operation is automatically performed by Excel. 00:05:10.570 --> 00:05:23.510 Then we drag it down and as you can see, VAT values of 0.94, 0.53 are calculated. 00:05:27.071 --> 00:05:30.413 We save with the Ctrl-S key. 00:05:30.793 --> 00:05:34.314 We continue with numbers and formulas.

WEBVTT 00:00:01.104 --> 00:00:10.813 Şimdi burada arkadaşlar, tükenmez kalem 10 adet, adet fiyatı 1 TL. 00:00:12.195 --> 00:00:14.697 Bunu nasıl yaptığımı da size göstermek istiyorum. 00:00:14.817 --> 00:00:17.300 Adet fiyatı gördüğünüz bu TRL işareti var. 00:00:17.340 --> 00:00:19.122 Ben bunu elimle girmedim. 00:00:20.663 --> 00:00:21.664 Excel ile tanımladım. 00:00:21.724 --> 00:00:24.407 Hücreleri biçimlendire geldiğinizde para birimi. 00:00:25.683 --> 00:00:29.145 seçip daha sonra simge olarak TRL'yi arayabilir. 00:00:29.365 --> 00:00:32.507 Ondalık basamak sayısını da belirtebilirsiniz. 00:00:36.510 --> 00:00:38.151 Toplam satışı bulacağız. 00:00:38.211 --> 00:00:40.693 Kurşun kalem ve silgiyle dahil olmak üzere. 00:00:40.833 --> 00:00:49.778 Ondan sonra bir de KDV'nin %18 olduğunu varsaydığımız bir düzenekte KDV'sinin ne kadara TRL karşılığında geldiğini hesaplayalım. 00:00:51.440 --> 00:00:58.246 Excel'de çalışırken veya herhangi bir matematiksel işlem yaparken biliyorsunuz birimler çok önemli. 00:00:58.306 --> 00:01:06.374 Bu sebeple size hatırlatmak adına D ve E kolonlarında ben Türk lirası işaretini koymamıştım. 00:01:06.734 --> 00:01:12.980 Birim olarak koyuyorum ki buraya sonucun ne olacağına dair beklentimizi daha kesin bir hale alsın. 00:01:14.929 --> 00:01:19.632 Şimdi 1 TRL ve 10 adet ise 10 çarpı 1 yapacağız değil mi? 00:01:19.691 --> 00:01:21.033 Burada o zaman ne yapacağız? 00:01:21.093 --> 00:01:23.675 Çarpım işlemi yapacağız arkadaşlar. 00:01:23.775 --> 00:01:24.696 Eşittir çarpım. 00:01:24.736 --> 00:01:25.757 Parantez yaptık. 00:01:25.937 --> 00:01:27.197 Birinci değeri seçtik. 00:01:27.978 --> 00:01:28.859 Virgül'ü koyduk. 00:01:29.099 --> 00:01:30.580 İkinci değerimizi seçtik. 00:01:31.541 --> 00:01:32.581 Parantez'i kapattık. 00:01:32.700 --> 00:01:38.525 Enter'a bastık ve gördüğünüz gibi şu anda Excel bana 10 euro olarak burayı verdi değil mi? 00:01:39.325 --> 00:01:53.317 Yapabileceğimiz işlemlerden bir tanesi biçim boyacısını kullanarak Gördüğünüz gibi TRL'ye biçim boyasına tıkladım ve 10 TRL şeklinde burayı değiştirme 00:01:53.357 --> 00:01:53.537 şansım oldu. 00:01:53.557 --> 00:02:06.592 Diğer bir yandan işlemi gerçekleştirmeden önce hücreleri seçip Hücreleri biçimlendir dediğimizde para birimi dediğimizde az önce gösterdiğimiz gibi TRL 00:02:06.652 --> 00:02:11.115 yazdım gördüğünüz gibi TR fiziksel klavye ve geldi karşıma TRL'yi seçtim. 00:02:11.895 --> 00:02:20.323 Tamam dedim ve şu anda bu hücrelerin biçimi Türk lirası olarak burada yukarıda zaten görebiliyorsunuz para birimi olarak şu anda belirlendi. 00:02:21.913 --> 00:02:26.717 Az önce yaptığım işlemi aşağıya şu artı işareti çıkıyor burada görüyorsunuz. 00:02:26.857 --> 00:02:32.481 Artı işaretin ucundan tutarak çekiyorum çünkü bütün yapacağım işlemlerin hepsi çarpma işlemi. 00:02:34.442 --> 00:02:40.606 Diğer bir yandan şimdi KDV'ye geldiğimizde bu iş biraz değişecek. 00:02:40.907 --> 00:02:41.207 Neden? 00:02:42.877 --> 00:02:54.481 TRL 1520-295 bizim burada yaptığımız toplam değerler evet gerçekten her şey birbirine tutuyor fakat ne yapmıştık biz işlemi aşağı doğru sürüklemiştik. 00:02:55.161 --> 00:03:04.002 İşlemi aşağı doğru sürüklediğimiz zaman işlem için gerekli bütün satır değerleri de otomatik olarak biz aşağıya çektikçe değişiyor. 00:03:04.043 --> 00:03:11.825 Yani A2 ve C2 çarpımı, A3 ve C3 çarpımı, A4 ve C4 çarpımı. 00:03:13.550 --> 00:03:26.199 Eğer biz bir rakamın sabit kalmasını istiyorsak yani KDV değerinin mesela sabit olduğu bir düzenekte toplam satışın içerisinden KDV'nin kaç TRL olduğunu 00:03:26.899 --> 00:03:40.269 bulurken bunu yapmanın yollarından bir tanesi tabii ki çarpım yazıp 10 TRL'yi seçip ikinci değer olarak yüzde 18'i eklemek. 00:03:43.260 --> 00:03:49.765 Ve enter'a bastığımızda 1.80 yine euro olarak geldiği durumda TLT'ye çeviriyoruz. 00:03:50.265 --> 00:04:01.693 Sonra aşağı doğru çektiğimde fark ettiğiniz gibi C9 ve C10 olarak değer aldı. 00:04:01.913 --> 00:04:03.254 İkinci değeri değil mi burada? 00:04:03.614 --> 00:04:04.435 Görüyorsunuz. 00:04:05.696 --> 00:04:08.297 Yani %18 burada C8'di. 00:04:09.198 --> 00:04:11.600 C8'i biz E2'de kullandık. 00:04:13.672 --> 00:04:16.937 E3'e ve E4'e geldiğimizde ne oldu? 00:04:17.377 --> 00:04:20.422 C8 değeri C9 ve C10 olarak kaydı. 00:04:20.462 --> 00:04:22.385 Yani burada yazılı bir değer yok. 00:04:22.505 --> 00:04:23.968 O zaman biz ne yapmak istiyoruz? 00:04:24.267 --> 00:04:26.371 C8'i sabitlemek istiyoruz. 00:04:27.401 --> 00:04:34.966 Şimdi arkadaşlar bu değerin sabit kalabilmesi için yani KDV değerimizin sabit kalabilmesi için biz dolar işaretleri koyacağız. 00:04:36.527 --> 00:04:48.936 Neden bunun işte kolon, kolon C kolonu olduğu için C kolonu ve 8 yani o hücreyi biz buraya sabitleyeceğiz ki aşağı doğru çektiğimizde tüm işlemlerde C8 00:04:48.996 --> 00:04:50.777 hücresi sabit kalsın yani yüzde 18 değeri. 00:04:52.383 --> 00:05:02.747 Bunu yapmanın yollarından bir tanesi tabi manuel olarak el yordamıyla bu dolar işaretine girmek bir tanesi de C8 alanına geldiğinizde mouse'u alıp C8'in 00:05:02.787 --> 00:05:10.010 bulunduğu yani birbirinden sonraki bir alana koyduğunuzda F4'e bastığınızda bu işlem otomatik olarak Excel tarafından gerçekleştiriliyor. 00:05:10.570 --> 00:05:23.510 Daha sonra alıp aşağıya sürüklüyoruz ve Gördüğünüz gibi 0.94, 0.53 olarak KDV değerleri hesaplanmış oluyor. 00:05:27.071 --> 00:05:30.413 Ctrl-S tuşuyla save ediyoruz. 00:05:30.793 --> 00:05:34.314 Sayılar ve formüllerle devam ediyoruz.