Скачать "Формулы Excel - Топ 41 функция - они пригодятся каждому!"

Обложка аудиозаписи

Подождите немного, мы готовим ссылки для удобного просмотра видео без рекламы и его скачивания.

Предыдущее видео: Функции и Формулы Excel: Новый Плейлист - Функции Excel (1)Следующее видео: Формулы Excel – все особенности, тонкости и трюки в одном видео! - Функции Excel (2)

Формулы Excel – все особенности, тонкости и трюки в одном видео! - Функции Excel (2)

Формулы Excel – все особенности, тонкости и трюки в одном видео! - Функции Excel (2)

Канал: Билял Хасенов – Excel, VBA & More

Функции и Формулы Excel: Новый Плейлист - Функции Excel (1)

Функции и Формулы Excel: Новый Плейлист - Функции Excel (1)

Канал: Билял Хасенов – Excel, VBA & More

ВПР vs. ИНДЕКС & ПОИСКПОЗ - Функции Excel (8)

ВПР vs. ИНДЕКС & ПОИСКПОЗ - Функции Excel (8)

Канал: Билял Хасенов – Excel, VBA & More

ВПР и Кофе (?!) - Функции Excel (6)

ВПР и Кофе (?!) - Функции Excel (6)

Канал: Билял Хасенов – Excel, VBA & More

XLOOKUP aka ПРОСМОТРX – Эволюция ВПР! - Функции Excel (12)

XLOOKUP aka ПРОСМОТРX – Эволюция ВПР! - Функции Excel (12)

Канал: Билял Хасенов – Excel, VBA & More

On Error Resume vs. GoTo и работа с ошибками (Серия VBA 36)

On Error Resume vs. GoTo и работа с ошибками (Серия VBA 36)

Канал: Билял Хасенов – Excel, VBA & More

VBA Excel программирование для начинающих - Новый курс VBA (1)

VBA Excel программирование для начинающих - Новый курс VBA (1)

Канал: Билял Хасенов – Excel, VBA & More

Вычисление оценок, комиссий и т.д. в Excel - ВПР и ИСТИНА - Функции Excel (11)

Вычисление оценок, комиссий и т.д. в Excel - ВПР и ИСТИНА - Функции Excel (11)

Канал: Билял Хасенов – Excel, VBA & More

Сравнение двух списков в Excel (Различия & Сходства)

Сравнение двух списков в Excel (Различия & Сходства)

Канал: Билял Хасенов – Excel, VBA & More

Срез сводной таблицы с функцией поиска в Excel | 5-ое Видео курса "Сводные Таблицы"

Срез сводной таблицы с функцией поиска в Excel | 5-ое Видео курса "Сводные Таблицы"

Канал: Билял Хасенов – Excel, VBA & More

excel

vba

вба

эксель

функции эксель

просмотрx

впр

гпр

поискпоз

00:00:01

приготовил подборку функций Excel, о

00:00:03

которых должен знать просто каждый. С

00:00:05

большинством этих функций приходится

00:00:07

иметь дело сразу после знакомства с

00:00:08

Excel, как с программой в принципе и эти

00:00:11

функции в последующем сопровождают

00:00:12

любого пользователя Excel на

00:00:14

повседневной основе, поэтому это видео

00:00:16

будет полезным, пожалуй, для каждого,

00:00:17

начиная от тех, кому предстоит пройти

00:00:19

собеседование,

00:00:20

включая уже начавших свою работу с

00:00:22

программой новичков, и вплоть до

00:00:23

профессионалов, ведь в конце концов

00:00:25

повторение тоже никогда не бывает лишним.

00:00:27

Кроме того, в качестве бонуса я добавил

00:00:29

перевод каждой функции на шесть языков.

00:00:31

Уверен, то что как минимум английские

00:00:33

версии названий функций могут

00:00:35

понадобиться многим. Что ж, давайте

00:00:36

приступим! Для урока я приготовил вот

00:00:38

этот файл. Обязательно советую Вам его

00:00:40

скачать по двум причинам: во-первых,

00:00:42

каждую функцию мы сейчас будем обсуждать

00:00:44

и прописывать в данной рабочей книге.

00:00:46

Повторение действий вместе со мной поможет

00:00:48

Вам намного лучше усвоить получаемую

00:00:50

информацию, ну и, во-вторых, этот файл Вы

00:00:52

можете использовать, как заметку или же

00:00:54

шпаргалку на будущее. Смотрите, наведя

00:00:56

курсор мышки на название интересующей

00:00:58

Вас функции, Вы сможете прочитать, что она

00:01:01

делает и здесь же сразу представлено

00:01:02

наименование данной функции на других

00:01:04

языках. Ну и кроме того, в этой рабочей

00:01:06

книге есть второй лист, в котором функции,

00:01:08

их наименование на других языках, а также

00:01:11

Описание их принципа работы представлены

00:01:13

в табличном виде, что так же может

00:01:14

послужить неплохим вспомогательным

00:01:16

инструментом. Окей. Вернемся к первому рабочему листу и

00:01:19

приступим к разбору функции сначала

00:01:21

первой группы. Эти две группы пока скроем.

00:01:23

Первое - СЧЁТЗ. Эта функция позволяет

00:01:25

подсчитать, сколько в указанном диапазоне

00:01:27

имеется непустых ячеек. Смотрите, ставлю

