📚 Информатика 11 класс

Встроенные функции и их использование

Полное руководство по работе со встроенными функциями в электронных таблицах: от базовых математических до сложных финансовых расчётов

Введение: Твой невидимый помощник в мире данных

Представь, что у тебя есть личный ассистент, который мгновенно выполняет сложные расчёты, анализирует огромные массивы информации и никогда не устаёт. Звучит как фантастика? На самом деле, такой помощник уже существует — это встроенные функции электронных таблиц.

Когда стример анализирует статистику своих трансляций, когда компания прогнозирует продажи, когда учёный обрабатывает результаты эксперимента — везде работают функции. Они превращают сырые данные в понятную информацию и помогают принимать решения.

Давайте разберёмся, как работает этот инструмент и почему он стал незаменимым в современном мире данных.

Встроенные функции — это ваши умные помощники, готовые в любой момент обработать данные и выполнить сложные вычисления

3.1. Общие сведения о функциях

Что такое функция и почему это важно?

Математическая основа

Ты уже знаком с понятием функции из алгебры: это правило, которое каждому входному значению (аргументу) сопоставляет ровно одно выходное значение (результат). Например, f(x) = x² — это функция, которая возводит число в квадрат.

В электронных таблицах функции работают по тому же принципу, но их возможности гораздо шире:

Встроенная функция

Это готовая процедура (мини-программа), которая:

Принимает один или несколько аргументов (входных данных)
Выполняет определённые операции по заранее написанному алгоритму
Возвращает результат

Почему функции — это революция?

Представь, что тебе нужно найти среднее арифметическое 1000 чисел. Без функций ты бы писал формулу:

=(A1+A2+A3+...+A1000)/1000

Это займёт часы и почти гарантированно содержит ошибки.

С функцией:

=СРЗНАЧ(A1:A1000)

Одна строка — и готово.

            Функции дают нам:
            Скорость: мгновенное выполнение сложных операций
Точность: отсутствие человеческих ошибок
Масштабируемость: одна формула работает с любым объёмом данных
Переиспользование: написали один раз — применяем везде

        

Функция как фабрика: принимает сырые данные, обрабатывает их по определённому алгоритму и выдаёт готовый результат

Анатомия функции: из чего она состоит?

Любая функция в электронных таблицах имеет четкую структуру:

Имя функции

Краткое название, отражающее её назначение:

СУММ — суммирует числа
КОРЕНЬ — извлекает квадратный корень
ЕСЛИ — проверяет условие

Лайфхак: Имена функций — это сокращения от английских или русских слов. Если не помнишь название, начни вводить по смыслу — редактор предложит варианты.

Аргументы

Данные, с которыми работает функция. Они записываются в круглых скобках после имени и разделяются точкой с запятой:

=ИМЯ_ФУНКЦИИ(аргумент1; аргумент2; аргумент3)

Типы аргументов:

Числа: =КОРЕНЬ(25) → 5
Текст: =ДЛСТР("Привет") → 6
Ссылки на ячейки: =СУММ(A1:A10)
Диапазоны: =МАКС(B2:B50)
Другие функции: =ОКРУГЛ(СРЗНАЧ(C1:C20); 2)

Классификация по количеству аргументов

Можно выделить функции:

с одним аргументом, например КОРЕНЬ;
с несколькими аргументами, количество которых фиксировано, например ОКРУГЛ;
с нефиксированным количеством аргументов, например МАКС;
с некоторыми необязательными аргументами, например РАНГ;
без аргументов, например ТДАТА.

При использовании функции в формуле сначала указывается её имя, а затем в скобках указывается список аргументов через точку с запятой:

Функция	Запись в Microsoft Excel
Квадратный корень	`КОРЕНЬ(А1)`
Округление числа до заданного количества десятичных разрядов	`ОКРУГЛ(G13;2)`
Среднее значение	`СРЗНАЧ(А3:В10)`
Максимальное значение	`МАКС(А3:В10; С8:С12; М6)`
Текущие дата и время	`ТДАТА()`

