Автоматическое обновление курса валют в Excel

Добрый день, уважаемые читатели! Сегодня поговорим об автоматическом обновлении курса валют. В предыдущем УРОКЕ мы рассматривали загрузку курсов валют, но чтобы они обновлялись необходимо либо нажимать кнопку «Обновить» на вкладке «Данные» либо щёлкать правой кнопкой по таблице и выбирать «XML» — > «Обновить XML данные».

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

Пойдём стандартным путем, сначала загрузим курсы валют через «Данные» — > «Из интернета» (сразу оговорюсь, данный способ опробован на сайте worldcoinicons.com, где можно отметить таблицу с курсами, но макрос, который будет ниже, подойдёт и для загрузки через XML с сайта Центрального банка России). Отмечаем галкой таблицу с курсами и жмём «Импорт», затем «ОК» (когда спросит в какую ячейку помещать курсы валют).

Теперь получим вот такую картину.

А вот теперь нам понадобится вкладка «Разработчик», где жмём кнопку «Visual Basic».

Теперь нам нужно для активного листа создать модуль и имя процедуры, которую будем вызывать при открытии книги. Жмём «Insert» -> «Module».

В тексте модуля пишем следующий текст:

Sub BTC() ActiveWorkbook.RefreshAll End Sub

Тем самым мы создали команду обновления всей книги (имя команды в моём случае BTC, вы можете написать что угодно!).

Далее переходим в пункту в левом меню «Эта книга» ( двойным щелчком), в поле «Object» выбираем «Workbook», в поле «Procedure» у нас само появится «Open», т.е. при открытии, далее пишем вызов команды BTC. которая и будет обновлять курс валюты.

Private Sub Workbook_Open() Call BTC End Sub

Тем самым мы вызываем созданную выше команду.

БУДЬТЕ ОЧЕНЬ ВНИМАТЕЛЬНЫ! Когда будете сохранять книгу выбираем тип книги .xlsm в окне сохранения иначе макросы не сохранятся!

Осталось проверить, как работает наш макрос. Для этого открываем книгу и обращаем внимание вверх, там будет кнопка «Включить содержимое», щёлкаем её, а затем смотрим в нижний левый угол программы. Там будет следующая картина.

Идёт фоновый запрос. Значит макрос работает и мы увидим обновлённый курс валют. Также хочу отметить, что иногда обновление занимает много времени (особенно с загрузкой XML данных), наберитесь терпения и всё будет хорошо!

Стоит обратить внимание на один существенный момент. Если вы вводите в ячейку значение "01", а в ней появляется "1", значит, формат ячейки является числовым, а не текстовым. Его нужно поменять. Иначе формула работать не будет.

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

После смены формата нужно еще раз ввести значение в ячейку.

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

На заметку: если после смены формата формулы не работают, значит, в ваших параметрах обновление стоит в ручном режиме.

В этом случае необходимо обновить ячейку с формулой вручную (щелкнуть по ней два раза и нажать "enter") либо нажать кнопку F9, которая одновременно обновляет весь лист Excel.

Если вы хотите настроить автоматическое обновление страницы (а это просто необходимо сделать в нашем случае, иначе каждый раз придется вручную обновлять все ячейки), зайдите в "Сервис" => "Параметры" => "Вычисления" и выберите вариант "автоматически":

Итак, мы ввели в ячейку одно условие. Но как быть со всем остальным списком обозначения дней?

Все довольно-таки просто! В строке состояния копируем все выражение кроме знака "=",

ставим ";" перед последней скобкой выражения и вставляем после него скопированное выражение:

Исправляем во втором выражении "01" на "03" (работа в выходной день):

Готово! Смысл этого выражения будет звучать так:
Если значение ячейки D8 соответствует “01”, то значение ячейки AV8 нужно считать за 1, если нет, то действует новое условие: [если значение ячейки D8 соответствует «03», то значение ячейки AV8 нужно считать за «1»].
И так со всеми последующими условиями.

Теперь добавим любой код дня неявки. Скопируем второе выражение, сразу захватив ";":

и вставим опять же перед всеми скобками:

Исправляем "03" на "16" (отпуск за свой счет), и "1" на "0", чтобы этот день не учитывался как отработанный:

Вот и всё! Все остальные коды проставляем аналогичным образом.

Учтем, что логический оператор "ЕСЛИ" позволяет использовать не более 7-ми аргументов, поэтому если количество используемых вами кодов больше 7-ми, можно проставить коды только для явочных дней. Все остальные коды Excel в любом случае посчитает как "0".

Копируем полученную формулу и вставляем во все ячейки, которые соответствуют ячейкам Табеля учета рабочего времени:

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

То же самое делаем со второй половиной месяца.

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

Однако на практике можно столкнуться с тем, что при работе с табелем (копирование, вставка, вырезание ячеек) происходит сбой формата ячеек. Поэтому отметим одну особенность подсчета суммы в Excel.

Для того, чтобы сосчитать значения в любом формате нужно пользоваться ручным суммированием. Автосумма считает только значения в числовом формате.

Используем в нашем табеле эту особенность:

Теперь складываем количество дней за первый и второй период, потом — количество часов.

Для этого активизируем ячейку, в которой хотим создать формулу, и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем (выделяем левой клавишей мыши) соответствующие ячейки в следующем порядке:

= [Количество дней(часов) за 1 половину месяца] + [Количество дней(часов) за 2 половину месяца] ENTER

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

С помощью абсолютной ссылки посчитаем дни неявки:

и часы неявки по формуле:

= [Количество дней в месяце] * [Количество часов в день] — [Отработанное количество часов] ENTER

Все полученные формулы копируем и вставляем в соответствующие ячейки табеля до конца списка сотрудников.

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

Табель с числовыми кодами готов (СКАЧАТЬ)! Пользуйтесь на здоровье.

Для создания кода с буквенным кодами (СКАЧАТЬ) нужно заменить соответствующие цифровые коды на буквенные.

Если вы найдете способ автоматизировать оставшиеся поля, напишите мне по адресу: offisny@mail.ru

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

Дата добавления: 2015-06-10 ; просмотров: 2582 ; ЗАКАЗАТЬ НАПИСАНИЕ РАБОТЫ

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

О целесообразности и возможности сводных таблиц

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

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

Требования к исходной таблице:

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

Нерациональная организация информации:

Нельзя будет вывести итоги, например, только по городу.

Лучше значения ввести следующим таким образом.

Как сделать сводную таблицу в Excel

Чтобы создать сводную таблицу в качестве исходного диапазона возьмем каталог учебной литературы:

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

  1. Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» — «Таблица». Нажимаем кнопку «Сводная таблица».
  2. Автоматически выделяется весь диапазон. Открывается диалоговое окно инструмента. Необходимо проверить правильность параметров для отчета (диапазон, куда выводить сводную таблицу).
  3. Открывается окно для построения отчета, список полей. В правой нижней части страницы – области для размещения данных из исходного диапазона.
  4. Области позволяют сформировать структуру сводного отчета.
  5. Сначала заполним «Названия строк». Так как нужно вывести список книг по годам, то в этом разделе должен быть перечень названий книг. В списке полей ставим птичку напротив поля «Название». Данные столбца имеют текстовый формат – автоматически попадают в область «Названия строк». К значениям сразу применяется сортировка по алфавиту.
  6. Теперь ставим птичку напротив поля «Год выпуска».

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

Как обновить сводную таблицу в Excel

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

  1. Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
  2. Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
  3. Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

Как настроить автоматическое обновление сводной таблицы в Excel:

  1. Открыть лист со сводным отчетом. Щелкнуть в любом месте таблицы левой кнопкой мыши. Это нужно для того, чтобы активизировалась «Работа со сводными таблицами».
  2. На вкладке «Параметры» находим группу «Сводная таблица». Нажимаем – открывается меню кнопки. Выбираем пункт «Параметры».
  3. В открывшемся меню «Параметры сводной таблицы» нажать кнопку «Разметка и формат». Поставить галочки напротив следующих пунктов:

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

  1. Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
  2. Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».

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