00:01:30

знак равно вписываю СЧЁТЗ, открываю

00:01:33

скобки и указываю, например, диапазон с

00:01:36

перечислением городов. Закрываю скобку,

00:01:38

Нажимаю на Enter и функция нам сразу

00:01:40

говорит, что в указанном диапазоне, вот

00:01:42

здесь, 12 непустых ячеек. Убедиться в

00:01:44

принципе работы данной функции мы можем

00:01:46

следующим образом: просто возьму и удалю

00:01:48

один из городов. Моментально видим, что

00:01:51

выводимое функцией значение было

00:01:52

поправлено, и теперь функция нам говорит,

00:01:54

что в этом диапазоне 11 заполненных, то

00:01:57

есть непустых ячеек. Все верно! Тут же у

00:01:59

нас появляется отличная возможность

00:02:00

рассмотреть вторую функцию - СЧИТАТЬПУСТОТЫ.

00:02:02

Как Вы можете догадаться, она

00:02:04

имеет ровно противоположный функционал,

00:02:06

то есть подсчитывает, сколько в указанном

00:02:08

диапазоне не заполненных ячеек. Именно поэтому,

00:02:10

если в ее входном аргументе мы сейчас

00:02:13

укажем все тоже диапазон ячеек с

00:02:14

городами, то она нам выведет единицу. Если

00:02:17

мы вернем наименование временно

00:02:19

убранного города обратно в пустую

00:02:21

ячейку, то СЧЁТЗ снова выводит

00:02:23

цифру 12, а СЧИТАТЬПУСТОТЫ, не найдя

00:02:25

пустых ячеек, выводит ноль. Отлично!

00:02:27

Следующая функция - СЧЁТ. С помощью неё мы

00:02:30

можем почитать количество ячеек

00:02:32

заполненных числами. Смотрите, пишу эту

00:02:35

функцию в ячейку и в качестве вводимого

00:02:37

диапазона укажу, например, строчку первого

00:02:39

города Бремена. Закрываю скобку, Enter, и

00:02:42

как видим, функция возвращает число два. И

00:02:44

вправду, в указанном диапазоне мы также

00:02:46

можем найти как раз два числа. Если мы

00:02:48

расширим входной диапазон, например, вот

00:02:50

таким образом, до второй строки таблицы,

00:02:52

то вполне ожидаемо функция возвращает

00:02:54

нам уже число 4. Отлично! Переходим к

00:02:56

следующей функции - СУММ. Уверен, она

00:02:59

большинству зрителей знакома, но просто

00:03:01

так не упомянуть её нельзя, тем более что

00:03:03

иногда бывают ситуации, когда новички

00:03:04

суммируют значение вручную, вот таким

00:03:07

образом. Если Вам суммировать нужно

00:03:08

большое количество значений, как например

00:03:10

здесь всю выручку, то не делайте это вот

00:03:12

так вручную. Лучше просто вписать функцию

00:03:15

СУММ и в качестве входного аргумента

00:03:16

ввести все ячейки с суммируемыми

00:03:18

значениями. Enter, и функция моментально

00:03:21

выполняет за нас всю работу. Окей. Следующая

00:03:23

функция - МАКС. Всё крайне просто: указываем

00:03:26

ей диапазон ячеек и в результате

00:03:28

получаем значение, которое в этом

00:03:29

диапазоне является максимальным. Функция

00:03:32

МИН работает по такому же принципу.

00:03:33

Указываем ячейки и узнаем, какое значение

00:03:36

этого диапазона является наименьшим.

00:03:38

СРЗНАЧ, то есть среднее значение. Функция

00:03:40

выполняет соответствующую своему

00:03:42

названию задачу - возвращает среднее

00:03:44

арифметическое

00:03:45

указанных значений. Отлично! Продолжаем.

00:03:47

Следующей у нас идёт функция ЕСЛИ. В ней

00:03:50

мы можем вести определенные условия и 2

00:03:52

возвращаемых значения - одно значение

00:03:54

будет выводиться при выполнении

00:03:55

поставленного условия, а второе при его

00:03:58

невыполнении. Приведем пример. Пишу равно,

00:04:00

ЕСЛИ, и в первом аргументе функции мы

00:04:03

должны прописать логическую проверку.

00:04:05

Давайте в качестве примера сделаем

00:04:06

следующее: укажем, например, вот эту ячейку

00:04:08

и скажем. Если её значение меньше 500 000,

00:04:11

то выводи нам, ставим точку с запятой, и в

00:04:13

кавычках говорим, что должно быть выведено.

00:04:15

В противном случае, опять точка с запятой, пусть

00:04:18

выводится подпись «Высокая выручка».

00:04:20

Нажимаю Enter и у нас естественно

00:04:22

выводится первый вариант ответа - выручка

00:04:24

низкая. При этом, если мы сейчас изменением

00:04:27

входное проверяемое значение для

00:04:28

приписанной функции, то есть вот вхожу в

00:04:30

ячейку и теперь в качестве проверяемой

00:04:32

выручки выбираю, например, данное значение,

00:04:34

то после нажатия на Enter мы сразу

00:04:36

получаем подпись «Высокая выручка». Окей. При

00:04:39

этом здесь в качестве оператора

00:04:40

сравнения я вписал символ меньше. Для

00:04:42

других же сравнений можно также

00:04:44