Структура функции напоминает конструктор: основной блок (имя) и присоединяемые части (аргументы), которые можно комбинировать по-разному

Назначение каждой функции, наличие аргументов, их количество и тип можно посмотреть в Справке или в комментариях при вводе функции в формулу.

Вставить функцию в формулу можно несколькими способами:

использовать кнопки категорий функций в группе Библиотека функций вкладки Формулы на ленте;
воспользоваться инструментом Вставить функцию в группе Библиотека функций или в строке формул;
ввести функцию непосредственно в ячейку или в поле Строка формул.

Рассмотрим более подробно второй способ.

Если щёлкнуть на кнопке Вставить функцию строки формул, то откроется окно Мастер функций, а в текущую ячейку автоматически вставится знак «=» (если в этой ячейке ввод формулы ещё не начинался). В окне Мастер функций в списке поля Категория можно выбрать нужную категорию, после чего в списке поля Выберите функцию выбрать нужную функцию.

После выбора имени функции в текущую ячейку автоматически вставляется имя функции и пара круглых скобок, а также открывается окно Аргументы функции с полями для ввода аргументов этой функции.

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

Если аргументом является число или текст, то его нужно вводить в поле с клавиатуры. Если аргументом является ссылка на ячейки, то её также можно ввести с клавиатуры, но лучше выделить соответствующие ячейки с помощью мыши.

Для некоторых функций Microsoft Excel автоматически предлагает первый аргумент. Например, для функции СУММ предлагается найти сумму чисел диапазона ячеек, заполненных числовыми данными, которые находятся над ячейкой с формулой или слева от неё, если верхний диапазон ячеек пуст.

Задание для самостоятельной работы: Два других способа вставки функции в формулу исследуйте самостоятельно. Назовите их основные отличия друг от друга. Что у них общего? Какой из способов будете применять вы?

3.2. Математические и статистические функции

Для решения математических задач (решения уравнений, построения графиков функций) вам могут быть полезны функции, представленные в таблице ниже. Здесь же представлены некоторые из статистических функций, позволяющих автоматизировать статистическую обработку данных. С их помощью можно вычислить наименьшее значение, подсчитать количество ячеек, содержащих заданную информацию, и т. д.

Категории функций: инструмент для любой задачи

Как вы думаете, сколько встроенных функций в современных табличных процессорах? В Excel их более 450! Чтобы не потеряться в этом многообразии, функции разделены на категории по назначению.

Математические функции: расчёты и вычисления

Когда используются: анализ данных, научные расчёты, финансовое моделирование, создание графиков.

Функция	Количество аргументов	Запись	Результат
ABS(число)	1	`ABS(F5)`	Модуль (абсолютная величина) числа
SIN(число)	1	`SIN(D4)`	Синус числа (угла в радианах)
РАДИАНЫ(число)	1	`РАДИАНЫ(А10)`	Перевод из градусной меры угла в радианную
ГРАДУСЫ(число)	1	`ГРАДУСЫ(С6)`	Перевод радианной меры угла в градусы
ПИ()	0	`ПИ()`	Значение числа π
СТЕПЕНЬ(число; степень)	2	`СТЕПЕНЬ(А2; 5)`	Число, возведённое в степень
СУММ(число1; [число2]; ...)	От 1 до 255; все, кроме первого, необязательные	`СУММ(А3:В10)`	Сумма чисел, указанных в скобках
ОКРУГЛ(число; число_разрядов)	2	`ОКРУГЛ(G13; 2)`	Число, округлённое до заданного количества десятичных разрядов
СЧЁТ(значение1; [значение2]; ...)	От 1 до 255; все, кроме первого, необязательные	`СЧЁТ(А3:В10; G13)`	Количество чисел в указанных ячейках
МИН(число1; [число2]; ...)	От 1 до 255; все, кроме первого, необязательные	`МИН(А3:В10; G13:G23)`	Наименьшее среди указанных в скобках чисел
РАНГ(число; ссылка на список; [порядок])	3; третий необязательный	`РАНГ(А1; $А$1:$А$5; 1)`	Ранг числа в списке чисел

