5 фишек Excel о которых знают не все

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

К сожалению, многие используют программу только для вывода в красивой табличной форме ряда значений. Если это про Вас, можете смело пропустить статью. Или всё же ознакомиться с содержимым и узнать что-то новенькое.

Для начала вспомним рабочую область программы:

Используется принцип нумерования, знакомый всем со школы — по́мните «Морской бой»? Вот это примерно оно и есть.

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

Помимо этого можно записывать формулы, ссылаясь на другие ячейки. Для этого сначала ставится знак равно, после чего следует запись формулы. Альтернативный вариант — нажать кнопку f_x

В формулах поддерживаются как основные математические операции — сложение, вычитание, умножение, деление — так и вызов имеющихся функций. В примере выше значение ячейки C7 вычисляется путём деления содержимого ячейки A7 на B7.

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

Вводный курс закончен. Далее обещанные фишки.

Узнать количество дней между датами

Задача довольно тривиальна, но она помогает лучше постигнуть логику Экселя.
Постановка задания: через сколько дней после Нового года наступит 8 марта?
Решаем:

В первую ячейку пишем одну дату, во вторую — другую. А в третьей вводим формулу разности ячеек, вычитаем из A1 значение B1: =A1 — B1. Если получилось не то, что ожидалось, просто установите ячейке C1 числовой формат.
Ответ — 66 дней.

Извлечение корня n-ой степени

В Экселе есть функция КОРЕНЬ() для извлечения квадратного корня. А что делать, когда требуется извлечь, например, корень 12-ой степени?

Это может понадобиться для подсчёта ежемесячных банковских процентов, начисляемых в режиме капитализации — полученные проценты каждый месяц начисляются на основной счёт и начинают участвовать в подсчёте итоговой суммы. Например, если в условиях депозита написано 6% с учётом капитализации, по факту ежемесячное начисление будет не 0,5% как могло бы показаться, а корень 12-ой степени из 6%, что соответствует примерно 0,487% ежемесячно.
Если каждый месяц проценты снимать, то за год получится 5,841% что соответствует минус ~1590 рублей с каждого миллиона.

Вспомним простое математическое правило:
Как видим, операцию извлечения корня можно представить в виде степени.

Теперь можно воспользоваться встроенной функцией возведения в произвольную степень: =СТЕПЕНЬ(1,06; 1/12)

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

Если случалось производить какие-то вычисления в Экселе, то наверняка знакомы функции ОКРВНИЗ() и ОКРВВЕРХ(). Каждая принимает 2 параметра: число и точность. Если последний параметр установить в 1, округление производится до единиц, 10 — до десятков и т. д. Каким же образом можно округлить, допустим, до 2-го знака после запятой (сотые)?

Легко! Задаём точность в том же десятичном формате. Примеры:
для выражения =ОКРВВЕРХ(0,020000000001; 0,01) результат будет 0,03
для =ОКРВНИЗ(0,0299999999999; 0,01) результат будет 0,02

Поведение отличается от математического школьного правила, полученного с помощью функции ОКРУГЛ():
для =ОКРУГЛ(0,020000000001; 2) результат будет 0,02
для =ОКРУГЛ(0,0299999999999; 2) результат будет 0,03

Примеры выше, что называется, ни о чём. Давайте что-нибудь более полезное. Вычислим итоговую сумму, необходимую для перевода средств через систему Яндекс.Деньги, в которой дополнительно взимается комиссия 0,5%.

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

Таким образом, чтобы перевести 2,2 рубля, нужно иметь на балансе хотя бы 2 рубля 22 копейки.

Если попытаться посчитать с помощью обычного округления, получим неверный ответ:
=ОКРУГЛ( 2,2 * 1,005; 2 ) выдаст 2,21

Решением будет использовать функцию округления вверх:
=ОКРВВЕРХ( 2,2 * 1,005; 0,01 ) получим 2,22 что нам и требовалось

Применить формулу ко всей колонке

Можно навести на указанный угол активной ячейки, зажать левую кнопку мышки и протянуть курсор вниз, до ячейки C7. Excel не просто бездумно скопирует формулу из ячейки, но и подставит для каждой строки своё значение: для вычисления C3 будут использоваться значения A3 и B3, для C4 — A4 и B4 и т. д.

А что если значений сотни или даже тысячи? Тогда просто щёлкните по указанной точке дважды. Эксель автоматически продлит формулу из ячейки по колонке вниз, до окончания данных в левой колонке.

Использование фиксированных ячеек

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

Например, что делать, если для вычисления нужно использовать курс доллара? Каждый раз менять содержимое всех формул сложно назвать удобным. Но способ есть. Это фиксированные ячейки.

Можно «зафиксировать» ячейку, колонку и/или ряд. Для этого перед каждой координатой ячейки (колонка|строка) нужно поставить знак доллара. Пример:

В данном примере можно «растянуть ячейку» (см. предыдущий пункт) на всю колонку, и в каждой значение $B$1 останется. А B5 изменится: на B6, B7…

Однако абсолютная фиксация не всегда нужна. Давайте сделаем таблицу умножения. Есть группа чисел от 1 до 10 по горизонтали и вертикали. Заполняем первую ячейку нашей таблицы (B3) так: =$A3 * B$2

Теперь, кликаем на угол ячейки и тянем мышкой вправо, отпускаем, снова кликаем на угол и тянем вниз. Получится что-то вроде этого:

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

---

На первый раз, думаю, хватит.

Excel — очень полезный инструмент. Может, с точки зрения программирования запись формул выглядит громоздко, а синтаксис кажется неудобным, зато это компенсируется «визуальным масштабированием» — можно легко добавить данных, «дотянуть» соседние ячейки и сразу получить результат. Если приходится много считать, а программой ещё не пользуетесь, возможно, сейчас самое время начать.