использовать следующие обозначения:

00:04:46

больше, меньше или равно, больше или равно,

00:04:49

равно и не равно. Отлично. Дальше у нас

00:04:51

идут уже более продвинутые функции. Они

00:04:53

совмещают в себе вот эти расчетные функции

00:04:55

с условной функцией ЕСЛИ. Давайте

00:04:58

разберемся. Первый пример у нас - это

00:04:59

функция СЧЁТЕСЛИ. С помощью неё мы можем

00:05:02

указать определенный диапазон ячеек и

00:05:04

задать критерий, на соответствие которому

00:05:06

значения этих ячеек будут проверяться.

00:05:08

Приведем пример. Пишу СЧЁТЕСЛИ и ввожу

00:05:10

сперва, например, диапазон с указанием

00:05:12

населения в городах. Критерий будет

00:05:14

следующий, например, больше миллиона. Пишу

00:05:17

его вот так в кавычках. Enter и видим: в

00:05:19

таблице у нас перечислено 4 города,

00:05:21

население в которых превышает миллион

00:05:23

человек. Окей, следующая функция схожая - СУММЕСЛИ,

00:05:26

то есть условная сумма. У нее, однако,

00:05:29

есть одна отличительная особенность — это

00:05:31

вот этот опциональный, то есть

00:05:32

необязательный аргумент. Давайте сперва

00:05:34

приведем такой пример: говорим СУММЕСЛИ,

00:05:36

и сперва идет диапазон проверяемых

00:05:38

значений, пусть это будет опять-таки

00:05:40

население. Дальше по аналогии с функцией

00:05:42

СЧЁТЕСЛИ нужно ввести критерий. Пусть это

00:05:44

также будет критерий «Население больше миллиона».

00:05:45

Если я сейчас нажму Enter, а так

00:05:48

можно сделать, так как у нас данный

00:05:49

аргумент не обязательный, то выводится

00:05:51

сумма населения тех городов, в которых

00:05:53

живет более одного миллиона человек. Если я

00:05:55

выберу вручную вот так ячейки с

00:05:57

населением четырех подходящих городов, то ниже

00:05:59

справа мы увидим подтверждение - в сумме

00:06:01

это 8 044 000. Сделаем, однако,

00:06:04

один шаг назад и посмотрим, что же делает

00:06:06

тот самый опциональный аргумент.

00:06:07

Возвращаюсь в формулу ячейки и видим:

00:06:09

последний аргумент — это диапазон

00:06:11

суммирования. Давайте введём в качестве

00:06:13

диапазона суммирования, например выручку,

00:06:15

то есть теперь на критерий будет

00:06:17

проверяться население городов, а

00:06:18

суммироваться будет выручка. Закрываем

00:06:20

скобку, нажимаем Enter и мы получаем вот

00:06:23

такую сумму - около миллиона восьми ста тысяч.

00:06:25

Можно еще поправить формат этого числа.

00:06:27

Ну и сразу проверим. Также выбираю

00:06:29

выручку каждого подходящего города и

00:06:31

видим: суммы выручки совпадают. Следующая

00:06:33

функция - СРЗНАЧЕСЛИ, и она опять-таки

00:06:36

аналогична функции СУММЕСЛИ. То

00:06:38

есть вводим проверяемый диапазон,

00:06:40

критерий и также есть опциональной

00:06:42

диапазон усреднения, если таковой должен

00:06:44

отличаться от проверяемого диапазона.

00:06:45

Среднее значение получается равно

00:06:47

примерно четырёмстам пятидесяти тысячая. Окей. Следующая

00:06:50

функция у нас – это ЕСЛИМН, то есть функция,

00:06:53

которая позволяет проверять сразу

00:06:55

множество условий. Ставлю равно и пишу

00:06:57

её название. Теперь вводим первое условие.

00:06:59

Давайте в качестве примера будем

00:07:01

проверять следующее - укажем ячейку B3 и

00:07:03

скажем, если в ней значение равно слову

00:07:06

«Север», то ставим точку с запятой, пусть

00:07:08

выводится английское обозначение слова

00:07:10

север. Следующая логическая проверка. Если

00:07:12

эта же ячейка содержит слово «Восток», то

00:07:15

пусть выводится английское слово «East». И

00:07:17

вот таких условий можно вводить, сколько

00:07:19

требуется. Нам их нужно в этом примере

00:07:21

всего 4, так что давайте я два оставшихся

00:07:23

варианта также пропишу. Закрываю скобку,

00:07:25

подтверждаю ввод и поскольку мы ссылались

00:07:27

на слово «Север», на выходе мы получаем

00:07:29

английское слово «North». Если я сейчас вот

00:07:32

так внутри формулы смещу ссылку со слова

00:07:34

«Север», например, на слово «Восток», то в

00:07:36

результате у нас правильным образом

00:07:38

обновится перевод. При этом обратите

00:07:39

внимание, в таблице на втором листе также

00:07:41

имеется столбец «Доступна с». В нём

00:07:44

отмечены функции, которые являются

00:07:45

достаточно новыми и доступны лишь

00:07:47

начиная с соответствующей, указанной вот

00:07:49

здесь версии Microsoft Office. Функция

00:07:52

ЕСЛИМН как раз является подобной

00:07:54

относительно новой функции и поэтому

00:07:56

доступна она лишь тем, кто работает в