Реальный кейс:

Разработчик игры рассчитывает траекторию полёта снаряда. Ему нужны тригонометрические функции для определения угла и синус/косинус для расчёта координат.

=ОКРУГЛ(начальная_скорость * COS(угол_в_радианах) * время; 2)

Статистические функции: от хаоса к порядку

Когда используются: анализ результатов опросов, обработка экспериментальных данных, аналитика продаж.

Функция РАНГ — подробно

Рассмотрим более детально работу статистической функции РАНГ, имеющую формат:

РАНГ(число; ссылка на список; [порядок])

Здесь:

число — это число, для которого определяется ранг (порядок);
ссылка на список — ссылка на список, которому принадлежит число (нечисловые значения в ссылке игнорируются);
порядок — способ упорядочения значений списка:
- 0 или отсутствие параметра — определяет ранг (позицию, место) числа в списке так, как если бы список был отсортирован в порядке убывания (т. е. максимальному значению присваивается ранг равный 1, чуть меньшему числу — ранг 2 и т. д.);
- число, не равное 0, — определяет ранг числа так, как если бы список сортировался в порядке возрастания (т. е. минимальному числу присваивается ранг 1, чуть большему числу — ранг 2 и т. д.).

Функция РАНГ присваивает повторяющимся числам одинаковый ранг. При этом наличие повторяющихся чисел влияет на ранг последующих чисел.

Практический пример: Топ стримеров по количеству зрителей

Стример	Зрители	Ранг (убывание)
Алекс	45000	1
Мария	45000	1
Иван	12000	3
Анна	8000	4

Обрати внимание: Алекс и Мария получили одинаковый ранг 1, а следующий ранг — 3 (ранг 2 «пропущен»). Это важная особенность функции.

Задание: В ячейку В1 введена и скопирована в В2:В6 одна из двух следующих формул:

=РАНГ(A1; $A$1:$A$6; 1);
=РАНГ(A1; $A$1:$A$6; 0).

По какой из формул представлены результаты вычислений в столбце В?

Как вы можете объяснить отсутствие числа 2 среди значений ячеек диапазона С1:С6, если это — результаты вычислений по другой из приведённых выше формул?

A	B	C
45	5	1
12	4	3
8	3	4
45	5	1
6	2	5
3	1	6

Статистические функции наводят порядок в данных: находят среднее, выявляют лидеров и отстающих, определяют места в рейтинге

3.3. Логические функции

Логическая функция — функция, результатом которой является ИСТИНА или ЛОЖЬ.

К категории логических относятся функции ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

Базовые логические операторы

Функции И, ИЛИ, НЕ позволяют создавать составные логические выражения. Формат этих функций:

И(логическое_значение1; [логическое_значение2]; ...)
ИЛИ(логическое_значение1; [логическое_значение2]; ...)
НЕ(логическое_значение)

Аргументами функций И, ИЛИ, НЕ могут быть логические выражения или ссылки на ячейки, содержащие логические значения.

1. И (AND)

Возвращает ИСТИНА, только если все условия истинны.

=И(A1>10; A1<100)  // Число больше 10 И меньше 100?

Условие 1	Условие 2	Результат И
ИСТИНА	ИСТИНА	ИСТИНА
ИСТИНА	ЛОЖЬ	ЛОЖЬ
ЛОЖЬ	ИСТИНА	ЛОЖЬ
ЛОЖЬ	ЛОЖЬ	ЛОЖЬ

2. ИЛИ (OR)

Возвращает ИСТИНА, если хотя бы одно условие истинно.

=ИЛИ(B1="отлично"; B1="хорошо")  // Оценка хорошая?

3. НЕ (NOT)

Инвертирует логическое значение.

=НЕ(C1>100)  // То же самое, что C1<=100

