Sayılar Ve Formüller İlerletilmiş

WEBVTT 00:00:02.219 --> 00:00:11.107 Freunde, in Übung 11 werden einige Formeln auf fortgeschrittenem Niveau definiert, die ich Ihnen beibringen werde. 00:00:12.087 --> 00:00:16.732 Ich habe die Formeln hier eingegeben, damit ich sie Ihnen zeigen und erklären kann. 00:00:18.154 --> 00:00:26.722 Ich bitte Sie, die Formeln einzugeben, die ich Ihnen gezeigt habe, in das Formelfeld einzugeben, wir werden es zusammen machen. 00:00:28.158 --> 00:00:30.920 Lassen Sie uns zunächst erklären, was wir tun. 00:00:31.301 --> 00:00:38.987 Jetzt für den Gesamtumsatz am Montag, Dienstag, Mittwoch, Donnerstag, Freitag und Samstag. 00:00:40.468 --> 00:00:42.729 Das heißt, für die Arbeitstage der Woche. 00:00:43.571 --> 00:00:45.072 Sonntag ist ein Ruhetag. 00:00:45.453 --> 00:00:52.418 Hier sehen Sie eine angepasste Zahlenfolge. 00:00:53.159 --> 00:00:54.140 Verkaufsmengen. 00:00:54.770 --> 00:01:02.696 Sie sehen, dass wir das Kommaformat anwenden und dass es zweistellige Dezimalwerte gibt. 00:01:04.437 --> 00:01:12.303 Und eine unserer Aufgaben ist, dass ein Bonus gezahlt wird, wenn der wöchentliche Umsatz über 10.000 liegt. 00:01:13.904 --> 00:01:17.247 Wir hatten gelernt, die Summe zu berechnen. 00:01:17.907 --> 00:01:23.471 Für die Summe verwenden wir die Addition und die Formel ist hier bereits sichtbar. 00:01:25.330 --> 00:01:25.950 Was machen wir? 00:01:26.090 --> 00:01:27.031 Wir sagen "Gleich". 00:01:27.812 --> 00:01:29.773 Wir schreiben "Summe", öffnen eine Klammer. 00:01:29.813 --> 00:01:36.438 Dann nehmen wir die Zahlenwerte von Montag bis Samstag und drücken die Eingabetaste. 00:01:36.498 --> 00:01:39.000 Wie Sie sehen können, ist der Wert hier erschienen. 00:01:40.200 --> 00:01:46.105 Wenn wir von der Gesamtanzahl sprechen, gibt es hier zwei Methoden. 00:01:46.801 --> 00:01:54.303 Eine davon zählt ein Feld, in dem Text eingegeben wird, also versuchen wir herauszufinden, dass es 7 Tage in der Woche gibt. 00:01:54.823 --> 00:02:02.844 Die andere zählt nur die numerischen Werte, also versuchen wir herauszufinden, dass es 6 Arbeitstage in der Woche gibt. 00:02:03.704 --> 00:02:19.460 Die Formel lautet also Freunde, verbinden, also Ba Wert zählen, also ich weiß nicht, was die Abkürzung dafür war, aber unser Ziel hier ist die Gesamtanzahl, also das englische Wort "count". 00:02:19.600 --> 00:02:22.241 Was machen wir? 00:02:24.603 --> 00:02:25.284 Wir sagen "gleich". 00:02:25.844 --> 00:02:26.845 Was war die Formel für "Summe"? 00:02:29.286 --> 00:02:32.649 Schauen wir noch einmal. 00:02:32.689 --> 00:02:33.529 Ba Wert zählen. 00:02:38.453 --> 00:02:45.983 Wir haben Ba Werte in Klammern gesetzt. 00:02:46.623 --> 00:02:48.085 Wir haben alle Felder ausgewählt. 00:02:48.885 --> 00:02:51.447 Wenn Sie Enter drücken, erscheint die Zahl 6. 00:02:52.348 --> 00:02:54.089 Von B2 bis H2. 00:02:56.731 --> 00:03:02.276 Wenn wir von Ba D gefüllt zählen sprechen, zählen wir alle Zellen, die gefüllt sind. 00:03:03.276 --> 00:03:08.180 Hier sagen wir "gleich". 00:03:08.861 --> 00:03:03.276 Ba D gefüllt zählen. 00:03:17.852 --> 00:03:21.877 Wir haben alle Werte von B2 bis H2 in die Klammer gesetzt. 00:03:21.978 --> 00:03:25.263 Wenn Sie Enter drücken, also die Eingabetaste, erscheint die Zahl 7. 00:03:25.943 --> 00:03:30.020 Wir verwenden den Durchschnitt für den Durchschnitt. 00:03:30.581 --> 00:03:32.861 Wir haben den Durchschnitt in Klammern gesetzt. 00:03:33.701 --> 00:03:36.022 Wir wählen nur numerische Werte für den Durchschnitt aus. 00:03:36.082 --> 00:03:41.723 Ich denke, wir alle wissen bereits mathematisch, dass Textwerte nicht in den Durchschnitt einfließen. 00:03:43.443 --> 00:03:45.524 Wie Sie sehen, haben wir auch den Durchschnittswert erhalten. 00:03:45.784 --> 00:03:47.364 Für den Maximalwert... 00:03:49.924 --> 00:03:51.085 Wir schreiben nicht Max. 00:03:51.565 --> 00:03:54.165 Für das Maximum, lassen Sie uns hier sogar auf Deutsch korrigieren. 00:03:54.505 --> 00:03:57.166 Das deutsche Wort für Maximum wird genau so geschrieben. 00:03:58.226 --> 00:04:01.527 M-A-K-S-I-M-U-M Maximum. 00:04:03.128 --> 00:04:09.071 Deshalb schreiben wir MAX. 00:04:11.092 --> 00:04:12.552 Wir wählen unsere Werte aus. 00:04:12.632 --> 00:04:29.238 Wir schließen die Klammer von B2 bis G2 und drücken die Eingabetaste. Hier haben wir den maximalen Wert, wie Sie sehen, 927, 1890, 1756, 2105, 1672, 3019. 3019.33 00:04:29.298 --> 00:04:33.000 ist hier. 00:04:36.942 --> 00:04:40.803 Das deutsche Wort für Minimum ist Minimum, deshalb verwenden wir MIN. 00:04:41.765 --> 00:04:42.325 Wir nehmen es. 00:04:42.785 --> 00:04:54.712 Erneut schließen wir von B2 bis G2 und drücken die Eingabetaste, und wie Sie sehen, ist der kleinste Wert 927.30. Für die Dezimalrundung werden wir die RUNDEN-Funktion verwenden. 00:04:55.993 --> 00:05:11.647 Beim Verwenden der RUNDEN-Funktion müssen wir darauf achten, für wie viele Dezimalstellen wir den Bereich runden möchten. 00:05:11.707 --> 00:05:21.675 Zum Beispiel, wenn wir runden, nehmen wir die Gesamtzahl. 00:05:21.675 --> 00:05:31.824 1137586 Dann habe ich eine 1 für eine Dezimalstelle geschrieben. 00:05:32.065 --> 00:05:34.307 Was ist passiert, als ich Enter gedrückt habe? 00:05:34.307 --> 00:05:40.436 11375.86 Es wurde auf 11375.90 gerundet. 00:05:40.956 --> 00:05:43.798 Beim Runden wird aus der 6 eine 8 oder 9. 00:05:43.938 --> 00:05:45.319 Was kann ich also tun? 00:05:45.719 --> 00:05:53.785 Da die 0 hier unnötig ist, kann ich die Zehnerstelle entfernen, indem ich eine Zahl vom Wert abziehe. 00:05:55.346 --> 00:05:57.247 Lassen Sie uns ein weiteres Beispiel geben. 00:05:57.587 --> 00:05:58.327 Angenommen. 00:05:58.327 --> 00:06:11.144 Lassen Sie uns eine Dezimalzahl wie 11375.658974 schreiben. 00:06:11.363 --> 00:06:12.144 Was machen wir jetzt? 00:06:12.565 --> 00:06:16.786 Lassen Sie uns die Zahl runden. 00:06:17.766 --> 00:06:18.186 Nicht. 00:06:18.766 --> 00:06:23.628 Lassen Sie uns zum Beispiel für 3 Dezimalstellen runden. 00:06:25.137 --> 00:06:27.879 Wie Sie sehen, sind es 3 Dezimalstellen. 00:06:28.099 --> 00:06:30.561 Ich nehme die gleiche Formel und setze sie darunter. 00:06:31.142 --> 00:06:34.545 Dann schreibe ich anstelle von 3 wieder eine 1 in die Formel. 00:06:34.825 --> 00:06:36.666 Wie Sie sehen, ist der Dezimalwert hier. 00:06:38.408 --> 00:06:43.412 Die Zahl, die ich nach dem Komma eingebe, wird zur Stelle. 00:06:43.592 --> 00:06:46.574 Wir runden entsprechend. 00:06:46.594 --> 00:06:49.717 Nun, die Information, die uns bezüglich der Funktion gegeben wird, lautet wie folgt. 00:06:51.707 --> 00:06:54.928 Wenn der wöchentliche Umsatz über 10.000 TL liegt, wird ein Bonus gewährt. 00:06:55.008 --> 00:07:15.578 Jetzt, um die Funktion verwenden zu können, benötigen wir die Kriterien, die diese Situation erzeugen, also die Bedingung und folglich den Bonuswert oder keinen Bonus. 00:07:18.743 --> 00:07:22.465 Deshalb müssen wir die Werte für 10.000 und Bonus in einer Zelle erkennen. 00:07:22.585 --> 00:07:24.587 Deshalb habe ich 10.000 hier geschrieben. 00:07:25.287 --> 00:07:26.608 Ich habe den Bonus geschrieben. 00:07:26.928 --> 00:07:28.049 Ich habe keinen Bonus geschrieben. 00:07:29.249 --> 00:07:30.930 Wenn wir zur Formel kommen, ist es gleich. 00:07:31.091 --> 00:07:36.734 Wenn wir in der Klammer C6 haben, also was ist C6? 00:07:39.295 --> 00:07:40.476 Wir schauen uns C6 an. 00:07:40.536 --> 00:07:41.037 Summe. 00:07:41.457 --> 00:07:45.059 Wir haben auch das Gleiche mit B6 gemacht. 00:07:47.059 --> 00:07:55.823 Wenn C6 größer als D13 ist, also größer als 10.000, gibt es entweder einen Bonus oder keinen Bonus. 00:07:56.023 --> 00:07:59.384 Je nachdem, ob diese Bedingung erfüllt ist oder nicht. 00:07:59.664 --> 00:08:23.034 Also, wenn B6 in dieser Situation insgesamt mehr als 10.000 ist, an einem Tag. 00:08:24.876 --> 00:08:26.737 Wenn der Wert richtig ist, gibt es einen Bonus. 00:08:28.038 --> 00:08:29.999 Wenn der Wert falsch ist, gibt es keinen Bonus. 00:08:31.240 --> 00:08:32.961 Also, wenn es mehr als 10.000 ist, gibt es einen Bonus. 00:08:33.301 --> 00:08:34.622 Wenn es nicht mehr als 10.000 ist, gibt es keinen Bonus. 00:08:35.403 --> 00:08:36.643 Ich habe die Operation durchgeführt. 00:08:36.764 --> 00:08:37.564 Enter gedrückt. 00:08:37.583 --> 00:08:38.945 Wie Sie sehen, wurde der Bonus ausgegeben. 00:08:39.785 --> 00:08:40.666 Weil 11.375 größer als 10.000 ist. 00:08:45.383 --> 00:08:49.667 Eine andere Möglichkeit, diesen Vorgang durchzuführen, ist eigentlich folgende. 00:08:51.589 --> 00:09:04.339 Wenn Sie in das Datenfeld gehen, innerhalb des Datenfeldes, über die Datenvalidierung, wenn Sie auf Datenvalidierung klicken, irgendeinen Wert, 00:09:07.822 --> 00:09:27.221 Textlänge, alle Zahlen usw., wenn wir alle Zahlen auswählen, nicht zwischen den Daten, groß, wenn wir mindestens 10.000 für alle Zahlen sagen, sagen wir okay. 00:09:27.801 --> 00:09:30.203 Auf diese Weise können Sie es auch durchführen. 00:09:31.323 --> 00:09:33.145 Lassen Sie uns das hier zum Beispiel noch einmal anwenden. 00:09:33.145 --> 00:09:33.905 11.375.86. Datenvalidierung. 00:09:33.925 --> 00:09:34.245 Eingabemeldung. 00:09:47.088 --> 00:09:47.607 Einstellungen... 00:09:48.429 --> 00:09:49.090 Aus den Einstellungen... 00:09:51.331 --> 00:09:51.812 Alle Zahlen... 00:09:54.154 --> 00:09:54.474 Groß... 00:09:57.316 --> 00:09:57.676 Mindestens... 00:09:57.676 --> 00:10:05.943 10.000 sagen wir okay... 00:10:08.706 --> 00:10:11.886 Wir können auch diesen Datenvalidierungsprozess durchführen. 00:10:11.967 --> 00:10:14.848 Ungültige Daten in einen Kreis einschließen. 00:10:15.227 --> 00:10:18.830 Wir können Validierungskreise löschen und ähnliche Vorgänge durchführen. 00:10:18.850 --> 00:10:22.971 Aber das wird eher für Markierungszwecke verwendet. 00:10:23.451 --> 00:10:24.291 Datenvalidierung. 00:10:25.011 --> 00:10:33.795 Mein Tipp an Sie ist, wenn eine solche Situation vorliegt, wie wir es gemacht haben, eine Zellenbenennung vorzunehmen, um diesen Vorgang durchzuführen. 00:10:33.975 --> 00:10:36.655 Wir speichern die mit Strg-S durchgeführte Aktion ab.