00:07:57

Excel 2019 или в более новой версии. Тем

00:08:00

не менее функция эта достаточно

00:08:02

интересна и поэтому я хотел также ее

00:08:04

всем показать. Для тех же у кого эта

00:08:05

функция отсутствует, конечно, также

00:08:07

имеется множество вариантов, как ее можно

00:08:09

заменить. В качестве примера, который мне

00:08:11

первым приходит в голову, можно назвать

00:08:12

решение с использованием вложенных

00:08:14

функций ЕСЛИ. Смотрите, давайте я очищу

00:08:16

ячейку H8 и в ней мы сейчас как раз

00:08:19

пропишем пример подобных вложенных

00:08:21

условий. Делается это так. Начнем также

00:08:23

опять-таки с примера ячейки B3. Если вот это

00:08:26

значение равно слову «Север», то пусть

00:08:28

выводится слово «North». Окей первый вариант

00:08:30

готов. Второй вариант будет такой: в

00:08:32

противном случае, и проводим вторую

00:08:34

проверку. Если значение этой же ячейки

00:08:36

равно славу «Восток», то пусть выводится

00:08:39

слова «East», и так далее. Аналогично

00:08:41

заканчиваем нашу логическую цепочку для

00:08:43

всех четырех сторон света с помощью

00:08:45

вложенных функций ЕСЛИ. Как видите, в

00:08:47

итоге мы получаем все тот же функционал

00:08:49

с единственной разницей в том, что,

00:08:51

конечно, вложенные функции ЕСЛИ, пожалуй,

00:08:53

менее наглядны. Это, конечно, далеко не

00:08:55

критично. Отлично, таким образом, мы

00:08:57

подходим к следующей подгруппе функций.

00:08:59

Их особенность заключается в том, что они

00:09:01

объединяют в себе функцию ЕСЛИ с

00:09:03

вычислительными функциями СЧЁТ, СУММ, МАКС

00:09:05

и так далее, и главная характеристика это

00:09:07

то, что они могут проверять значение на

00:09:09

соответствие сразу нескольким критериям.

00:09:11

Давайте приведем один пример со всеми

00:09:13

этими пятью функциями. Скажем, например,

00:09:15

что нас интересует выручка всех городов,

00:09:17

относящихся к региону юг, это получается

00:09:20

первое условие, и второе условие — это то,

00:09:22

что население этих южных городов не

00:09:24

превышает миллиона жителей. Интересовать

00:09:26

нас будет количество отдельных подобных

00:09:28

выручек, скажем так, их сумма, среднее

00:09:31

значение, а также максимум и минимум.

00:09:33

Начинаем с функции подсчета. Пишем СЧЕТЕСЛИМН

00:09:36

и, как видите, сперва мы должны

00:09:38

ввести диапазон ячеек, проверяемых на

00:09:40

первое условие. У нас это указание региона,

00:09:43

так, что вводим вот этот диапазон, точка с запятой, и

00:09:46

говорим условие — это должны быть города

00:09:48

региона «Юг». Отлично! Точка с запятой, и теперь мы

00:09:51

должны ввести диапазон, который будет

00:09:53

проверяться на второе условие — это

00:09:55

конечно может быть и тот же самый, уже

00:09:56

введенный диапазон, но, в нашем случае это

00:09:58

не так. В нашем случае на выполнение

00:10:00

второго условия мы проверяем количество

00:10:02

населения в городах. Указываем диапазон с

00:10:04

населением, точка с запятой, и говорим: нам нужны города,

00:10:08

в которых население не превышает

00:10:09

миллиона, вводим обозначение меньше

00:10:12

миллиона, Enter, и у нас выводится число

00:10:14

два. Смотрим, всего у нас три города из

00:10:17

региона «Юг», и лишь в одном из них проживает

00:10:19

более миллиона человек, поэтому мы и

00:10:20

получаем двойку. Если предположим, вдруг,

00:10:23

например, Франкфурт будет переведён в

00:10:24

регион сети продаж «Юг», то сразу по

00:10:26

заданным критериям проходит еще один

00:10:28

Город и СЧЁТЕСЛИМН

00:10:29

сразу выводит уже

00:10:31

тройку. Это был просто пример для

00:10:33

наглядности, так что вернем Франкфурт

00:10:35

обратно в регион «Запад». Отлично! Теперь

00:10:37

посчитаем с помощью функции СУМММЕСЛИМН

00:10:40

сумму выручки проходящих по критериям

00:10:43

двух городов. Мы их уже знаем — это

00:10:44

Нюрнберг и Штутгарт. Итак, пишу СУММЕСЛИМН

00:10:47

и здесь мы сперва должны указать, что

00:10:50

будем суммировать. Это выручка, а уже

00:10:52

после указания вводим все те же критерии.

00:10:54

Регион «Юг» и население менее миллиона.

00:10:56

Отлично! Ну и по аналогии высчитываем

00:10:58

среднюю выручку этих городов. Выходит

00:11:01

около двухсот пяти тысяч. Таким образом, у нас в

00:11:03

этой подгруппе функций остаются функции

00:11:05

МАКСЕСЛИ и МИНЕСЛИ. Внимание! Эти

00:11:08

функции доступны с версии Excel 2019,

00:11:11

кроме того, у них есть интересная

00:11:13

особенность - несмотря на то, что на их