Функция ЕСЛИ: сердце логики

Функция ЕСЛИ имеет формат:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Значение этой функции определяется так:

если лог_выражение имеет значение ИСТИНА, то значение функции равно значению выражения значение_если_истина;
если лог_выражение имеет значение ЛОЖЬ, то значение функции равно значению выражения значение_если_ложь.

Простой пример: Проверка на совершеннолетие

=ЕСЛИ(A1>=18; "Совершеннолетний"; "Несовершеннолетний")

Вложенные ЕСЛИ: Можно создавать многоуровневые проверки

=ЕСЛИ(A1>=90; "Отлично"; 
  ЕСЛИ(A1>=75; "Хорошо"; 
    ЕСЛИ(A1>=60; "Удовлетворительно"; "Неудовлетворительно")
  )
)

Интересный факт: Вложенные ЕСЛИ — это, по сути, реализация структуры if-else-if из программирования прямо в таблице!

Условные функции: умная обработка данных

Табличные процессоры имеют и такие функции, которые вычисляют сумму, среднее арифметическое, количество не всех значений из диапазонов ячеек, а только тех, которые удовлетворяют определённому условию:

функция СУММЕСЛИ вычисляет сумму тех чисел из указанного диапазона, которые удовлетворяют заданному условию;
функция СРЗНАЧЕСЛИ вычисляет среднее арифметическое тех чисел из указанного диапазона, которые удовлетворяют заданному условию;
функция СЧЁТЕСЛИ подсчитывает количество ячеек из указанного диапазона, содержимое которых удовлетворяет заданному условию.

Пример СУММЕСЛИ

Сумма продаж товаров дороже 1000 руб.

=СУММЕСЛИ(B2:B50; ">1000")

Пример СЧЁТЕСЛИ

Сколько студентов получили "отлично"?

=СЧЁТЕСЛИ(D2:D100; "отлично")

Практический пример: Решение логического уравнения

Пример 1. Выясним, сколько решений имеет логическое уравнение:

((x1 → x2) → (x3 → x4)) = 1

Преобразуем исходное уравнение, выразив импликацию через инверсию и дизъюнкцию:

(¬x1 ∨ x2) ∨ (¬x3 ∨ x4) = (x1 & ¬x2) ∨ ¬x3 ∨ x4 = 1

Запишем формулу для вычисления логического выражения с помощью логических функций Microsoft Excel:

=ИЛИ(И(X1;НЕ(X2)); НЕ(X3); X4)

Итак, исходное уравнение имеет 13 решений — столько раз встречается значение ИСТИНА в диапазоне результатов. Для подсчёта этого значения можно воспользоваться функцией СЧЁТЕСЛИ.

Вопрос: Вспомните другой способ решения этого уравнения.

Логические функции — это развилка в программе: в зависимости от условия выбирается один из двух путей обработки данных

3.4. Финансовые функции

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

Рассмотрим несколько финансовых функций, которыми полезно уметь пользоваться каждому человеку, планирующему взять в банке кредит или сделать вклад.

Почему они особенные?

Финансовые расчёты учитывают изменение стоимости денег во времени. Тысяча рублей сегодня НЕ равна тысяче рублей через год из-за инфляции и процентов.

Базовые понятия

Аргументами финансовых функций являются:

ставка — процентная ставка за период;
плт — выплата, производимая в каждый период (месяц, квартал, год и т. п.);
пс — приведённая (нынешняя) стоимость инвестиции;
кпер — общее число периодов платежей по кредиту;
бс — будущая стоимость инвестиции;
тип — число 0, если оплата в конце периода; число 1, если оплата в начале периода (по умолчанию — 0).

Ключевые функции

1. КПЕР — сколько времени нужно, чтобы погасить кредит?

Функция КПЕР(ставка; плт; пс; [бс]; [тип]) возвращает количество периодов платежей для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Пример 2. Пусть ставка кредита в некотором банке составляет 18% годовых. Клиент хочет взять кредит на сумму 100 000 руб. и может выплачивать банку по 4000 руб. ежемесячно. Нужно определить, за сколько периодов клиент сможет погасить этот кредит.

