Приемы :: Планета Excel

Сортировка:
дата создания
дата изменения
просмотры
комментарии

Диаграмма выполнения плана

Ещё один наглядный вариант визуализации выполнения плана с помощью столбчатой гистограммы с накоплением и «усов» погрешностей в Microsoft Excel.

Решение системы уравнений в Excel

Как решить систему уравнений (линейных и нелинейных) в Microsoft Excel с помощью обратной матрицы Крамера и подбором в надстройке Поиск решения (Solver).

Регистро НЕ чувствительность в Power Query (и Power BI)

Как победить регистрочувствительность в Power Query при фильтрации, удалении дубликатов и объединении таблиц с помощью функции Comparer.OrdinalIgnoreCase

Склейка диапазонов новыми функциями VSTACK и HSTACK

Как склеить (друг под другом или друг рядом с другом) несколько таблиц (в том числе с нескольких листов) с помощью новых функций Office 365 — ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).

Динамический «Топ-N + Другие» в сводной таблице

Как при помощи запроса Power Query реализовать в сводной таблице вывод динамического «Топ-N + Другие».

Быстрое прогнозирование в Microsoft Excel

Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.

Путь к текущей книге и новая функция LET

Как с помощью формул получить в ячейке листа обновляемый путь к текущей книге.

Подробнее…

Риск безопасности и блокировка макросов

Как разблокировать макросы при появлении сообщения РИСК БЕЗОПАСНОСТИ, когда файл была скачана из интернета.

Слушаем интернет-радио в Excel

Как за пару минут реализовать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.

Горизонтальная фильтрация столбцов в Excel

Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.

Переключение вычислений в сводной таблице срезами

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

Копировать сумму выделенных ячеек в Буфер обмена

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

Как правильно разворачивать вложенные таблицы в Power Query

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

Моделирование лотереи в Excel

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

Столбец из примеров — искуственный интеллект в Power Query

Подробный разбор инструмента Столбец из примеров (Column from Examples), автоматически подбирающего формулу для преобразования ваших данных по заданному образцу.

Диаграмма по выделенной ячейке

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

Сборка разноформатных таблиц с одного листа в Power Query

Как собрать несколько разноформатных простых (не умных!) таблиц с одного листа Excel, а затем развернуть их в плоские (нормализовать) и соединить в единое целое с помощью Power Query.

Подробнее…

Нарастающий итог в Excel

Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.

LAMBDA — новая суперфункция Excel

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

Сохранение истории обновлений запросов Power Query

Как при обновлении запросов Power Query сохранять старые значения для истории, а не заменять их на новые.

Деление слипшегося текста функцией ФИЛЬТР.XML

Быстрый способ разделить слипшиеся данные на несколько ячеек с помощью функции ФИЛЬТР.XML, превратив исходный текст в XML-код.

Обновляемый курс валют в Excel

Как использовать функции ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) для загрузки мгновенно обновляемого курса любой валюты на любой интервал дат.

Система учета заказов на Google Calendar и Excel

Как вести учет заказов в Google-календаре и загружать потом оттуда данные в Excel с обновлением через Power Query.

Динамические гиперссылки между таблицами

Как сделать удобные динамические гиперссылки, чтобы быстро переходить из одной таблицы в другую — к заданному значению в строке и столбце. Что-то типа ВПР, но возвращающая ссылку, а не значение.

Поиск ключевых слов в тексте

Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.

Сборка таблиц с разными шапками из нескольких книг

Как при помощи Power Query собрать данные из всех файлов заданной папки, если заголовки столбцов в них отличаются.

Разделение таблицы по листам

Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа — с обновлением (через Power Query) и без (только VBA).

Одновременная фильтрация нескольких сводных таблиц

Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.

Картинки как подписи на диаграмме

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

Преимущества сводной по Модели Данных

Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?

Поиск последнего вхождения (инвертированный ВПР)

Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?

Парсинг нетабличных данных с сайтов

Импорт нетабличных данных, которые обычно «не видит» Power Query, на примере парсинга цен и товаров с сайта Wildberries.