00:11:15

конце не прописаны буквы МН, то есть

00:11:17

«Множество условий», принимать они могут

00:11:19

более одного критерия для проверки

00:11:20

значений. То есть вот, смотрите, сперва

00:11:23

диапазон, где ищем максимальное значение,

00:11:25

затем первая проверяемая характеристика, и вторая.

00:11:28

Enter, и получаем указание максимального

00:11:31

значение из подходящих — это 275 тысяч. И

00:11:34

вправду, из двух подходящих по критериям

00:11:36

городов по выручке выигрывает как раз

00:11:38

Штутгарт с данной суммой. Функция МИНЕСЛИ

00:11:40

работает аналогично - вот получаем

00:11:43

значение выручки Нюрнберга. Супер! Целая

00:11:45

подгруппа этих важных расчетных функций

00:11:47

проработана. Перейдем к основным

00:11:49

функциям для подтягивания значений.

00:11:52

Первое - легендарная ВПР, то есть вертикальный

00:11:54

просмотр. Работает она так. Скажем нам

00:11:56

нужно значение выручки Дрездена.

00:11:58

Получить его с помощью ВПР мы можем

00:12:00

следующим образом: пишу ВПР, искомое

00:12:02

значение у нас — это Дрезден и искать его

00:12:04

мы будем вот в этой таблице. При этом

00:12:06

обратите внимание. Искомое значение

00:12:08

должно находиться в первом столбце

00:12:09

указываемой таблицы, поэтому в подобной

00:12:11

ситуации мы вынуждены выделить таблицу

00:12:14

следующим образом. Дальше точка с запятой, и

00:12:16

говорим: из данной таблицы, из какого

00:12:18

столбца правее нужно вернуть значение. То

00:12:21

есть считаем — это первый столбец, это

00:12:23

второй, это третий столбец, скажем нам

00:12:25

нужна выручка, так что вводим тройку, и в

00:12:28

качестве последнего аргумента, чтобы шел

00:12:30

поиск точного совпадения, вводим

00:12:32

логическое значение ЛОЖЬ. Enter и

00:12:34

значение успешно подтянуто. При этом

00:12:36

сейчас сразу благодаря функции ВПР мы

00:12:38

познакомимся с функцией ЕСЛИОШИБКА.

00:12:40

Смотрите, давайте в функции ВПР попробуем

00:12:43

найти значения выручки города Ганновер.

00:12:45

Заменяю Дрезден на Ганновер, Enter и мы

00:12:47

получаем ошибку #Н/Д, так как ВПР

00:12:49

указанный город в таблице не нашел — это

00:12:51

и правильно, ведь его там нет. Я это сейчас

00:12:53

проделал специально для того, чтобы

00:12:55

разобрать функцию ЕСЛИОШИБКА. Смотрите,

00:12:57

что она делает. Пишу, ЕСЛИОШИБКА и

00:13:00

указываю данную ячейку. Точка с запятой и теперь

00:13:03

говорим про себя: если в этой ячейке

00:13:04

ошибка, то, ставим точку с запятой, и

00:13:07

указываем, что тогда должна функция

00:13:09

вписать в ячейку. В качестве примера

00:13:11

пусть это будет фраза «Город не найден».

00:13:13

Enter и мы видим только что указанную

00:13:16

фразу, так как и вправду ВПР у нас вот

00:13:18

здесь выводит ошибку. Но Вы спросите, что

00:13:20

эта функция будет выводить, если в

00:13:22

проверяемый ячейке нет ошибки? Мы же это

00:13:24

не вписали! Все просто. В таком случае,

00:13:26

функция ЕСЛИОШИБКА будет выводить то

00:13:29

значение, которое, собственно, и находится

00:13:30

в указанной ячейке, то есть смотрите, я

00:13:32

сейчас верну на место Дрезден и сразу

00:13:34

выводиться его выручка, и эта же выручка

00:13:36

дублируется в ячейке с функцией ЕСЛИОШИБКА

00:13:39

Супер! Таким образом, мы подошли к

00:13:41

очередной новый функции ПРОСМОТРX.

00:13:43

Подробно я о ней рассказывал вот в этом

00:13:45

видео, советую его посмотреть. Пока

00:13:47

же сделаем быстрый разбор. ПРОСМОТРX, на

00:13:50

конце находится именно английская буква

00:13:51

Х, это такой продвинутый вариант ВПР.

00:13:53

Как Вы помните я сказал то, что с помощью

00:13:55

обычной функции ВПР невозможно получить

00:13:58

значение, находящееся слева от

00:14:00

проверяемого столбца, то есть

00:14:01

возвращаемые значения должны быть всегда

00:14:03

правее искомых значений. Подобных

00:14:05

ограничений функция ПРОСМОТРX не имеет.

00:14:08

Смотрите, как все легко. Просто указываю

00:14:10

искомое значение, все тот же Дрезден, ищем

00:14:12

мы его вот здесь, и возвращаем выручку вот

00:14:14

отсюда. Идеально - быстро и просто. Кроме

00:14:17

того, ПРОСМОТРX не нуждается в помощи

00:14:19

функции ЕСЛИОШИБКА в ситуациях, когда

00:14:21

значение не было найдено, так как у него

00:14:23

есть свой собственный опциональный

00:14:24

аргумент для выполнения данной задачи. То

