Автоматическое обновление курса валют в 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
Чтобы создать сводную таблицу в качестве исходного диапазона возьмем каталог учебной литературы:
Подразумевается, что данная таблица состоит из сотен строк. Создадим сводный отчет для выведения списка книг из конкретной категории или определенного года. Задача сформулирована – перейдем к реализации.
- Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» — «Таблица». Нажимаем кнопку «Сводная таблица».
- Автоматически выделяется весь диапазон. Открывается диалоговое окно инструмента. Необходимо проверить правильность параметров для отчета (диапазон, куда выводить сводную таблицу).
- Открывается окно для построения отчета, список полей. В правой нижней части страницы – области для размещения данных из исходного диапазона.
- Области позволяют сформировать структуру сводного отчета.
- Сначала заполним «Названия строк». Так как нужно вывести список книг по годам, то в этом разделе должен быть перечень названий книг. В списке полей ставим птичку напротив поля «Название». Данные столбца имеют текстовый формат – автоматически попадают в область «Названия строк». К значениям сразу применяется сортировка по алфавиту.
- Теперь ставим птичку напротив поля «Год выпуска».
Сводная таблица сделана. С помощью нескольких кликов. Такой способ представления информации удобен для финансовых отчетов.
Как обновить сводную таблицу в Excel
От сводной таблицы больше пользы, если она динамическая. То есть при внесении новых данных в исходный диапазон поля отчета можно обновить. Как это сделать?
- Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
- Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
- Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.
Как настроить автоматическое обновление сводной таблицы в Excel:
- Открыть лист со сводным отчетом. Щелкнуть в любом месте таблицы левой кнопкой мыши. Это нужно для того, чтобы активизировалась «Работа со сводными таблицами».
- На вкладке «Параметры» находим группу «Сводная таблица». Нажимаем – открывается меню кнопки. Выбираем пункт «Параметры».
- В открывшемся меню «Параметры сводной таблицы» нажать кнопку «Разметка и формат». Поставить галочки напротив следующих пунктов:
Закрыть окно, нажав кнопку ОК.
Еще один вариант:
- Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
- Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».
Теперь сводный отчет при открытии будет обновляться программно.