usto-excel

Последние несколько месяцев я был сильно загружен (ну и признаюсь честно - иногда ленился). На то были свои причины, одна из которых смена места работы. Пришлось осваивать и переваривать много новой информации. К сожалению по этим причинам я стал редко публиковать новые посты, хотя накопилось очень много интересных тем.
Обещаю исправиться.
Ну а пока выкладываю ссылку на своё первое видео в YouTube. Надеюсь оно подарит Вам праздничное настроение.
С наступающим!

Уверен, многие из Вас знакомы с такой функцией MS Excel как СРЗНАЧ(). Эта функция, как и понятно из её названия, вычисляет среднее значение диапазона данных. Но ещё в MS Excel есть функция МЕДИАНА() и человеку далёкому от статистики трудно понять в чём состоит разница между этими функциями.

К примеру, если у нас имеется выборка данных 1, 2, 3, 4, 5 то результат обоих функций будет одинаковым.

Как видим, функции СРЗНАЧ() и МЕДИАНА() дают совершенно разные результаты. Почему же это происходит?

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

Так вот, разница между этими функциями состоит в том, что функция СРЗНАЧ() суммирует данные в указанном диапазоне (зарплату) и делит результат на количество этих данных (количество сотрудников).
А функция МЕДИАНА() сначала сортирует данные по порядку возрастания, после чего делит этот список на две равные части.
Если количество данных является нечётным, то медиана равняется числу находящемуся в середине отсортированного списка. Если же, как в нашем примере, количество данных в диапазоне является чётным, то медиана равняется среднему значению чисел находящихся ближе всего к линии разделения (в нашем примере значения строк №5 и №6).

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

1 способ - формулы

2 способ - специальная вставка

3 способ - пользовательский формат

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

Прежде всего, занесём в MS Excel вводные данные по кредиту, а именно:

• ПС - приведённая (текущая) стоимость, в нашем случае сумма кредита;
• Кпер - количество периодов, в течении которых нужно производить выплаты, т.е. если по условиям кредита клиент обязан ежемесячно погашать кредит, то количество периодов платежа равняется 12-ти;
• Ставка - процентная ставка под которую выдаётся кредит.

Рассчитываем ежемесячную сумму платежей

=ПЛТ(ставка; кпер; пс; [бс]; [тип])

Три первых аргумента (ставка, кпер, пс) у нас уже имеются.
Четвёртый аргумент "бс" означает будущую стоимость, которой мы хотим достичь после внесения последнего платежа. Данный аргумент является необязательным и принимается равным нулю, если не указывать его значение.
Пятый аргумент "тип" означает тип платежа, а именно производится ли платёж в начале периода (1) или же в конце периода (0). Данный аргумент также является необязательным и принимается равным нулю, если не указывать его значение.

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

=ПЛТ(C4/C3;C3;C2)

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



Как видим у нас тут две проблемы:

1. В связи с тем, что понятие платежа подразумевает денежную сумму, при первом вводе в ячейку функции ПЛТ() MS Excel автоматически меняет её формат на денежный. 
2. И так как платёж означает уменьшение оставшихся у клиента денежных средств результат функции ПЛТ() отображается со знаком минус.

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



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

Рассчитываем сумму ежемесячных платежей по процентам

=ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип])

где период - это порядковый номер периода платежа, который мы будем брать из столбца В, используя относительную ссылку:

=ПРПЛТ($C$3/$C$2;B6;$C$2;$C$1)

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

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

=ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип])

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



Если мы всё сделали правильно, то сумма платежей по процентам и основной сумме по каждому периоду будет равняться сумме общих платежей по данному периоду.

Рассчитываем остаточную сумму кредита

Таким образом, за 12 месяцев клиент должен будет заплатить $11 347,15 из которых $1 347,15 будут составлять проценты по кредиту.

Процесс выгрузки данных из MS Excel в Power Query на первый взгляд достаточно прост - нужно лишь выделить любую ячейку внутри нужного диапазона данных и на вкладке Данные, выбрать команду Из таблицы.

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


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

Если теперь мы вернёмся на предыдущий лист, то увидим что Excel автоматически преобразовал наш диапазон в умную таблицу и присвоил ей дефолтное имя Таблица1. Точно такое же имя было присвоено и созданному нами в  Power Query запросу.


Вы также можете заранее преобразовать диапазон с данными в умную таблицу и присвоить ей более понятное имя после чего загружать эти данные в Power Query.

Получение данных из именованного диапазона

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



Теперь, если Вы попытаетесь загрузить данные в Power Query (опять же с помощью команды Из таблицы на вкладке Данные), Excel распознает именованный диапазон и уже не будет пытаться преобразовать его в умную таблицу.

Получение данных с помощью пустого запроса

Если Ваша книга Excel уже содержит умные таблицы или именованные диапазоны, то Вы можете выбрать и загрузить их прямо из среды Power Query.
Для этого на вкладке Данные, в выпадающем списке Создать запрос выбираем Из других источников -> Пустой запрос. Откроется абсолютно пустое окно Power Query.


Далее в строке формул пишем:

=Excel.CurrentWorkbook()

Появится список всех умных таблиц и именованных диапазонов, содержащихся в текущей книге Excel. Находим нужную нам умную таблицу или именованный диапазон и жмём на зелёную надпись Table напротив их названия.

Получение данных из другой книги

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

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

До недавнего времени, как-то облегчить этот скучный и неблагодарный труд, можно было лишь при помощи:

• написания специальных формул, с использованием,библиотеки функций MS Excel;
• создания процедур на встроенном языке Visual Basic for Applications;
• использования SQL-запросов.

• загрузка данных в таблицу с указанием листа (новый либо существующий);
• только создание соединения - в случае если мы планируем использовать этот запрос для объединения с другими запросами;
• добавление в модель данных - если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.

Заключение

Сортировать данные в MS Excel можно не только по значению или настраиваемому списку но и по цвету.

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

Выделяем диапазон с данными для сортировки. На вкладке Главная выбираем Сортировка и фильтр -> Настраиваемая сортировка. В появившемся окне Сортировка, в поле Столбец выбираем Сумма продаж, в поле Сортировка - Цвет ячейки, в поле Порядок выбираем зелёный цвет и из выпадающего списка выбираем значение Сверху.

Далее жмём кнопку Добавить уровень и точно также заполняем поля, только в поле Сортировка выбираем Цвет шрифта, а в поле Порядок - красный цвет и значение Снизу.



Жмём ОК и получаем отсортированный по цвету список. Профит.