00:14:27

есть приведем тот же пример: Дрезден

00:14:28

заменяем на Ганновер, Enter, выходит ошибка.

00:14:31

Если же мы сейчас добавим аргумент

00:14:33

«если_ничего_не_найдено», то выходит вот такой

00:14:35

отличный результат. Супер! Что ж, первую

00:14:38

большую группу функций мы проработали.

00:14:40

Давайте я её скрою и перейдем к разбору

00:14:42

второй группы. Итак, еще одна новая

00:14:44

функция - ОБЪЕДИНИТЬ. Работает она с

00:14:46

Excel 2019 года и выполняет следующую

00:14:49

задачу - с помощью неЁ можно объединить

00:14:51

значение из нескольких ячеек в одну

00:14:53

текстовую строку, отделив эти значения

00:14:56

друг от друга при надобности

00:14:57

определенным разделителем. То есть вот

00:14:59

тут я приготовил следующие четыре слова

00:15:01

и, предположим, нам их нужно объединить в

00:15:03

одну строку, разделив отдельные слова при

00:15:05

Этом запятой с пробелом. Делается это крайне

00:15:07

просто. В качестве разделителя в кавычках

00:15:09

вводим запятую с пробелом, пропускать

00:15:11

пустые ячейки скажем да, ИСТИНА, ну и

00:15:14

указываем нужные ячейки. Всё сработало

00:15:16

отлично за исключением одного - смотрите,

00:15:18

вот тут как-то много пробелов. Вероятно,

00:15:20

тот, кто подготавливал эти ячейки

00:15:22

проглядел и оставил много ненужных

00:15:24

пробелов перед словом «Запад», или это

00:15:26

может был такой кривой экспорт из

00:15:28

определенной программы. В общем не важно,

00:15:29

нам в любом случае нужно это поправить.

00:15:31

Сперва проверим - захожу в эту ячейку и

00:15:33

правда, вот здесь можно найти пять каких-то

00:15:36

лишних пробелов перед словом «Запад».

00:15:38

Подобные ситуации отлично исправляет

00:15:40

функция СЖПРОБЕЛЫ, то есть сжать

00:15:42

пробелы. Она удаляет все лишние пробелы,

00:15:45

оставляя лишь нужные между словами.

00:15:47

Смотрите ввожу функцию СЖПРОБЕЛЫ, Enter

00:15:51

Из-за центрирования ячейки нам разницы пока

00:15:53

не видно, но если мы сейчас сделаем

00:15:55

выравнивание по левому краю, то видим:

00:15:57

левые пробелы были успешны убраны.

00:15:59

Поэтому как мы можем воспользоваться

00:16:00

этой функции в прошлом примере? Просто

00:16:02

оборачиваем в предыдущей ячейке функцию

00:16:05

ОБЪЕДИНИТЬ в функцию СЖПРОБЕЛЫ и

00:16:07

получаем идеальный результат. Очень часто

00:16:09

кстати, эта функция используется в

00:16:11

ситуациях, когда, например, перед или после

00:16:13

слов идут вот такие ненужные одиночные

00:16:16

пробелы. Также в таких ситуациях просто

00:16:18

применяем функцию СЖПРОБЕЛЫ и

00:16:20

получаем чистое значение. Окей, идем дальше.

00:16:23

Функция ЛЕВСИМВ. С помощью нее мы можем

00:16:25

получить определенное количество

00:16:26

символов, начиная с начала указанной

00:16:29

текстовой строки. То есть смотрите, пишу

00:16:30

ЛЕВСИМВ и укажу в качестве входного

00:16:32

текста вот этот объединенный текст. После

00:16:35

этого говорим, сколько символов начиная

00:16:37

слева и читая вправо нам нужно получить

00:16:39

из этого текста. Пусть это будет 5 символов.

00:16:42

Enter и получаем слово «Север». Функция

00:16:45

ПРАВСИМВ работает аналогично, только

00:16:47

возвращается в итоге указанное

00:16:49

количество символов, начиная с конца

00:16:50

текста. Указываем ту же фразу, все также 5

00:16:53

символов и получаем в итоге слово «Запад».

00:16:55

Отлично! Теперь функция ПСТР. Она

00:16:58

позволяет вырезать, так сказать, кусочек

00:17:00

текста из середины. То есть смотрите,

00:17:02

например, нам нужно слово «Восток». Пишу

00:17:05

ПСТР, указываем текст, начальная позиция,

00:17:08

посчитаем, включая запятую и пробел. 1, 2, 3, 4,

00:17:11

5, 6, 7, 8. Восьмая начальная позиция и длина

00:17:14

будет 6 символов. Enter и получаем слово

00:17:17

«Восток». Окей, дальше идут вот такие

00:17:19

простенькие функции. ПРОПИСН позволяет

00:17:22

перевести весь текст в прописной, то есть

00:17:24

в заглавные буквы. Вот пишу название

00:17:25

функции и указываю наш текст. В итоге

00:17:28

получаем вот такой результат. Функция

00:17:30

СТРОЧН имеет обратный результат. Она

00:17:32

переводит все символы в строчную форму, то

00:17:35

есть буквы становятся маленькими. Функция

00:17:37

же ПРОПНАЧ производит эффект, который мы

00:17:39

как раз уже можем наблюдать вот здесь. То

00:17:41

есть первая буква каждого слова