WEBVTT 00:00:02.219 --> 00:00:11.107 Friends, in Exercise 11, I will teach you a few formulas defined here at an advanced level, involving numbers and formulas. 00:00:12.087 --> 00:00:16.732 I have entered the formulas here so that I can explain them to you by showing. 00:00:18.154 --> 00:00:26.722 My request from you is to enter the formulas I have shown you into the formula field, we will do it together anyway. 00:00:28.158 --> 00:00:30.920 First, let me explain what we are doing. 00:00:31.301 --> 00:00:38.987 Now, the total sales for Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. 00:00:40.468 --> 00:00:42.729 So, it is entered for the working days of the week. 00:00:43.571 --> 00:00:45.072 Sunday is a day off. 00:00:45.453 --> 00:00:52.418 Here, you see our number sequence adapted to our request. 00:00:53.159 --> 00:00:54.140 Sales amounts. 00:00:54.770 --> 00:01:02.696 You see that we apply a comma style here and that there are two-digit decimal values. 00:01:04.437 --> 00:01:12.303 And one of our tasks is that if the weekly sales are over 10,000, a bonus will be given. 00:01:13.904 --> 00:01:17.247 Now we had learned how to do the total operation. 00:01:17.907 --> 00:01:23.471 We use addition for the total and you can see the formula here already. 00:01:25.330 --> 00:01:25.950 What are we doing? 00:01:26.090 --> 00:01:27.031 We say equals. 00:01:27.812 --> 00:01:29.773 We write sum, open parentheses. 00:01:29.813 --> 00:01:36.438 Then we take the number values from Monday to Saturday and press the enter key. 00:01:36.498 --> 00:01:39.000 As you can see, the value, the total value came out here. 00:01:40.200 --> 00:01:46.105 When we say total count, there are two methods here. 00:01:46.801 --> 00:01:54.303 One is to count a field with text input, trying to reach that there are 7 days in a week. 00:01:54.823 --> 00:02:02.844 The other is to count only the fields with numbers, trying to reach the days of the week with input, which are 6 working days. 00:02:03.704 --> 00:02:19.460 The formula for this is friends connect, which means connect, value, number meaning Ba value count, I don't know what the expansion of this was thinking but our goal here is 00:02:19.600 --> 00:02:22.241 total count, which is count in English. 00:02:24.603 --> 00:02:25.284 What are we doing? 00:02:25.844 --> 00:02:26.845 We say equals. 00:02:29.286 --> 00:02:32.649 What was the formula for Top? 00:02:32.689 --> 00:02:33.529 Let's look at it again. 00:02:34.030 --> 00:02:34.970 Ba value count. 00:02:38.453 --> 00:02:45.983 We put Ba value count in parentheses. 00:02:46.623 --> 00:02:48.085 We selected the entire field. 00:02:48.885 --> 00:02:51.447 When you press Enter, 6 came. 00:02:52.348 --> 00:02:54.089 From B2 to H2. 00:02:56.731 --> 00:03:02.276 When we say Ba value filled count, it will count all the cells that are filled. 00:03:03.276 --> 00:03:08.180 Here we say equals. 00:03:08.861 --> 00:03:15.549 Ba value filled count. 00:03:17.852 --> 00:03:21.877 We entered all the values from B2 to H2 inside the parentheses. 00:03:21.978 --> 00:03:25.263 When you press Enter, that is, the Enter key, 7 came. 00:03:25.943 --> 00:03:30.020 We use Average for average. 00:03:30.581 --> 00:03:32.861 We opened the parentheses for average. 00:03:33.701 --> 00:03:36.022 We only select numerical values for the average. 00:03:36.082 --> 00:03:41.723 I think we all know that text values do not enter the average mathematically at this point. 00:03:43.443 --> 00:03:45.524 As you can see, the average value has also come. 00:03:45.784 --> 00:03:47.364 For the maximum value... 00:03:49.924 --> 00:03:51.085 We don't write Max. 00:03:51.565 --> 00:03:54.165 For maximum, let's correct it in Turkish here. 00:03:54.505 --> 00:03:57.166 The Turkish word for maximum is written exactly like this. 00:03:58.226 --> 00:04:01.527 M-A-X-I-M-U-M maximum. 00:04:03.128 --> 00:04:09.071 That's why we write MAX. 00:04:11.092 --> 00:04:12.552 We select our values. 00:04:12.632 --> 00:04:29.238 We close the parentheses from B2 to G2 and press the Enter key, and here, as you can see, the maximum value we call the total highest value within all values is 927, 1890, 1756, 2105, 1672, 3019. 3019.33 00:04:29.298 --> 00:04:33.000 came here. 00:04:36.942 --> 00:04:40.803 The Turkish word for minimum is minimum, so we use min. 00:04:41.765 --> 00:04:42.325 We take it. 00:04:42.785 --> 00:04:54.712 Again, we close from B2 to G2 and enter, and as you can see, the smallest value is 927.30. We will use the round function for decimal rounding. 00:04:55.993 --> 00:05:11.647 When using the round function, we need to pay attention to the number of decimal places we want to round the area we want to round. 00:05:11.707 --> 00:05:21.675 For example, let's say we round it up and take the total count. 00:05:21.675 --> 00:05:31.824 1137586 Then I wrote 1 for the decimal place. 00:05:32.065 --> 00:05:34.307 What happened when I pressed Enter? 00:05:34.307 --> 00:05:40.436 11375.86 It rounded to 11375.90. 00:05:40.956 --> 00:05:43.798 You know, 6 turns into 8 when rounding. 00:05:43.938 --> 00:05:45.319 So, what can I do? 00:05:45.719 --> 00:05:53.785 Since the 0 here is unnecessary, I can bring the number down by one to make it more concise. 00:05:55.346 --> 00:05:57.247 Let's give another example. 00:05:57.587 --> 00:05:58.327 Let's say it like this. 00:05:58.327 --> 00:06:11.144 Let's write a high decimal number like 11375.658974. 00:06:11.363 --> 00:06:12.144 Now what should we do? 00:06:12.565 --> 00:06:16.786 Let's select the number to round. 00:06:17.766 --> 00:06:18.186 Not. 00:06:18.766 --> 00:06:23.628 For example, we will do it for 3 decimal places. 00:06:25.137 --> 00:06:27.879 As you can see, it came to 3 decimal places. 00:06:28.099 --> 00:06:30.561 I'm taking the same formula and bringing it down. 00:06:31.142 --> 00:06:34.545 Then, instead of 3 in the formula, I write 1 again. 00:06:34.825 --> 00:06:36.666 As you can see, the decimal value is here. 00:06:38.408 --> 00:06:43.412 The number I entered after the decimal point becomes the place value. 00:06:43.592 --> 00:06:46.574 Accordingly, we perform the rounding operation. 00:06:46.594 --> 00:06:49.717 Now, the information given to us regarding the function is as follows. 00:06:51.707 --> 00:06:54.928 If the weekly sales exceed 10,000 TL, a bonus will be given. 00:06:55.008 --> 00:07:15.578 Now, in order to use the IF function, what we need are the criteria that create this situation, that is, the IF relationship, and as a result, whether there is a bonus or not. 00:07:18.743 --> 00:07:22.465 Therefore, we need to define the values of 10,000 and whether there is a bonus in a cell. 00:07:22.585 --> 00:07:24.587 So, I wrote 10,000 here. 00:07:25.287 --> 00:07:26.608 I wrote Bonus. 00:07:26.928 --> 00:07:28.049 I wrote No Bonus. 00:07:29.249 --> 00:07:30.930 When we come to the formula, it equals. 00:07:31.091 --> 00:07:36.734 If in the parentheses C6, which is C6? 00:07:39.295 --> 00:07:40.476 We look at C6. 00:07:40.536 --> 00:07:41.037 Total. 00:07:41.457 --> 00:07:45.059 At the same time, we had done the same process with B6. 00:07:47.059 --> 00:07:55.823 If C6 is greater than D13, that is, greater than 10,000, either there is a bonus or there is no bonus. 00:07:56.023 --> 00:07:59.384 According to whether this condition is met or not. 00:07:59.664 --> 00:08:23.034 That is, if B6 is greater than 10,000 in this situation, one day. 00:08:24.876 --> 00:08:26.737 If the value is correct, there is a bonus. 00:08:28.038 --> 00:08:29.999 If the value is wrong, there is no bonus. 00:08:31.240 --> 00:08:32.961 So, if it is greater than 10,000, there is a bonus. 00:08:33.301 --> 00:08:34.622 If it is not greater than 10,000, there is no bonus. 00:08:35.403 --> 00:08:36.643 I have performed the operation. 00:08:36.764 --> 00:08:37.564 I pressed Enter. 00:08:37.583 --> 00:08:38.945 As you can see, the bonus appeared. 00:08:39.785 --> 00:08:40.666 Because 11.375 is greater than 10,000. 00:08:45.383 --> 00:08:49.667 Another way to perform this operation is actually this. 00:08:51.589 --> 00:09:04.339 When you come to the data field, within the data field, through data validation, when you click on data validation, any value, 00:09:07.822 --> 00:09:27.221 text length, like all numbers, when we select all numbers, not between data, greater, when we say at least 10,000 for all numbers, we say okay. 00:09:27.801 --> 00:09:30.203 It is also possible to do it this way. 00:09:31.323 --> 00:09:33.145 For example, let's apply this again here. 00:09:33.145 --> 00:09:33.905 11.375.86. Data validation. 00:09:33.925 --> 00:09:34.245 Input message. 00:09:47.088 --> 00:09:47.607 Settings... 00:09:48.429 --> 00:09:49.090 From settings... 00:09:51.331 --> 00:09:51.812 All numbers... 00:09:54.154 --> 00:09:54.474 Greater... 00:09:57.316 --> 00:09:57.676 At least... 00:09:57.676 --> 00:10:05.943 When we say okay to 10,000... 00:10:08.706 --> 00:10:11.886 We can also perform this data validation process. 00:10:11.967 --> 00:10:14.848 Encircle the invalid data. 00:10:15.227 --> 00:10:18.830 We can perform operations like clearing validation circles. 00:10:18.850 --> 00:10:22.971 But this is more of a situation used for marking. 00:10:23.451 --> 00:10:24.291 Data validation. 00:10:25.011 --> 00:10:33.795 My advice to you is if there is such a situation, you can perform this operation by defining within the cell as we did. 00:10:33.975 --> 00:10:36.655 We are saving the operation we did with Ctrl-S.

