Воскресенье , 18 Февраль 2018
Как новичку заработать в интернете?
ДомойПубликации5 фишек Excel о которых знают не все

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

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

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

5 фишек Экселя

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

Рабочая область Excel

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

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

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

Кнопка «Вставить функцию»

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

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

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

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

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

Пример №1

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

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

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

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

Вспомним простое математическое правило: корень 12-ой степени из 1,06 равен этому числу в степени 1/12
Как видим, операцию извлечения корня можно представить в виде степени.

Теперь можно воспользоваться встроенной функцией возведения в произвольную степень: =СТЕПЕНЬ(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, для C4A4 и B4 и т. д.

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

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

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

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

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

Обращение к фиксированной ячейке

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

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

Таблица умножения в Excel

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

Таблица умножения в Excel: тянем ячейки

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


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

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



Рейтинг: 1

Автор публикации

1 756
не в сети 16 часов

x64 (aka andi)

Комментарии: 2606Публикации: 360Регистрация: 02-04-2009
Так себеНеплохоХорошоЗамечательноСупер! (3 голосов, в среднем: 5,00 из 5)
Загрузка...

8 комментариев

  1. Доброе время суток, Анди!

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

    А еще есть класнная функция «ЕСЛИ» … Ее одной на целую статью хватит!

    Недавно был на очень полезном сайте про банковские карточки. Hranidengi.ru
    Вот там человек так здорово донат оформил, что просто руки чешутся задонатить, глянь мысль:

    Рейтинг: 1
    • Денис, доброй ночи.
      Согласен, Эксель просто непознанность кучи фишек, скрывающихся за банальными ячейками ака таблица.
      За мысль спасибо. Правда, тут зависит от восприятия каждого. Мне на шоколадку дать жалко, сам их люблю crazy

      Рейтинг: 1
      • За мысль спасибо. Правда, тут зависит от восприятия каждого. Мне на шоколадку дать жалко, сам их люблю crazy

        Ну дык, раз любишь сам, чего тогда не просишь? Разве это неприлично?

        Я уже пять шоколадок слопал, вот так.

        Рейтинг: 1
  2. Сложен для меня Эксель smile Жена разбирается хорошо в нем, помогает. а так — чисто бюджет подсчитать в строке и столбике…

    Рейтинг: 2
    • Дима, на самом деле всё очень просто) Тут нет программирования, просто вставляй формулы да выделяй нужные ячейки. Эксель показывает запись функции, так что с этой точки зрения всё ок.

      Рейтинг: 1

Оставить комментарий

Ваш email не будет опубликован.Необходимы поля отмечены *

*

Добавьте изображение (jpg/gif/png)

Авторизация

Регистрация

Пароль не введен

Генерация пароля