00:17:42

становится прописной, а все остальные

00:17:44

строчными. Поскольку к этому тексту нет

00:17:46

смысла применять разбираемую функцию

00:17:48

ПРОПНАЧ, проверим её на ячейке выше.

00:17:50

Пишу ПРОПНАЧ, ссылаюсь вот сюда и в

00:17:52

итоге мы получаем уже описанный результат.

00:17:54

Супер! Теперь эти две функции. Сперва ДЛСТР,

00:17:57

то есть длина строки. С помощью этой

00:18:00

функции мы можем узнать, сколько символов

00:18:03

имеется в указанном тексте, то есть вот

00:18:04

пишу ДЛСТР, указываю данные текст и нам

00:18:07

говорится, что в нём имеется 24 символа. С

00:18:10

помощью же функции поиск мы можем узнать

00:18:12

начальную позицию первого вхождения

00:18:14

определенных символов в проверяемом

00:18:16

тексте. То есть смотрите, искать будем,

00:18:18

например, слово «Юг» вот в этом изначальном

00:18:20

диапазоне. Закрываю скобку, Enter и видим:

00:18:23

слово Юг у нас начинается с шестнадцатого символа

00:18:26

данного текста. Еще одна группа функций

00:18:28

проработана, так что переходим к

00:18:29

последней. Итак, СЕГОДНЯ - простейшая

00:18:32

функция, но крайне полезная. С её помощью

00:18:34

мы можем получать указание текущей даты.

00:18:36

Вот пишу СЕГОДНЯ, открываю и сразу закрываю

00:18:39

скобку, Enter и Вы только что узнали дату

00:18:41

записи этого видео - 2 декабря 2021 года.

00:18:44

Функция ТДАТА действует схожим образом и

00:18:47

отличается тем, что также указывает и

00:18:49

текущее время. То есть смотрите, ввожу

00:18:51

функцию ТДАТА, в скобках вводить ничего

00:18:53

не надо, Enter и теперь Вы так же знаете

00:18:55

во сколько я записывал это видео. Окей,

00:18:58

следующая подгруппа функций позволяет

00:19:00

выделить определенные элементы из даты

00:19:02

или же времени. Смотрите, пишу ГОД,

00:19:04

указываю вот эту дату и в итоге мы

00:19:06

получаем год 2021. Если бы здесь была

00:19:09

определенная дата, например, 2022-го года, то

00:19:12

именно этот год был бы в итоге указан и

00:19:14

здесь. Приведенные ниже функции работают

00:19:17

аналогичным образом. Вот выделяем из даты

00:19:19

месяц, вот день. Часы, секунды и

00:19:21

минуты также успешно выделяются из вот

00:19:23

этой даты. Кстати, как можете заметить, при

00:19:26

каждом изменении в рабочей книге функция

00:19:28

ТДАТА поправляет текущее время. То

00:19:30

есть она постоянно обновляет свой

00:19:31

результат, чтобы он был верным. Функция

00:19:33

СЕГОДНЯ делает на самом деле то же самое

00:19:35

просто мы этого пока не видим. Если же

00:19:37

этот файл открыть уже завтра, то мы увидим

00:19:39

новое значение даты. Окей. Остаются всего

00:19:41

три функции. Итак, ДАТА. С помощью этой

00:19:44

функции мы можем делать обратное вот

00:19:46

этим функциям действие, то есть мы можем

00:19:48

собрать из отдельных элементов даты

00:19:49

значение формата даты Microsoft Excel.

00:19:52

Вот, пишу ДАТА, открываю скобки и год

00:19:54

пусть будет, например, 2022, месяц январь,

00:19:57

цифра 1, и первое число. Enter и у нас

00:20:00

выводится нужная нам дата - новый год. Окей,

00:20:03

функция ДАТАМЕС. Важная функция.

00:20:05

Смотрите, что она делает. С помощью нее мы

00:20:07

можем получить дату, отстающую на

00:20:09

заданное количество месяцев от указанной

00:20:11

даты. То есть давайте я поменяю дату на

00:20:13

31 января 2022 года. Если нам нужно

00:20:15

значение даты отстающее от этой даты

00:20:18

скажем ровно на месяц вперед, то просто

00:20:20

добавить к этому дню скажем, например, 30

00:20:22

или 31 день мы не можем так, как в каждом

00:20:25

месяце свое количество дней. И вот эту

00:20:27

задачу, как раз решает функция ДАТАМЕС.

00:20:29

Просто указываем исходную дату и говорим,

00:20:31

в каком направлении нам нужна отстоящая

00:20:33

по месяцам дата. Пусть будет на один

00:20:35

месяц вперед. Enter и мы, как раз получаем

00:20:37

конец февраля - двадцать восьмое число.

00:20:39

Если внести -1, то мы получим конец

00:20:42

декабря предыдущего года, опять считай

00:20:44

вернулись к новому году. Отлично!

00:20:46

Последняя функция - ТЕКСТ. На самом деле

00:20:48

она выполняет очень даже продвинутую

00:20:50

функцию. Заинтересованным я бы

00:20:51

посоветовал целиком посмотреть вот это

00:20:53

видео - оно длинное и достаточно тяжелое,

00:20:56

но после его просмотра Вы узнаете,

00:20:58

гарантирую, просто массу новых для себя

00:20:59