Обязательные аргументы функции:

ставка — годовая ставка в процентах, разделённая на количество периодов платежей за год (в нашем примере это 18%/12);
плт — сумма, которую клиент ежемесячно должен возвращать банку (в нашем примере это –4000, т. к. эти деньги отдаются);
пс — размер кредита (в нашем примере это 100 000).

Формула для вычисления количества периодов выплат для погашения взятого кредита будет иметь вид:

=КПЕР(18%/12; –4000; 100000)

Получаем приблизительно 32 периода (месяца), т. е. более 2,5 лет.

2. ПС — на какую сумму можно взять кредит?

Функция ПС(ставка; кпер; плт; [бс]; [тип]) возвращает приведённую (к текущему моменту) стоимость инвестиции, представляющую собой общую сумму, которая на данный момент равноценна ряду будущих выплат.

Пример 3. Выясним, на какую сумму клиент может взять кредит, если ставка 19% годовых, а выплачивать он может по 12 000 руб. на протяжении двух лет (24 периода).

Обязательные аргументы функции:

ставка (19%/12);
кпер — общее количество периодов выплаты платежей по кредиту (24);
плт (–12 000).

Формула для вычисления размера кредита будет иметь вид:

=ПС(19%/12; 24; –12000)

Получаем приблизительно 238 054 руб.

3. СТАВКА — какая реальная процентная ставка?

Функция СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) вычисляет процентную ставку за период (а не за год).

Пример 4. Пусть клиент хочет взять кредит 100 000 руб. на 2 года. При этом выплачивать он может по 5000 руб. ежемесячно. Может ли он воспользоваться предложением банка, ставка по кредитам в котором составляет 20%?

Обязательные аргументы функции:

кпер (24);
плт (–5000);
пс (100 000).

Формула для вычисления ставки будет иметь вид:

=СТАВКА(24; –5000; 100000)

В результате вычислений получаем процентную ставку за месяц 1,51308%. Соответственно, процентная ставка за год составит 18,157% (1,51308 ⋅ 12).

Таким образом, клиенту не рекомендуется брать кредит в банке, ставка по кредитам в котором составляет 20%.

4. БС — сколько будет на вкладе через N лет?

Функция БС(ставка; кпер; плт; [пс]; [тип]) возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки. Иначе говоря, с её помощью можно вычислить сумму, которую выплатят клиенту за вклад под определённые проценты по окончании срока вклада.

Пример 5. Клиент хочет сделать вклад на 3 года на сумму 300 000 руб. под 11% годовых с ежемесячным начислением процентов. Выясним, какую сумму он получит по окончании срока вклада.

Аргументы функции:

ставка — годовая ставка в процентах, разделённая на количество периодов начисления процентов за год (в нашем примере это 11%/12);
кпер — количество периодов начисления процентов (3 ⋅ 12 = 36);
плт — сумма, которая добавляется к вкладу каждый период времени: 0 или отрицательное число (в нашем примере это 0, т. к. пополнение вклада клиентом не предусмотрено);
пс — начальная сумма вклада (в нашем примере это 300 000).

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

=БС(11%/12; 36; 0; –300000)

В результате вычислений получаем 416 663,58 руб.

С ежемесячным пополнением вклада

Пример 6. Клиент хочет сделать вклад на 2 года на сумму 100 000 руб. под 10,5% годовых с ежемесячным начислением процентов. При этом он имеет возможность ежемесячно пополнять вклад ещё на 2000 рублей. Выясним, какую сумму клиент получит по окончании срока вклада.

Для нахождения результата мы воспользуемся той же функцией, что и в примере 5. Отличие состоит в том, что аргумент плт в этом случае примет значение –2000.

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

=БС(10,5%/12; 24; –2000; –100000)

В результате вычислений получаем 176 409,84 руб.

