Шпаргалка по формулам Excel: руководство для среднего уровня

Итак, вам удалось освоить основы Excel — спасибо вам! Теперь вы решили подняться на ступеньку выше и погрузиться в более продвинутые вещи, верно? Что ж, вам повезло, потому что мы собрали для вас удобную промежуточную шпаргалку по Excel!

Эта промежуточная шпаргалка Microsoft Excel охватывает широкий круг тем, которые помогут повысить ваши навыки в числовом анализе, обработке текста и сложной логике даты и времени.

Имея Excel Intermediate Cheat Sheet в качестве настольного справочника, вы можете глубже погрузиться в более мощные формулы и функции Microsoft Excel.

Теперь обязательно прочитайте, а также распечатайте и сохраните шпаргалку ниже!

Давайте начнем!

Промежуточные математические формулы

Наша шпаргалка для начинающих охватывает наиболее распространенные математические функции для начинающих, такие как функция СУММ. Промежуточные пользователи должны ознакомиться с этими более сложными функциями:

• ABS() возвращает абсолютное значение числа.

• SQRT() возвращает квадратный корень числа

• RAND() предоставляет случайное число от 0 до 1

Условная логика

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

• СУММЕСЛИМН(диапазон_суммы, диапазон_критериев1, критерий1, [….])

• СЧЁТЕСЛИМН(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2], …)

• СРЗНАЧЕСЛИМН(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2], …)

Функции оценивают предоставленные критерии и выдают логическое значение ИСТИНА или ЛОЖЬ.

Предположим, у вас есть следующие данные о продажах в столбцах A, B и C:

Чтобы рассчитать сумму продаж красных футболок, используйте формулу СУММЕСЛИМН:

=СУММЕСЛИМН(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

Чтобы подсчитать количество рядов с красными футболками, используйте формулу СЧЁТЕСЛИМН:

=СЧЁТЕСЛИМН(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

Чтобы рассчитать средние продажи красных футболок, используйте формулу СРЗНАЧЕСЛИМН:

=СРЗНАЧЕСЛИ(C2:C6, A2:A6, «Футболка», B2:B6, «Красный»)

Промежуточные статистические формулы

Помимо основных функций, таких как MIN и MAX, в Excel есть более широкий набор статистических формул для пользователей среднего уровня. Вот некоторые из наиболее полезных:

1. МИНА и МАКСА

Наиболее часто используемые функции MIN и MAX игнорируют текстовые значения.

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

Отличия показаны на картинке ниже. Первая строка оценивает минимальное значение 10, а вторая строка оценивает как 0 из-за наличия текстового значения.

2. СЧЁТЕ и СЧЁТЕСЛИ

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

Функция СЧЁТЕСЛИ менее специфична, поскольку используется для подсчёта количества ячеек в диапазоне, удовлетворяющих определённому условию или критерию.

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

Формулы Excel для финансового анализа

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

• ПРОИЗВЕД(число1, [число2…])

• ЧАСТНОЕ(числитель, знаменатель)

• LOG(число, [база])

1. Функция ПРОДУКТА

Предположим, вы хотите рассчитать будущую стоимость первоначальных инвестиций в размере 1000 долларов США (ячейка B1) при годовой процентной ставке 4% (ячейка B2) через пять лет (ячейка B3).

Используйте функцию ПРОИЗВЕД, чтобы рассчитать будущую стоимость по этой формуле:

=B1 * ПРОИЗВЕД(1 + B2)^B3

Эта формула вернет будущую стоимость ваших инвестиций.

2. Функции ЧАСТНОЕ и ЛОГИН

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

Используйте функцию QUOTIENT в сочетании с функцией LOG, чтобы вычислить это следующим образом:

=ЧАСТНОЕ(ЛОГ(2)/ЛОГ(1 + В2), 1)

Эта формула вернет 17 с выборочными данными, что указывает на то, что потребуется 17 лет, чтобы инвестиции удвоились в стоимости при годовой процентной ставке 4%.

На этом рисунке показаны формулы на листе Excel :

Функции для инвестиционных сценариев

Это некоторые функции, которые подходят для конкретных финансовых сценариев. Один из них может быть именно тем, что вам нужно:

• NPV — функция NPV (чистая приведенная стоимость) вычисляет чистую приведенную стоимость инвестиций на основе ряда будущих денежных потоков на основе ставки дисконтирования.

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

• INTRATE — функция INTRATE вычисляет процентную ставку для полностью инвестированной ценной бумаги.

• ПЛТ — функция ПЛТ вычисляет общий платеж по долговой ценной бумаге.

• IRR – Функция IRR обеспечивает внутреннюю норму доходности.

• ДОХОДНОСТЬ . Функция ДОХОДНОСТЬ обеспечивает доходность ценной бумаги на основе процентной ставки, номинальной стоимости и срока погашения.

Промежуточные формулы даты и времени Excel

Основные функции даты и времени в Excel включают функции СЕЙЧАС и СЕГОДНЯ для текущей даты. Промежуточные пользователи также должны знать, как извлекать компоненты из заданной даты, используя:

• ДЕНЬ(дата)

• МЕСЯЦ(дата)

• ГОД(дата)

Формула =МЕСЯЦ(«23 апреля 2023») вернет результат 4 для 4-го месяца. Точно так же функции ДЕНЬ и ГОД вернут 23 и 2023 соответственно.

1. Промежуточные функции месяца

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

• EDATE(дата_начала, количество_месяцев)

• КОНМЕСЯЦА(дата_начала, количество_месяцев)

Например, формула =ДАТАМЕС(«23 апреля 2023», 2) вычислит дату двумя месяцами позже.

Формула =КОНМЕСЯЦА(23 апреля 2023 г.», 2) вычисляет конец месяца через два месяца.

В результате получается «30 июня 2023 года», в котором учтено, что в июне всего тридцать дней. Если вы укажете 3 месяца, результатом будет «31 июля 2023 года».

2. Функции промежуточной недели

Вы можете использовать комбинацию функций СУММ и ДЕНЬ НЕД для подсчета количества полных рабочих дней в диапазоне дат.

Функция ДЕНЬНЕД возвращает день недели для заданной даты. Второй параметр определяет систему нумерации по умолчанию с воскресенья по субботу.

Если у вас есть ряд дат в диапазоне ячеек B1:B40, используйте эту формулу для расчета рабочих дней:

=СУММ(–(ДЕНЬНЕД(B1:B40,2)>5))

3. Промежуточные функции времени.

Функция YEARFRAC вычисляет долю года между двумя датами.

Функция TIMEVALUE используется для преобразования времени, представленного в виде текста, в десятичное число, представляющее долю 24-часового дня.

Результатом является порядковый номер Excel, где 1 представляет полные 24-часовые сутки, 0,5 — 12 часов, 0,25 — 6 часов и т. д.

Предположим, у вас есть время в текстовом формате «17:45» в ячейке A1, и вы хотите преобразовать его в десятичное число. Это формула:

=ВРЕМЯЗНАЧ(A1)

Значение 0,74 представляет собой долю 24-часового дня, прошедшего за пятнадцать минут до шести часов.

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

Функции Excel для условной и логической алгебры

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

1. ЕСЛИ(условие, значение_если_истина, значение_если_ложь):

2. И(условие1, условие2, …)

3. ИЛИ(условие1, условие2, …)

4. НЕ(условие1, условие2, …)

Функция ЕСЛИ оценивает условие и возвращает разные значения в зависимости от того, является ли условие истинным или ложным.

Функции И, ИЛИ и НЕ проверяют, какие условия выполняются, и позволяют принимать соответствующие решения.

Вот несколько примеров, основанных на данных о продажах, которые мы использовали ранее.

1. Функция ЕСЛИ

Предположим, вы хотите отобразить текст «Низкие продажи», если продажи товаров меньше 15. Если продажи выше, вы хотите отобразить «Высокие продажи» в новом столбце.

Используйте эту формулу и скопируйте ее в другие строки:

=ЕСЛИ(C2

2. Функция И

Предположим, вы хотите отобразить TRUE или FALSE для футболок, проданных не менее 15 раз. Используйте эту формулу:

=И(A3=»Футболка», C3>=15)

3. Функция ИЛИ

Предположим, вы хотите проверить несколько условий и вернуть TRUE, если какое-либо из условий выполнено, и FALSE в противном случае, тогда вы должны использовать функцию ИЛИ.

Например, если у нас есть результаты теста в ячейках A1 и B1, и мы хотим знать, превышает ли какой-либо из них 80, мы можем использовать: =OR(A1>80, B1>80) . Это вернет ИСТИНА, если одна из оценок (или обе) больше 80, и ЛОЖЬ, если обе 80 или меньше.

4. НЕ Функция

Предположим, вы хотите проверить, не является ли предмет толстовкой. Используйте эту формулу:

= НЕ (A2 = «Толстовка с капюшоном»)

Ссылки на ячейки

Новичок быстро освоит использование абсолютных и относительных ссылок, таких как $A$1 или B2. Пользователи среднего уровня должны ознакомиться с использованием косвенных ссылок, индексов и смещений:

1. ДВССЫЛ(ref_text)

2. ИНДЕКС(диапазон, номер_строки, номер_столбца)

3. OFFSET (ссылочная_ячейка, строки, столбцы):

1. НЕПРЯМАЯ функция

Функция ДВССЫЛ возвращает значение указанной ссылки на ячейку, введенное в виде текста. Чтобы сослаться на ячейку B3, используйте эту формулу:

= ДВССЫЛ («B3»)

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

2. Функция ИНДЕКС

Функция ИНДЕКС используется для ссылки на ячейки в указанном диапазоне на основе номеров строк и столбцов.

Чтобы сослаться на ячейку B3 в таблице из шести строк, используйте следующую формулу:

=ИНДЕКС(B1:B6, 3)

3. Функция СМЕЩЕНИЯ

Функция СМЕЩ возвращает ячейку или диапазон, который находится на указанном количестве строк и столбцов от опорной ячейки.

Если вы хотите отобразить значение ячейки, которое составляет две ячейки по горизонтали и одну ячейку вниз от A1, используйте эту формулу:

=СМЕЩЕНИЕ(A1, 2, 1)

Вот формулы в действии:

Промежуточные текстовые формулы

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

1. TEXTJOIN(разделитель, ignore_empty, text1, [text2, …])

2. ЗАМЕНИТЬ(старый_текст, начальный_номер, число_символов, новый_текст)

3. ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

1. Функция ТЕКСТОВОЕ СОЕДИНЕНИЕ

Функция TEXTJOIN объединяет ячейки с указанным разделителем. Вы также можете указать, где следует игнорировать пустые ячейки.

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

=TEXTJOIN(“, “, ИСТИНА, A2:A4)

2. ЗАМЕНИТЬ Функция

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

Чтобы заменить первые два символа строки на «XX», используйте эту формулу:

=ТЕКСТСОЕДИНЕНИЕ(A2, 1, 2, «ХХ»)

3. ЗАМЕНИТЬ Функция

Функция замены заменяет указанные вхождения текстовой строки в другой текстовой строке новой текстовой строкой.

Чтобы заменить вхождение слова «Джинсы» на «Леггинсы», используйте следующую формулу:

=ПОДСТАВИТЬ(A3, «Джинсы», «Леггинсы»)

Вот формулы в действии:

Промежуточные формулы поиска

Общие функции поиска, такие как ВПР и ПОИСКПОЗ, описаны в памятке для начинающих.

Пользователи среднего уровня должны ознакомиться с функцией поиска CHOOSE:

• ВЫБЕРИТЕ(номер_индекса, значение1, [значение2, …])

Функция ВЫБОР возвращает значение из списка значений на основе указанного номера индекса.

Предположим, у вас есть столбец с размерами футболок, помеченный как 1, 2 или 3. Вы хотите отобразить категории малых, средних и больших размеров соответственно в одной строке. Используйте эту формулу:

=ВЫБОР(A1, «Маленький», «Средний», «Большой»)

Формулы для обработки ошибок Microsoft Excel

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

1. ЕСЛИОШИБКА(значение, если_ошибка)

2. IFNA(значение, значение_если_на)

1. Функция ЕСЛИОШИБКА

Ошибки деления на ноль распространены, но Excel по умолчанию отображает #DIV/0! не будет понятно каждому пользователю.

Используйте функцию ЕСЛИОШИБКА, чтобы заменить стандартную ошибку на «Невозможно разделить на ноль» этой формулой:

=ЕСЛИОШИБКА(A1/B1, «Невозможно разделить на ноль»)

На этом рисунке показана необработанная ошибка в третьей строке и функция ЕСЛИОШИБКА, обрабатывающая четвертую строку.

2. Функция ИФНА

Функция IFNA в Excel используется для обнаружения и обработки ошибок #N/A.

В другом разделе этой шпаргалки показана функция ВЫБОР. Эта функция вернет ошибку N/A, если номер индекса не является целым числом.

Предположим, что размер футболки был введен как 1,5, и пользователь ошибочно подумал, что это покажет категорию где-то между маленьким и средним. Вместо этого будет отображаться ошибка #N/A .

Чтобы получить более полезную ошибку, используйте эту формулу:

=IFNA(ВЫБОР(A2, «Низкий», «Средний», «Высокий»), «Недопустимый индекс»)

Сочетания формул и функций

Существует несколько сочетаний клавиш, которые могут повысить эффективность работы с рабочим листом.

• F2 : отредактируйте активную ячейку и поместите точку вставки в конец содержимого ячейки.

• F9 : вычислить и отобразить результат выбранной части формулы.

• Ctrl + Shift + Enter : введите формулу массива.

• Shift + F3 : открыть диалоговое окно «Вставить функцию».

• Esc : отменить ввод формулы и вернуться к исходному содержимому ячейки.

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

Промежуточное форматирование ячеек

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

1. Выберите ячейки, которые вы хотите отформатировать.

2. Щелкните правой кнопкой мыши выбранные ячейки и выберите «Формат ячеек».

3. Выберите соответствующую категорию (например, Число, Валюта, Дата) и примените нужный формат.

Некоторые общие типы форматирования, которые следует учитывать в финансах, включают:

• Валюта : 1 234,56 доллара США.

• Процент : 12,34%

• Бухгалтерский учет : (1234,00 долл. США)

• Дата : 10 мая 2023 г.

Последние мысли

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

Практика является ключом к овладению любым навыком, поэтому не бойтесь экспериментировать с этими формулами в своей повседневной работе. Они перенесут ваш опыт с начального уровня на средний.

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

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

Чувствуете, что хотите выйти на новый уровень? Ознакомьтесь с нашей шпаргалкой по расширенным формулам Excel.