вещей. Пока же мы приведем простенький

00:21:01

пример, который будет, однако, полезен для

00:21:03

всех. С помощью функций ТЕКСТ мы можем

00:21:05

выделять определенные части из дат. То

00:21:07

есть смотрите, пишу ТЕКСТ и указываю дату.

00:21:10

Пусть это будет сегодняшняя дата. Затем

00:21:12

во втором аргументе мы говорим, как нам

00:21:14

нужно отформатировать указанное значение.

00:21:16

При работе с датами есть вот такие

00:21:18

варианты. Давайте я, например, введу четыре

00:21:20

буквы «Д» и в итоге мы узнаем, что второе

00:21:22

декабря 2021 года это четверг. Кроме того,

00:21:25

предположим, нам нужно прописать короткое

00:21:27

указание месяца этой даты пишем вместо

00:21:30

четырех букв «Д» три буквы «М», и получаем в

00:21:32

результате то самое короткое указание

00:21:34

название месяца. Эти указания можно

00:21:37

помимо всего прочего комбинировать в

00:21:38

самых разных вариантах. Вот например

00:21:40

можно указать вот такой вариант, и так

00:21:42

далее. Вариантов здесь просто огромное

00:21:44

количество. Вот мы с Вами и разобрали

00:21:45

набор, пожалуй, самых важных и

00:21:47

обязательных функций Excel. Надеюсь, этот

00:21:49

видеоурок был для Вас полезным и

00:21:51

интересным. Если это так, то

00:21:52

подписывайтесь на канал и мы с Вами

00:21:54

увидимся в следующем видео. Пока!

Описание:

В этом видео представлена подборка 41 функции Excel , которые, пожалуй, можно назвать самыми важными. Ссылка для скачивания файла из видео: https://docs.google.com/spreadsheets/d/1yQsx0fTcNBnvbbiSQQlC06aEzoBMewrY/edit?usp=sharing&ouid=104968505004400136199&rtpof=true&sd=true Хочешь поддержать мой канал? Это можно сделать с помощью функции «Спонсорство»: https://www.youtube.com/channel/UCVWD83UyE_wyWo1xuUQ0duA/join

Готовим варианты загрузки

Популярные

HD видео

Только звук

Все форматы

* — Если видео проигрывается в новой вкладке, перейдите в неё, а затем кликните по видео правой кнопкой мыши и выберите пункт "Сохранить видео как..."

** — Ссылка предназначенная для онлайн воспроизведения в специализированных плеерах

Вопросы о скачивании видео

Как можно скачать видео "Формулы Excel - Топ 41 функция - они пригодятся каждому!"?

Сайт http://unidownloader.com/ — лучший способ скачать видео или отдельно аудиодорожку, если хочется обойтись без установки программ и расширений. Расширение UDL Helper — удобная кнопка, которая органично встраивается на сайты YouTube, Instagram и OK.ru для быстрого скачивания контента.
Программа UDL Client (для Windows) — самое мощное решение, поддерживающее более 900 сайтов, социальных сетей и видеохостингов, а также любое качество видео, которое доступно в источнике.
UDL Lite — представляет собой удобный доступ к сайту с мобильного устройства. С его помощью вы можете легко скачивать видео прямо на смартфон.

Какой формат видео "Формулы Excel - Топ 41 функция - они пригодятся каждому!" выбрать?

Наилучшее качество имеют форматы FullHD (1080p), 2K (1440p), 4K (2160p) и 8K (4320p). Чем больше разрешение вашего экрана, тем выше должно быть качество видео. Однако следует учесть и другие факторы: скорость скачивания, количество свободного места, а также производительность устройства при воспроизведении.

Почему компьютер зависает при загрузке видео "Формулы Excel - Топ 41 функция - они пригодятся каждому!"?

Полностью зависать браузер/компьютер не должен! Если это произошло, просьба сообщить об этом, указав ссылку на видео. Иногда видео нельзя скачать напрямую в подходящем формате, поэтому мы добавили возможность конвертации файла в нужный формат. В отдельных случаях этот процесс может активно использовать ресурсы компьютера.

Как скачать видео "Формулы Excel - Топ 41 функция - они пригодятся каждому!" на телефон?

Вы можете скачать видео на свой смартфон с помощью сайта или pwa-приложения UDL Lite. Также есть возможность отправить ссылку на скачивание через QR-код с помощью расширения UDL Helper.

Как скачать аудиодорожку (музыку) в MP3 "Формулы Excel - Топ 41 функция - они пригодятся каждому!"?

Самый удобный способ — воспользоваться программой UDL Client, которая поддерживает конвертацию видео в формат MP3. В некоторых случаях MP3 можно скачать и через расширение UDL Helper.

Как сохранить кадр из видео "Формулы Excel - Топ 41 функция - они пригодятся каждому!"?

Эта функция доступна в расширении UDL Helper. Убедитесь, что в настройках отмечен пункт «Отображать кнопку сохранения скриншота из видео». В правом нижнем углу плеера левее иконки «Настройки» должна появиться иконка камеры, по нажатию на которую текущий кадр из видео будет сохранён на ваш компьютер в формате JPEG.

Сколько это всё стоит?

Нисколько. Наши сервисы абсолютно бесплатны для всех пользователей. Здесь нет PRO подписок, нет ограничений на количество или максимальную длину скачиваемого видео.