Подробнее…

Распределяем список по наборам

Как разделить список по наборам неодинакового размера — разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.

Сводная по таблице с многострочной шапкой

Как построить сводную таблицу по исходным данным с многострочной шапкой, преобразовав их в нормальный вид с помощью надстройки Power Query.

Выпадающий список с показом изображений

Как сделать в Excel 2013 и новее выпадающий список, при выборе из которого рядом будет появляться изображение выбранного объекта (товара, человека и т.д.)

Как правильно использовать стили в Excel

Зачем (на самом деле!) нужны Стили в Excel и как правильно их использовать для быстрого форматирования ваших данных.

Исправление чисел, превратившихся в даты

Три способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.

Вафельная диаграмма в Excel

2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.

Массовая замена текста в Power Query функцией List.Accumulate

Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.

Массовая замена текста формулами

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

Регулярные выражения (RegExp) в Power Query

Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.

Производственный календарь в Excel

Как при помощи Power Query сделать в Excel «вечный» производственный календарь — автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.

Обманчивая простота функции ПОСЛЕД (SEQUENCE)

Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) — генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.

Импорт данных из OneDrive и SharePoint в Power Query / BI

Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.

ВПР и числа-как-текст

Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются «числа-как-текст», что приводит к ошибкам #Н/Д.

План-факт анализ в сводной таблице с Power Pivot и Power Query

Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями «многие-ко-многим», простых мер на DAX и допиливании входных данных с помощью Power Query.

Ад Условного Форматирования

Что такое «Ад Условного Форматирования», когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?

Нечёткий текстовый поиск в Power Query

Подробный разбор нового инструмента в Power Query — объединения таблиц с помощью нечёткого текстового поиска.

Самый быстрый ВПР

Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым?

Функция ПРОСМОТРX — наследник ВПР

Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP).

Экспобанк — кредитные и дебетовые карты, кредиты, вклады и другие банковские услуги для бизнеса и физических лиц

Экспобанк — кредитные и дебетовые карты, кредиты, вклады и другие банковские услуги для бизнеса и физических лиц

Сбережения

«Уютный»

Вклад

9%
процентная ставка

от 30 000 ₽
сумма вклада

С нами теплее

«Проценты вперед»

Вклад

до 7,8%
процентная ставка

от 30 000 ₽
сумма вклада

Карты

Дебетовая карта Expo Drive
Карта

5% кэшбэк
в авто категориях и 1%
кэшбэк на остальные
покупки

0 ₽
обслуживание

Кредиты

Инвестиции

ИИС

Возможность полностью вернуть налоги на доходы по ИИС

Инвестиции

Драгоценные металлы

Инвестиции в золото и серебро через брокерский счет

Страхование

ОСАГО и КАСКО

до 63% экономия
на полисе ОСАГО

до 2,5 раз экономия
на полисе Мини КАСКО

Сбережения

«Яркий процент»

до 9,5%

процентная ставка

Кредиты

«Авто Драйв»

до 10,4%

ставка

Ипотека

«Готовое жилье»

до 11,7%

годовых

Карты

Накопительная +

до 10%

процентная ставка

Рассчитайте доход по вкладу

Сумма вклада
₽¥

Cрок вклада
3 месяца4 месяца5 месяцев6 месяцев9 месяцев1 год18 месяцев2 года3 года

Онлайн оформление вклада

Пополнять




Капитализация




Снимать проценты




Снимать частично

Измените параметры вклада
У нас не нашлось вклада, идеально соответствующего вашим условиям. Попробуйте изменить параметры поиска

Доход по вкладу

На какой срок

Сумма в конце срока

Получить онлайн
Найти отделение банка

Новости
Объявления для клиентов
Блог

Экспобанк подключился к Программе льготного кредитования АПК
17 ноября



Экспобанк запустил новый вклад «Уютный»
10 ноября



Карта «Мир» от Экспобанка в топ-10 лучших виртуальных дебетовых карт
26 октября

все новости