Вопрос: Как изменится формула в примере 6, если клиент ежемесячно будет не пополнять счёт на 2000 руб., а снимать со счёта по 1000 руб.?

Финансовые функции моделируют рост денег во времени: вклады приносят проценты, как дерево — плоды, а кредиты требуют регулярных «взносов»

3.5. Текстовые функции

В основном табличные процессоры используются для работы с числами, но в них предусмотрена и возможность работы с текстом. Например, в электронные таблицы заносятся наименования товаров и услуг, фамилии, имена и отчества сотрудников, партнёров и клиентов, их адреса, телефоны и многое другое.

Для обработки текста в табличных процессорах имеется набор функций, которые можно использовать для определения длины текста, номера позиции первого вхождения символа в текст, части текста, который удовлетворяет определённому условию и др.

Аргументами текстовых функций могут быть текстовые данные (их нужно заключать в кавычки), ссылки на ячейки с текстом, ссылки на ячейки с числами.

Преобразование регистра

Функция СТРОЧН преобразует все буквы обрабатываемого текста в строчные, а функция ПРОПИСН, наоборот, — в прописные. Функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные буквы — строчными.

Функция	Что делает	Пример
`СТРОЧН()`	Все буквы строчные	`=СТРОЧН("EXCEL")` → "excel"
`ПРОПИСН()`	Все буквы прописные	`=ПРОПИСН("word")` → "WORD"
`ПРОПНАЧ()`	Первая буква каждого слова — прописная	`=ПРОПНАЧ("иван иванов")` → "Иван Иванов"

Сравнение текста

Функция СОВПАД позволяет сравнить две текстовые строки в Microsoft Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае — ЛОЖЬ (функция учитывает регистр, но игнорирует различие в форматировании).

Задание: Какое значение появится в ячейке С1, если в неё записать формулу =СОВПАД(A1; B1)? Какое значение появится в ячейке С2, если в неё скопировать формулу из ячейки С1?

Очистка текста

Функция СЖПРОБЕЛЫ удаляет из текста все лишние пробелы, кроме одиночных между словами. Эту функцию полезно применять к данным, которые импортируются в рабочие листы Microsoft Excel из внешних источников.

Вопрос: Вспомните, как можно удалить все лишние пробелы из документа с помощью инструментов текстового процессора.

Кроме лишних пробелов импортируемые данные могут содержать и различные непечатаемые символы. Для удаления из текста всех непечатаемых символов предназначена функция ПЕЧСИМВ.

Анализ текста

ДЛСТР — возвращает длину строки в символах.

=ДЛСТР("Информатика")  → 11

Практическое применение: Проверка длины пароля

=ЕСЛИ(ДЛСТР(A1)<8; "Слишком короткий пароль"; "ОК")

Извлечение частей текста

ЛЕВСИМВ — =ЛЕВСИМВ(текст; количество) — символы слева
ПРАВСИМВ — =ПРАВСИМВ(текст; количество) — символы справа
ПСТР — =ПСТР(текст; начало; количество) — символы из середины

=ЛЕВСИМВ("Информатика"; 4)  → "Инфо"
=ПРАВСИМВ("Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript."; 6)  → "ail.ru"
=ПСТР("Информатика"; 6; 3)  → "рма"

Лайфхак: Извлечение домена из email

=ПРАВСИМВ(A1; ДЛСТР(A1) - НАЙТИ("@"; A1))

Для "Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript." → "gmail.com"

Задание: Выскажите свои предположения о назначении текстовых функций ДЛСТР, ЛЕВСИМВ, ПРАВСИМВ, ПСТР по результатам их работы:

В1=ДЛСТР(А1)
В1=ЛЕВСИМВ(А1;3)
В1=ПРАВСИМВ(А1;3)
В1=ПСТР(А1;3;2)

Поиск и замена

Функции НАЙТИ и ПОИСК очень похожи. Они находят вхождение одной строки в другую и возвращают положение первого символа искомой фразы относительно начала текста. Различие в том, что первая учитывает регистр, а вторая — нет.