WEBVTT 00:00:02.219 --> 00:00:11.107 Arkadaşlar alıştırma 11, sayılar ve formüller ileri seviye olarak burada tanımlanmış birkaç tane formülü size öğreteceğim. 00:00:12.087 --> 00:00:16.732 Ben formülleri buraya girdim ki size göstererek anlatabileyim. 00:00:18.154 --> 00:00:26.722 Sizden ricam formül alanına gerekli olan, size gösterdiğim formülleri girmenizi zaten beraber yapacağız. 00:00:28.158 --> 00:00:30.920 İlk başta ne yaptığımızı bir anlatalım. 00:00:31.301 --> 00:00:38.987 Şimdi satış toplamı pazartesi için, salı için, çarşamba için, perşembe için, cuma için ve cumartesi için. 00:00:40.468 --> 00:00:42.729 Yani haftanın çalışılan günler için girildi. 00:00:43.571 --> 00:00:45.072 Pazar günü ise tatil günü. 00:00:45.453 --> 00:00:52.418 Burada gördüğünüz gibi isteğe uyarlanmış bir sayı dizimiz var. 00:00:53.159 --> 00:00:54.140 Satış miktarları. 00:00:54.770 --> 00:01:02.696 Burada virgül stilini uyguladığımızı görüyorsunuz ve iki basamaklı ondalık değerler olduğunu görüyorsunuz. 00:01:04.437 --> 00:01:12.303 Ve görevlerimizden bir tanesi de eğer haftalık satış 10.000 üstü ise bonus verilecek diye. 00:01:13.904 --> 00:01:17.247 Şimdi toplam işlemini yapmayı öğrenmiştik. 00:01:17.907 --> 00:01:23.471 Toplam için toplama kullanıyoruz ve gördüğünüz gibi formül burada gözüküyor zaten. 00:01:25.330 --> 00:01:25.950 Ne yapıyoruz? 00:01:26.090 --> 00:01:27.031 Eşittir diyoruz. 00:01:27.812 --> 00:01:29.773 Topla yazıyoruz, parantez açıyoruz. 00:01:29.813 --> 00:01:36.438 Sonra sayı değerlerini pazartesinden cumartesine kadar alıyoruz ve giriş tuşuna basıyoruz. 00:01:36.498 --> 00:01:39.000 Gördüğünüz gibi değer, topladeğeri burada çıktı. 00:01:40.200 --> 00:01:46.105 Toplam adet dediğimizde ise burada iki tane yöntem var. 00:01:46.801 --> 00:01:54.303 Bir tanesi hem metin girili olan bir alanı saymak yani haftanın 7 günü olduğunu ulaşmaya çalışıyoruz. 00:01:54.823 --> 00:02:02.844 Diğeri de sadece sayı yazılığı olan, girili olan haftanın günleri yani 6 gün çalışma günü buna ulaşmaya çalışıyoruz. 00:02:03.704 --> 00:02:19.460 Bunun formülü ise arkadaşlar bağla yani bağ, değer, sayı anlamına gelen Ba değer say, yani bunun açılımı ne düşünüyordu bilmiyorum ama bizim amacımız burada 00:02:19.600 --> 00:02:22.241 toplam adet yani İngilizcesi count bunun. 00:02:24.603 --> 00:02:25.284 Ne yapıyoruz? 00:02:25.844 --> 00:02:26.845 Eşittir diyoruz. 00:02:29.286 --> 00:02:32.649 Top de neydi formül? 00:02:32.689 --> 00:02:33.529 Bir kez daha bakalım. 00:02:34.030 --> 00:02:34.970 Ba değer say. 00:02:38.453 --> 00:02:45.983 Ba D Say Parantez içerisine aldık. 00:02:46.623 --> 00:02:48.085 Bütün alanı seçtik. 00:02:48.885 --> 00:02:51.447 Enter'a bastığınızda 6 geldi. 00:02:52.348 --> 00:02:54.089 B2'den H2'ye kadar. 00:02:56.731 --> 00:03:02.276 Ba D Dolu Say dediğimizde ise metinleri yani dolu olan tüm hücreleri sayacak. 00:03:03.276 --> 00:03:08.180 Buradan eşittir diyoruz. 00:03:08.861 --> 00:03:15.549 Ba D dolu say. 00:03:17.852 --> 00:03:21.877 Parantezin içerisine B2'den H2'ye kadar tüm değerlere girdik. 00:03:21.978 --> 00:03:25.263 Entra bastığınızda yani giriş tuşuna 7 geldi. 00:03:25.943 --> 00:03:30.020 Ortalama için Ortalamayı kullanıyoruz. 00:03:30.581 --> 00:03:32.861 Ortalama parantez açtık. 00:03:33.701 --> 00:03:36.022 Ortalama için sadece sayı değerleri seçiyoruz. 00:03:36.082 --> 00:03:41.723 Metin değerlerinin ortalamaya girmediğini zaten matematiksel olarak bu noktada hepimiz biliyoruz diye düşünüyorum. 00:03:43.443 --> 00:03:45.524 Gördüğünüz gibi ortalama değeri de geldi. 00:03:45.784 --> 00:03:47.364 Maksimum değeri için... 00:03:49.924 --> 00:03:51.085 Max yazmıyoruz. 00:03:51.565 --> 00:03:54.165 Maksimum için hatta burada gelin Türkçe düzeltelim. 00:03:54.505 --> 00:03:57.166 Maksimumun Türkçesi tam anlamıyla böyle yazılıyor. 00:03:58.226 --> 00:04:01.527 M-A-K-S-I-M-U-M maksimum. 00:04:03.128 --> 00:04:09.071 Bu sebeple MAC yazıyoruz. 00:04:11.092 --> 00:04:12.552 Değerlerimizi seçiyoruz. 00:04:12.632 --> 00:04:29.238 B2'den G2'ye kadar parantezi kapıp giriş tuşuna basıyoruz ve burada maksimum dediğimiz toplam Bütün değer içerisindeki en yüksek değer gördüğünüz gibi 927, 1890, 1756, 2105, 1672, 3019. 3019.33 00:04:29.298 --> 00:04:33.000 buraya geldi. 00:04:36.942 --> 00:04:40.803 Minimumun Türkçesi minimum, o yüzden min kullanıyoruz. 00:04:41.765 --> 00:04:42.325 Alıyoruz. 00:04:42.785 --> 00:04:54.712 Yine B2'den G2'ye kadar kapatıp giriyoruz ve gördüğünüz gibi en küçük değer 927.30. Ondalık yuvarlama için yuvarla fonksiyonunu kullanacağız. 00:04:55.993 --> 00:05:11.647 Yuvarla fonksiyonunu kullanırken dikkat etmemiz gereken şey yuvarlamak istediğimiz alanı kaç ondalık basamak için yuvarlamak istediğimizi göre bir seçimi yapıyoruz. 00:05:11.707 --> 00:05:21.675 Mesela yuvarla dedik Toplam sayımızı alalım. 00:05:21.675 --> 00:05:31.824 1137586 Sonra virgül 1 ondalık basamak için 1 yazdım. 00:05:32.065 --> 00:05:34.307 Entra bastığım zaman gördüğünüz gibi ne oldu? 00:05:34.307 --> 00:05:40.436 11375.86 11375 90'a yuvarlandı. 00:05:40.956 --> 00:05:43.798 6'dan 8'e 9 oluyor biliyorsunuz yuvarlarken. 00:05:43.938 --> 00:05:45.319 Böylece ne yapabilirim? 00:05:45.719 --> 00:05:53.785 Buradaki 0 gereksiz olduğu için 10 dalık sayısını sayıdan bir tane düşürerek muhale getirebilirim. 00:05:55.346 --> 00:05:57.247 Mesela başka bir örnek verelim. 00:05:57.587 --> 00:05:58.327 Şöyle olsun. 00:05:58.327 --> 00:06:11.144 11375 Nokta 6, 5, 8, 9, 7, 4 diye yüksek ondalıklı bir sayı yazalım. 00:06:11.363 --> 00:06:12.144 Şimdi ne yapalım? 00:06:12.565 --> 00:06:16.786 Yuvarla sayıyı seçelim. 00:06:17.766 --> 00:06:18.186 Değili. 00:06:18.766 --> 00:06:23.628 Virgül mesela 3 ondalık için yapacağız. 00:06:25.137 --> 00:06:27.879 Gördüğünüz gibi 3 ondalığa geldi. 00:06:28.099 --> 00:06:30.561 Aynı formülü alıp aşağıya getiriyorum. 00:06:31.142 --> 00:06:34.545 Sonra formülün içerisinde 3 yerine yine 1 yazıyorum. 00:06:34.825 --> 00:06:36.666 Gördüğünüz gibi ondalık değer burada. 00:06:38.408 --> 00:06:43.412 Virgülden sonra girdiğim sayı basamak değeri oluyor. 00:06:43.592 --> 00:06:46.574 Ona göre yuvarlama işlemi gerçekleştiriyoruz. 00:06:46.594 --> 00:06:49.717 Şimdi eğer fonksiyonu ile ilgili bize verilen bilgi şu. 00:06:51.707 --> 00:06:54.928 Eğer haftalık satış 10.000 TL üstü ise bonus verilecek. 00:06:55.008 --> 00:07:15.578 Şimdi eğer fonksiyonunu kullanabilmek için ihtiyacımız olan şey bu durumu oluşturan kriterleri yani eğer ilişkisi ve sonuç olarak bonus var veya bonus 00:07:18.743 --> 00:07:22.465 yok değerlerini bizim bir hücre içinde tanınmamız gerekiyor. 00:07:22.585 --> 00:07:24.587 O yüzden 10.000'i ben buraya yazdım. 00:07:25.287 --> 00:07:26.608 Bonus'u yazdım. 00:07:26.928 --> 00:07:28.049 Bonus yok yazdım. 00:07:29.249 --> 00:07:30.930 Formule geldiğimizde eşittir. 00:07:31.091 --> 00:07:36.734 Eğer parantez içerisinde C6 yani C6 dediğimiz hangisi? 00:07:39.295 --> 00:07:40.476 C6'ya bakıyoruz. 00:07:40.536 --> 00:07:41.037 Toplam. 00:07:41.457 --> 00:07:45.059 Aynı zamanda B6'la da aynı işlemi yapmıştık. 00:07:47.059 --> 00:07:55.823 Eğer C6 D13'den büyükse yani 10.000'den büyükse ya bonus var ya bonus yok. 00:07:56.023 --> 00:07:59.384 Bu koşulun gerçekleşip gerçekleşmediğine göre. 00:07:59.664 --> 00:08:23.034 Yani eşittir eğer B6 bu durum içerisinde toplam 10.000'den büyükse bir gün. 00:08:24.876 --> 00:08:26.737 Eğer değer doğruysa bonus var. 00:08:28.038 --> 00:08:29.999 Eğer yanlışsa değer, bonus yok. 00:08:31.240 --> 00:08:32.961 Yani 10.000'den büyükse bonus var. 00:08:33.301 --> 00:08:34.622 10.000'den büyük değilse bonus yok. 00:08:35.403 --> 00:08:36.643 İşlemin gerçekleştirdim. 00:08:36.764 --> 00:08:37.564 Entra bastım. 00:08:37.583 --> 00:08:38.945 Gördüğünüz gibi bonus çıktı. 00:08:39.785 --> 00:08:40.666 Çünkü 11.375 10.000'den büyük. 00:08:45.383 --> 00:08:49.667 Bu işlemi gerçekleştirmenin diğer bir yolu da aslına bakarsanız şu. 00:08:51.589 --> 00:09:04.339 Veri alanına geldiğinizde, veri alanı içerisinde, veri doğrulama üzerinden, veri doğrulamaya tık bastığınızda, herhangi bir değer, 00:09:07.822 --> 00:09:27.221 metin uzunluğu, tüm sayı gibi, tüm sayı seçtiğimizde veri arasında değil, büyük, tüm sayı büyük en az 10.000 dediğimizde tamam diyoruz. 00:09:27.801 --> 00:09:30.203 Bu şekilde de gerçekleştirmeniz mümkün. 00:09:31.323 --> 00:09:33.145 Mesela burada bunu tekrar uygulayalım. 00:09:33.145 --> 00:09:33.905 11.375.86. Veri doğrulama. 00:09:33.925 --> 00:09:34.245 Giriş iletisi. 00:09:47.088 --> 00:09:47.607 Ayarlar... 00:09:48.429 --> 00:09:49.090 Ayarlardan... 00:09:51.331 --> 00:09:51.812 Tüm sayı... 00:09:54.154 --> 00:09:54.474 Büyük... 00:09:57.316 --> 00:09:57.676 En az... 00:09:57.676 --> 00:10:05.943 10.000 Tamam dediğimizde... 00:10:08.706 --> 00:10:11.886 Bu veri doğrulama işlemini de gerçekleştirebiliyoruz. 00:10:11.967 --> 00:10:14.848 Geçersiz veriyi daire içine al. 00:10:15.227 --> 00:10:18.830 Doğrulama dairelerini temizle gibi işlemler gerçekleştirebiliriz. 00:10:18.850 --> 00:10:22.971 Fakat bu daha çok işaretleme için kullanılan bir durum. 00:10:23.451 --> 00:10:24.291 Veri doğrulama. 00:10:25.011 --> 00:10:33.795 Benim size tavsiyem eğer böyle bir durum varsa gerçekleştirdiğimiz gibi hücre içi tanımlama yaparak eğerle bu işlemi gerçekleştirebilirsiniz. 00:10:33.975 --> 00:10:36.655 Ctrl-S ile yaptığımız işlemi kaydediyoruz.