Сервисы

Перевод с карты на карту

Денежные переводы между картами любых банков

Подробнее

Mir Pay

Приложение для бесконтактной оплаты картами «Мир»
Подробнее

Система быстрых платежей

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

Возврат налогов

Вернуть налоги — легко.

Без очередей и бумажной волокиты

Подробнее

Инвесторам
Интеллектуальные решения и эффективные продукты для управления капиталом
Стратегия для любого инвестиционного аппетита

Вопрос — ответ

Что такое вклад?

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

Подробнее

Как проверить, что деньги в банке застрахованы?

Страхованием вкладов занимается специальное Агентство по Страхованию Вкладов, АСВ. Экспобанк сотрудничает с АСВ практически с самого начала существования агентства, с 2004 года

Подробнее

Где можно открыть вклад?

В Экспобанке вклад можно открыть в интернет-банке ExpoOnlineили непосредственно в отделении.

Подробнее

Что такое неснижаемый остаток?

Неснижаемый остаток – это отдельно обозначенная минимальная сумма, которая должна храниться на счете в течение срока действия вклада.

Подробнее

Мобильные приложения

Мобильные приложения

Expo-Online Частным лицам

Expo-control Бизнесу

Expo-Online

Expo-control

Карты

Кредиты

Экспобанк

Вклады

Инвестиции

mysql — Выбор обменного курса на основе валюты и даты

Я искал решение этой проблемы, но, по крайней мере, я не смог найти ничего, что было бы хотя бы похоже на мой случай.

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