=НАЙТИ("Of"; "Microsoft Office")  → 11
=ПОИСК("of"; "Microsoft Office")  → 11

Функция ПОДСТАВИТЬ заменяет определённый текст или символ на новое значение. Её применяют, когда заранее известно, какой текст необходимо заменить, а не его местоположение.

Функция ЗАМЕНИТЬ заменяет символы в заранее известном месте строки на новые. Функцию применяют, когда известно, где располагается текст, при этом сам он не важен.

=ПОДСТАВИТЬ("Привет мир"; "мир"; "мир!")  → "Привет мир!"
=ЗАМЕНИТЬ("Excel 2010"; 7; 4; "2024")  → "Excel 2024"

Удаление всех пробелов:

=ПОДСТАВИТЬ(A1; " "; "")

Объединение текста

Функция СЦЕПИТЬ последовательно объединяет значения указанных аргументов в одну строку.

=СЦЕПИТЬ("Иван"; " "; "Иванов")  → "Иван Иванов"

Современный способ: Оператор &

=A1 & " " & B1

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задаётся как первый аргумент функции, а количество повторов — как второй.

Задание 1: Чему равен результат вычисления по формуле ячейки С2, если результат вычисления по формуле ячейки А2 равен 6?

Функцию ПОВТОР можно применить и для «графического» представления числовых значений. Например, с её помощью можно визуализировать информацию об успеваемости некоторого ученика, получившего в текущем триместре 40 отметок «отлично», 45 — «хорошо» и 15 — «удовлетворительно».

Мощные комбинации

Подсчёт вхождений символа:

Сколько раз буква "о" встречается в слове "колокол"?

=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(A1; "о"; ""))

Логика: Удаляем все "о", вычитаем новую длину из старой.

Результат: 3

Задания:

Что будет отображено в ячейках В1 и В2?
С помощью какой из двух последних рассмотренных функций можно удалить все пробелы из текстовой строки? Как это сделать?
В ячейке содержится текст «колокол» (без кавычек). Что будет результатом вычислений по формуле: =ДЛСТР(A1)–ДЛСТР(ПОДСТАВИТЬ(A1;"о";""))?
Сформулируйте алгоритм подсчёта количества вхождений определённого символа в заданную строку.

Текстовые функции — это инструменты для «обработки» слов и предложений: обрезка, склейка, поиск, замена и трансформация

Практическая работа с функциями

Мастер функций: твой проводник

Способы вставки функции:

Кнопки на ленте → Формулы → Библиотека функций (по категориям)
Мастер функций → кнопка fx в строке формул или группе Библиотека функций
Ручной ввод → начинаешь печатать =СУММ и выбираешь из подсказок

Мастер функций — пошагово:

Шаг 1: Выбор функции

Открывается окно с категориями
Выбираешь категорию (Математические, Статистические, и т.д.)
Выбираешь нужную функцию из списка
Видишь краткое описание

Шаг 2: Ввод аргументов

Открывается окно "Аргументы функции"
Для каждого аргумента — поле ввода
Способ 1: Печатаешь значение с клавиатуры
Способ 2: Кликаешь на кнопку "Свернуть" и выделяешь ячейки мышью
Видишь результат в реальном времени

Полезные фишки:

Excel часто предлагает аргументы автоматически (например, для СУММ — ближайший диапазон с числами)
В окне аргументов видно текущее значение каждого аргумента
Можно переходить между аргументами клавишей Tab

Вложенные функции: композиция операций

Ключевая идея: Результат одной функции может быть аргументом другой.

Пример: Среднее всех чисел, округлённое до целого

=ОКРУГЛ(СРЗНАЧ(A1:A100); 0)

Как читать (справа налево / изнутри наружу):

Сначала вычисляется СРЗНАЧ(A1:A100) → получаем, например, 15.7834
Затем ОКРУГЛ(15.7834; 0) → 16

