Курс доллара в excel

Как построить диаграмму Ганта в Excel, автоматически проверять заявки на платеж, залатать дыры в таблицах, выгруженных из «1С»? Такие вопросы задали финансовые директора в ходе опроса, проведенного редакцией журнала. Некоторые из этих задач решаются с помощью встроенных инструментов программы, другие — за счет несложных комбинаций типовых функций. По итогам опроса, проведенного редакцией журнала, был составлен перечень самых распространенных проблем, с которыми сталкиваются финансовые директора при работе в Excel, и для них подобраны наиболее оптимальные решения. Освоение предлагаемых приемов и техник потребует не более пяти минут, а экономия времени при составлении отчетов в Excel в дальнейшем будет существенной. Выгрузка данных из учетной системы Нередко источником данных для разно­образных управленческих отчетов служит информация, выгруженная из учетной сис­темы. Распространенный недостаток таких импортированных таблиц — пустые ячейки там, где должен быть указан какой-либо аналитический признак. Если в нескольких строках значение аналитики одинаково, зачастую оно указывается только в верхней строке. Подобное мешает построению сводных таблиц на основе выгруженных данных, не позволяет использовать фильтры и формулы по аналитике — одним словом, возникает целый ворох проблем. Безусловно, при небольшом количестве таких незаполненных ячеек аналитические признаки можно копировать вручную. Если же число строк в таблице превышает несколько сотен, такой способ не годится. Да и когда данных немного, монотонные ручные операции — источник большинства ошибок. Поэтому лучше отказаться от ручного ввода и действовать по следующей схеме: выделяем столбец с незаполненными ячейками диапазон A1:A12 ; оставляем в выделении только ячейки без значений. Для этого нужно нажать клавишу F5, в открывшемся диалоговом окне нажать кнопку «Выделить», далее выбрать условие «Пустые ячейки» и нажать «ОК»; в первую ячейку из этого диапазона А2 вводим знак «равно» и нажимаем на клавиатуре кнопку «Вверх». В итоге получится формула, ссылающаяся на предыдущую ячейку. Но после ее появления надо нажать не Enter, а сочетание клавиш CTRL + Enter. Так формула скопируется во все выделенные ячейки; избавляемся от формул и оставляем только значения аналитики в столбце. Снова выделяем диапазон A1:A12копируем его и вставляем на то же самое место, но уже одни значения щелкнуть правой кнопкой мыши по выделению — «Специальная вставка» — выбрать опцию «Вставить значения». Защита от ошибок ручного ввода Те финансовые директора, кому постоянно приходится готовить планы или отчеты в разрезе контрагентов, не понаслышке знают, сколько хлопот доставляет отсутствие единообразия данных, введенных в разных таблицах. В ре­естре покупателей компания отражена как ООО «Ромашка», в отчете по дебиторам — «Ромашка», в платежном календаре — Romashka Ltd. Казалось бы, мелочь, и так понятно, о какой организации идет речь, но при попытке сделать сортировку по компаниям, построить сводную таблицу ничего хорошего не получится. У Excel на этот случай есть простой и удобный инструмент — проверка вводимых данных с раскрывающимся списком. Итак, предположим, что нам крайне важно проверять правильность ввода на­именований товаров в отчете о продажах. Начинаем с создания на любом листе списка правильных наименований товаров — образца, который Excel будет использовать для проверки вводимых данных. Дальше созданному диапазону надо дать имя для использования в дальнейшем в качестве источника данных для выпадающего списка. Для этого выделяем наш список все ячейки, содержащие корректные наименования товаровнажимаем сочетание клавиш CTRL + F3 и кнопку «Создать» и вводим имя диапазона без пробелов, к примеру, «Товары». Теперь возвращаемся в отчет о продажах и выделяем ту область ячеек, куда будут вноситься названия товаров. Открываем вкладку «Данные» — «Проверка данных». В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» задаем тип данных. В нашем случае — «Список». После этого останется нажать кнопку «ОК». Перейдя на одну из ячеек в отчете, куда будут заноситься наименования товаров, вы увидите, что рядом с ней появится стрелка. Теперь можно вводить номенклатуру вручную Excel выдаст предупреждение, если была допущена ошибка или выбирать из раскрывающегося списка. Кстати, при помощи «Проверки» можно ограничивать размеры вводимых чисел «Целое» и «Действительное» и длину строки «Длина текста»устанавливать диапазоны дат «Дата» или создать свое собственное ограничение, введя необходимую формулу «Другой». Наглядный отчет о движении денежных средств Хорошей иллюстрацией к отчету о ДДС может стать каскадная диаграмма отклонений см. Она характеризует динамику чистого денежного потока. Входящий остаток на январь у компании был в размере 100 млн рублей. За месяц чистый денежный поток оказался отрицательным — 10 млн рублей, а в феврале — еще —30 млн рублей. Построение каскадной диаграммы отклонений Разобрались? Такие диаграммы очень удобны, но не входят в число стандартных. Вот как можно восполнить этот пробел: сначала готовим исходные данные — дополнительную таблицу, содержащую четыре столбца см. Для этого нужно выбрать в контекстном меню команду «Формат ряда данных» на рис. В открывшемся окне в категории «Заливка» указать «Нет заливки», в категории «Цвет линий» — «Нет линий». Остается нажать «ОК» — диаграмма готова. Выбор невидимой границы и прозрачной заливки Актуальный курс валют в Excel Очень часто в расчетах, выполненных с помощью Excel, нужно использовать актуальные курсы различных валют. Приведем пример из практики. Компания специализируется на торговле импортными товарами. Прайс-лист включает цену в долларах и рублях. Отпускная цена в рублях рассчитывается в Excel ежедневно по курсу Центробанка, увеличенному на 2 процента. Сам расчет не представляет труда при условии, что в таблицу ежедневно специалистом «руками» вносится актуальный курс доллара. Увы, но в компании, о которой идет речь, однажды курс не был изменен на актуальный вовремя. В результате клиенту выставили счет с неправильной ценой и этот счет был оплачен. Этого не произошло бы, если бы обновление курса доверили Excel. Обратите внимание, что в Excel и на сайте должны быть одинаковыми знаки-разделители целой и дробной части числа точка или запятая ; как только страница загрузится, на ней появятся черно-желтые стрелки. Ими помечены области данных, пригодные для импорта в Excel см. Остается кликнуть по стрелке рядом с интересующими данными и нажать кнопку «Импорт» в правом нижнем углу окна. Программа запросит, куда поместить новые данные, и загрузит их в нужные ячейки через несколько секунд; для настройки частоты обновления валютного курса нужно кликнуть правой кнопкой мыши по любой ячейке из вставленного диапазона. Далее в контекстном меню выбрать команду «Свойства диапазона данных» и указать наиболее приемлемый вариант — обновлять каждый раз при открытии файла или с периодичностью в несколько минут количество минут указывается. Настройка автоматического обновления курса валют Диаграмма Ганта в Excel Самый популярный инструмент планирования и контроля сроков работ по тем или иным проектам — диаграмма Ганта. Но использовать специализированные решения, например MS Project, не всегда удобно. Лучше построить диаграмму Ганта в Excel. Самый простой способ — рисовать ее с помощью условного форматирования. Сначала потребуется создать таблицу-заготовку с названиями работ, сроками их исполнения даты начала, окончания, длительностьвеличиной задержек по этапам в днях и процентах см. Диаграмма Ганта На что стоит обратить внимание при подготовке этой таблицы: даты по горизонтали на рис. Для краткосрочных проектов, например, это один-два дня, для долгосрочных — неделя, месяц или. Для универсальности можно указать шаг временной шкалы в отдельной ячейке, а потом ссылаться на нее при разметке дат; начало первого этапа на рис. Она может быть как положительной пауза между этапамитак и отрицательной следующий этап выполняется одновременно с предыдущим. Технически это реализовано с помощью таких формул, как указано на рисунке 5. Исходная таблица для диаграммы Ганта Теперь нужно настроить правила условного форматирования. Эта функция поможет разметить ячейки цветом, тем самым изобразить этапы проекта их выполнение. Первое, что нужно сделать, — выделить диапазон таблицы, непосредственно предназначенный для отображения диаграммы Ганта в примере его верхний левый угол — ячейка G2. Затем выбрать на вкладке «Главная» команду «Условное форматирование», кликнуть по пункту «Создать правило», затем «Использовать формулу для определения форматируемых ячеек». Понадобится сформулировать всего два правила — заливать, например, синим цветом интервал, где работы уже завершены, голубым — еще выполняются. Если оба эти условия выполняются, то есть дата текущей ячейки находится внутри запланированного интервала, ячейка заполняется голубым цветом иначе говоря, ведутся работы. Стоит обратить внимание на последовательность ввода условий, поскольку Excel проверяет их и применяет выбранное форматирование именно в том порядке, в котором они внесены. Группировка данных в Excel Предположим, у вас на руках сводная ведомость заказов, полученных от покупателей за предыдущий год. В таблице — более 5,5 тысячи записей строкосновные колонки: «Номер заказа», «Дата», «Наименование товара», «Артикул», «Количество», «Сумма заказа в рублях без учета НДС». Задача — срочно сгруппировать все заказы по месяцам и кварталам, подсчитать по ним промежуточные итоги. Самое простое и в то же время самое неэффективное решение — отсортировать все операции по дате, затем добавить после последнего числа каждого месяца пустую строку, в которой рассчитать итог автосуммой. На выполнение всех этих действий уйдет уйма времени. А можно справиться за две минуты, если воспользоваться «Сводными таблицами». Порядок действий будет таким: выделяем все ячейки исходной таблицы. После этого на вкладке «Вставка» нажимаем кнопку «Сводная таблица». В появившемся диалоговом окне нажимаем кнопку «ОК». Сразу же на новом листе появится макет сводной таблицы, а также список ее полей колонок ведомости заказов в нашем случае ; мышкой перетаскиваем поле «Дата» из списка полей в область «Названия строк», поле «Сумма заказа в рублях без учета НДС» — в область «Значения»; задаем «правильные» имена двум колонкам сводной таблицы — «Дата» и «Сумма заказа в рублях без учета НДС» соответственно. Макет уже приобрел вид нормальной, привычной таблицы, осталось немного; ставим курсор на первое значение колонки «Дата», переходим на вкладку меню «Данные» и кликаем кнопку «Группировать». В появившемся диалоговом окне выбираем условие группировки «Месяцы» и «Кварталы», нажимаем кнопку «ОК». Программа автоматически создаст структуру данных в разрезе кварталов и месяцев, а также посчитает итоги см. Группировка данных по месяцам и кварталам Проверка заявки на платеж на соответствие бюджету Стандартная функция «СУММЕСЛИ» может творить чудеса. К примеру, с ее помощью, прежде чем согласовать очередную заявку на платеж, можно легко и быстро проверить, приведет ли это к перерасходу по той или иной статье бюджета движения денежных средств. Предположим, что есть бюджет движения денежных средств, составленный на месяц. В нем отдельным блоком выделены статьи, платежи по которым осуществляются на основе поступающих заявок от подразделений оплата товаров поставщикам, аренда, транспортные расходы. А также предусмотрены графы, названия которых говорят сами за себя: «План на месяц», «Факт на текущую дату», «Сумма заявленных, но не исполненных на текущую дату платежей», «Ожидаемые отклонения от бюджета при выполнении всех заявок». Кроме того, есть реестр платежей, формируемый из заявок на оплату, поступающих от руководителей функциональных подразделений. У ре­естра аналитика такая: дата подачи заявки, реквизиты счета, подразделение, инициировавшее платеж, а также сумма. Самое главное — для каждой заявки на платеж указывается бюджетная статья, в рамках которой будет осуществлена выплата и статус заявки — «Оплачено» или нет в последнем случае графа не заполняется. А теперь о том, как организовать автоматическую проверку платежных заявок на базе этих таблиц: добавляем в реестр платежей еще одну колонку. Она предназначена исключительно для служебных целей — для решения технических задач. Пробел в кавычках " " введен в формулу для того, чтобы текст не слипался. Если в ячейке F14 указано «Оплата товаров», Н14 — «Оплачено», то выше описанная формула выдаст вот такой результат: «Оплата товаров оплачено»; проверяем, совпадают ли наименования бюджетных статей в реестре заявок и в бюджете движения денежных средств. Они должны быть полностью идентичны; в бюджете денежных средств по каждой статье расходов рассчитываем фактический расход денежных средств на текущую дату. I14:I57;"Оплата товаров оплачено";'Реестр заявок на платеж'! G14:G39где 'Реестр заявок на платеж'! I14:I57 — ссылка на все ячейки технического столбца в реестре заявок. Перебирая данные из этого столбца, функция будет либо складывать суммы заявок, либо игнорировать. «Оплата товаров оплачено» — это и есть критерий, которым будет руководствоваться программа, решая, учитывать тот или иной платеж или. Совпадает значение в ячейках технического столбца с этой фразой — складывать, нет — пропускать. G14:G39 — ссылка на все ячейки, содержащие суммы по заявкам на платеж. Аналогично вводятся формулы для всех статей, оплата по которым осуществляется на основании заявок от руководителей подразделений см. Иначе говоря, нужно заполнить графу «Сумма заявленных, но не исполненных на текущую дату платежей». Логика будет той же, что и в предыдущем случае. Разница в том, что вместо оплаченных заявок надо будет заставить Excel отобрать те, по которым деньги еще не ушли. I14:I57;"Оплата товаров";'Реестр заявок на платеж'! Обратите внимание, что в критерии отбора заявок «Оплата товаров» после названия статьи бюджета должен стоять пробел. Помните, создавая технический столбец в реестре заявок, мы добавили знак пробела? Его и надо учесть. Иначе ничего работать не. В бюджете есть данные о запланированных выплатах на месяц, фактически осуществленных оплаченные заявки и предстоящих заявленные, но не исполненные платежи. Этих сведений более чем достаточно, чтобы заранее предвидеть перерасход бюджета. Кстати, по мере добавления заявок в реестр или изменения их статусаблагодаря функции «СУММЕСЛИ» данные будут мгновенно меняться. Автоматически форматируемые таблицы и отчеты Когда необходимо добавить строки и столбцы в таблицу ввести дополнительные статьи доходов и расходов, новые наименования продукции и т. Элегантным решением может стать создание динамического именованного диапазона, который будет автоматически подстраи­ваться под вновь добавляемые строки и столбцы. Предположим, что есть план продаж — таблица в Excel. Он используется как источник данных для других бюджетов см. Нужна динамическая ссылка на его содержимое, не зависящая от размера плана продаж. Выделяем то количество строк и столбцов, которое есть в настоящий момент, затем на вкладке «Главная» нажимаем кнопку «Форматировать как таблицу» и выбираем любой подходящий вариант форматирования см. Далее на вкладке «Конструктор» задаем ей имя без пробеловнаример, «План продаж». Оно и станет ссылкой на план продаж, а также может использоваться как аргумент для различных функций например, ВПРкак указание источника данных для сводных таблиц. Сколько бы вы ни добавляли в таблицу строк или столбцов в дальнейшем, формулы исправлять не придется — все будет работать корректно. Динамический диапазон Кстати, у таблиц, отформатированных таким образом, есть еще ряд преимуществ: красивые стили дизайна; автоматическое включение для них автофильтра и строки итогов; автоматическое копирование формул на весь столбец; закрепленная шапка таблицы остается на месте при прокрутке. Горячие клавиши, незаменимые при работе в Excel Создание «умной» таблицы с фильтрами, автоподстройкой размеров, дизайном, закреплением шапки и т. Подписка на статьи Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Подписаться Да, у нас все готово 29.