У меня есть таблица с валютами:

 CREATE TABLE `tblCurrencies` (
  `CurrID` int(11) NOT NULL AUTO_INCREMENT,
  `CurencySymbol` varchar(1) ПО УМОЛЧАНИЮ NULL,
  `CurrencyCode` varchar(3) ПО УМОЛЧАНИЮ NULL,
  `CurrencyDescription` varchar(100) ПО УМОЛЧАНИЮ NULL,
  ПЕРВИЧНЫЙ КЛЮЧ (`CurrID`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 НАБОР ШИМОВ ПО УМОЛЧАНИЮ = utf8;
 

Таблица с обменными курсами:

 CREATE TABLE `tblExchRates` (
  `ExcID` int(11) NOT NULL AUTO_INCREMENT,
  `CurrKey` int(11) ПО УМОЛЧАНИЮ NULL,
  `Дата` дата ПО УМОЛЧАНИЮ НОЛЬ,
  `Exchange` decimal(11,3) ПО УМОЛЧАНИЮ NULL,
  ПЕРВИЧНЫЙ КЛЮЧ (`ExcID`),
  КЛЮЧ `КуррКей` (`CurrKey`),
  ОГРАНИЧЕНИЕ `tblExchRates_ibfk_1` ВНЕШНИЙ КЛЮЧ (`CurrKey`) ССЫЛКИ `tblCurrencies` (`CurrID`) НА УДАЛЕНИЕ ОГРАНИЧЕНИЕ НА КАСКАД ОБНОВЛЕНИЯ
) ENGINE=InnoDB AUTO_INCREMENT=111 НАБОР ШИМОВ ПО УМОЛЧАНИЮ=utf8;
 

И таблица с товарами (обратите внимание, мои товары в таблице указаны цифрами, что в моем случае нормально):

 СОЗДАТЬ ТАБЛИЦУ `tblProducts` (
  `ProductID` int(11) NOT NULL AUTO_INCREMENT,
  `Контракт` int(11) ПО УМОЛЧАНИЮ NULL,
  `Продукт` int(11) ПО УМОЛЧАНИЮ NULL,
  `Тип` varchar(100) ПО УМОЛЧАНИЮ NULL,
  `Валюта` int(11) ПО УМОЛЧАНИЮ NULL,
  `Сумма` decimal(10,0) ПО УМОЛЧАНИЮ NULL,
  `PurchaseDate` дата и время ПО УМОЛЧАНИЮ NULL,
  ПЕРВИЧНЫЙ КЛЮЧ (`ProductID`),
  КЛЮЧ `Контракт` (`Contract`),
  КЛЮЧ `Валюта` (`Currency`),
  ОГРАНИЧЕНИЕ `tblShopCart_ibfk_2` ВНЕШНИЙ КЛЮЧ (`Валюта`) ССЫЛКИ `tblCurrencies` (`CurrID`) НА УДАЛЕНИЕ ОГРАНИЧЕНИЯ НА КАСКАД ОБНОВЛЕНИЯ,
  ОГРАНИЧЕНИЕ `tblShopCart_ibfk_1` ВНЕШНИЙ КЛЮЧ (`Contract`) ССЫЛКИ `tblContracts` (`ContractID`) НА УДАЛЕНИЕ ОГРАНИЧЕНИЕ НА КАСКАД ОБНОВЛЕНИЯ
) ENGINE=InnoDB AUTO_INCREMENT=3155 НАБОР ШИМОВ ПО УМОЛЧАНИЮ=utf8;
 

Например, в таблице «Курсы валют» значения устанавливаются следующим образом:

 CurrKey Date Exchange
1 15. 01.2017 0,850
1 31.01.2017 0,856
1 02-02-2018 0,918
1 18.02.2018 0,905
2 02.04.2018 1.765
2 14-02-2018 1.755
 

И так далее…

Я хочу получить запрос, выбирающий уникальный обменный курс на основе даты покупки товара и валюты, в которой он был куплен.

Другими словами, например, если у меня есть продукт, который был куплен 07-02-2018, запрос должен выбрать обменный курс, который действителен в соответствующем диапазоне дат, который соответствует дате покупки и его валюте. В этом примере правильный обменный курс для продукта, приобретенного 07-02-2018 с курсом 1, будет равен 0,918

. Обратите внимание, что обменные курсы устанавливаются на случайные даты (как в примере выше).

Мне удалось сделать этот запрос, но он не является точным, так как он иногда возвращает два или более результата (из-за установленного диапазона 10 дней), тогда как мне нужен только 1 результат:

 ВЫБОР
tblExchRates.Exchange
ИЗ
tblCurrencies
ВНУТРЕННЕЕ СОЕДИНЕНИЕ tblExchRates ON tblExchRates. CurrKey = tblCurrencies.CurrID
КУДА
tblCurrencies.CurrencyCode = "EUR" И
tblExchRates.Date МЕЖДУ (tblProducts.PurchaseDate - ИНТЕРВАЛ 10 ДНЕЙ) И (tblProducts.PurchaseDate + ИНТЕРВАЛ 10 ДНЕЙ)
 

Валютные курсы — основные источники международной экономической статистики

Основные источники:

Глобальные финансовые данные	Данные по облигациям, товарам, процентным ставкам, фондовым рынкам и индексам, фьючерсам, обменным курсам, ВВП, ценам и безработице. Мировые обменные курсы обратно в 1624 год.
Датастрим Интернэшнл	Предоставляет обменные курсы и другие экономические данные. Покрытие международное. Даты охвата различаются. Подробности см. в томе «Индексы, проценты и обменные курсы». В строке программы введите HELP ER? чтобы получить список кодов. Чтобы изменить валюту по умолчанию, используйте ~, за которым следует код. Для получения справки по использованию обменных курсов в Поток данных см. руководство. Доступен на рабочих станциях, расположенных рядом с Firestone A-13-J и STOKES. Часть Refinitiv Workspace.
Глобальная информация	Предоставляет ежедневные данные по обменным курсам и форвардным курсам.
Внутридневные данные по тикам	Предоставляет внутридневные данные. Принстон подписывается на следующие компоненты: фьючерсы, денежные индексы (включая VIX), лондонские акции, акции Euronext; и опционы на SPX (опционы Standard & Poor’s 500) и VIX (опционы на индекс волатильности CBOE). Включает данные о фьючерсах на валюты, фондовые индексы, зерно, металлы, процентные ставки, энергоносители, продукты питания, волокна и мясо. Также включает данные о внутридневной торговле и котировках акций, торгуемых на Лондонской фондовой бирже и Euronext, а также внутридневные данные о котировках Forex (данные о сделках Forex больше не переносятся после 31.10.2014, поскольку «данные о сделках» на самом деле были просто «данными о предложениях»). ‘ Поскольку «данные о ставках» содержатся в данных о котировках).. Доступно на рабочих станциях с ограниченным доступом (, расположенных рядом с Firestone A-13-J)  9 и 10 Сроки покрытия различаются. Обновляется ежедневно. Документация содержит множество полезных советов.
Куандл	Исторические глобальные индексы цен на ряд криптовалют (включая биткойн), рассчитанные Brave New Coin.
Кайко	Тиковые данные для многих основных криптовалют.
Международная финансовая статистика	1948- . ( HG3851.I574 (Текущий выпуск в DSS) Флагманское издание Международного валютного фонда. В одной из первых таблиц каждого выпуска приведены ежедневные обменные курсы на каждый рабочий день предыдущего месяца для основных валют плюс СПЗ и евро. ставки указаны в денежных единицах за доллар США. Существует небольшой временной лаг. На страницах «Страна» приведены более подробные данные, включающие годовые данные за последние 7 лет, квартальные данные за последние 3 года и месячные данные за последние 9месяцы. Включены страны, которые являются членами МВФ. Сводные статистические данные и «реальные эффективные обменные курсы» можно найти в International Financial Statistics Yearbook , 1979-. Доступен в электронном виде через Global Insight и Data-Planet Datasets .
Курсы обмена валют на конец недели….	Публикация Федеральной резервной системы. Каждый выпуск содержит ежедневные данные за предыдущую неделю. Охватывает около 25 валют.

Другие источники

.

Джефферсон, Северная Каролина: McFarland, 2006.	Содержит краткие денежные истории и хронологии для 203 стран и четырех конфедераций. (DR) HG216.B465 2006
Bidwell, R.L. Таблицы конвертации валют: сто лет перемен . Лондон: Коллингс, 1970.	В большинстве случаев предоставляет курсы обмена доллара США и британского фунта стерлингов для 28 стран с 1914 по 1969 год. Для некоторых информация дана до 1914 года. (DR) HG219 .B5
Дензел, Маркус А. Справочник мировых валютных курсов, 1590-1914 гг.  Фарнэм, графство Суррей, Англия; Берлингтон, В. Т.: Ашгейт, c2010.
Маккаскер, Джон Дж. Деньги и обмен в Европе и Америке, 1600-1775: Справочник. Чапел-Хилл: Университет Северной Каролины, 1978 г.	(DR) HG219.M33
Ежегодник мировых валют . 1957-1993 гг.	Предоставляет историю и статистику мировых валют. Включая цены черного рынка. (DR) HG219.P58
Справочник по средневековому обмену. Спаффорд, Питер. Лондон: Офисы Королевского исторического общества, 19.86.	(DR) HG3811.S68
MEMDB: банк данных Средневековья и раннего Нового времени	Содержит данные о валюте Средневековья.

Конвертеры валют

• Оанда
• Конвертер валют Xe

Обменные курсы черного рынка

• Глобальные финансовые данные
  • Временные ряды обменных курсов черного рынка примерно для 50 стран. В основном исторические, но некоторые из них довольно актуальны, в том числе для Северной Кореи и Венесуэлы.
  • Нажмите кнопку «Поисковые системы GFD».
  • В левой колонке в разделе «Тип серии» выберите «Обменные курсы — черный рынок» в раскрывающемся меню.

• Грамейси, Р., Мэлоун, С.В., и Хорст, Э.Т. (2014). Основы обменного курса, прогнозирование и спекуляция: байесовские модели на черном рынке. Журнал прикладной эконометрики , 29 (1), 22-41. В этом исследовании используется набор данных о ставках черного рынка, включая детерминанты, и содержится официальная ставка для построения премии черного рынка.