Сложная вложенность: Ранг числа среди абсолютных значений

=РАНГ(ABS(B5); ABS($B$1:$B$10); 0)

Правило: Функции выполняются от самых внутренних к самым внешним.

САМОЕ ГЛАВНОЕ

1. Встроенные функции — это готовые алгоритмы

Они превращают электронную таблицу из калькулятора в мощный инструмент анализа данных. Они экономят время, исключают ошибки и позволяют работать с любым объёмом информации.

2. Универсальная структура

Все функции состоят из имени и аргументов в круглых скобках. Понимание типов аргументов (числа, текст, ссылки, диапазоны) — ключ к свободному использованию любой функции.

3. Категории функций отражают типы задач

Математические — для расчётов, статистические — для анализа массивов данных, логические — для принятия решений, финансовые — для моделирования денежных потоков, текстовые — для обработки строк. Выбор правильной категории — половина решения задачи.

4. Композиция функций создаёт мощные инструменты

Вкладывая одну функцию в другую, вы строите сложную логику прямо в ячейке. Это основа автоматизации в таблицах и фундамент для понимания программирования.

5. Функции — это мост между данными и решениями

Они превращают сырую информацию в понятные выводы, будь то кредитный калькулятор, система оценки знаний или аналитика игровой статистики. Овладение функциями — это овладение языком данных.

Проверь себя

Концептуальные вопросы

1. Аналогия из жизни

Объясни младшему школьнику, что такое функция в Excel, используя метафору кулинарного рецепта. Что в этой аналогии является аргументами, а что — результатом?

2. Критическое мышление

В примере с функцией РАНГ два спортсмена получили одинаковый ранг 1, а следующий ранг — 3. Предложи реальную ситуацию (не из спорта), где такая логика была бы несправедливой. Как можно было бы модифицировать функцию?

3. Связь с программированием

Функция ЕСЛИ реализует структуру if-else. Изобрази блок-схему для вложенной формулы из раздела про логические функции (система оценки "Отлично/Хорошо/Удовлетворительно/Неудовлетворительно").

Практические задачи

4. Финансовая грамотность

Банк А предлагает кредит 200 000 руб. под 18% годовых. Банк Б — тот же кредит под 17%, но с комиссией 5000 руб. за открытие.

Составь формулу для расчёта ежемесячного платежа на 3 года в обоих случаях
Какой кредит выгоднее и на сколько?

5. Текстовый анализ

В ячейке A1 находится email: "Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript."

Напиши формулы для извлечения:

Имени пользователя до @ ("student.2024")
Домена после @ ("university.edu.ru")
Доменной зоны (последние буквы после точки: "ru")

6. Комбинированная задача

У тебя есть таблица с результатами теста (баллы от 0 до 100). Нужно:

Вычислить средний балл класса
Определить, сколько учеников набрали более 75 баллов
Для каждого ученика вывести оценку по шкале:
- 90-100: "Отлично (A)"
- 75-89: "Хорошо (B)"
- 60-74: "Удовлетворительно (C)"
- <60: "Неудовлетворительно (F)"
Найти ранг каждого ученика

Запиши все необходимые формулы.

Исследовательское задание

7. Мини-проект

Создай "Калькулятор личных финансов", который:

Принимает на вход: текущие сбережения, ежемесячный доход, ежемесячные расходы, желаемая покупка (цена)
Рассчитывает: через сколько месяцев можно купить желаемое, если откладывать остаток от дохода
Учитывает: возможность положить деньги на вклад под процент
Даёт рекомендацию: что выгоднее — копить на депозите или копить без процентов, но иметь возможность купить раньше

Используй минимум 5 разных функций из разных категорий.

Совет на прощание

Лучший способ освоить функции — экспериментировать. Открой Excel, создай таблицу с любыми данными (твоя статистика в играх, расписание, список покупок) и попробуй проанализировать её с помощью изученных функций. Ошибки — это нормально, они учат понимать логику работы!