Книга «Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. 6-е издание»

Привет, Хаброжители! Уэйн Винстон научит вас быстро анализировать данные, принимать решения, подводить итоги, составлять отчеты, обрабатывать данные и строить аналитические модели в Microsoft Excel 2019 и Office 365. В новом шестом издании вас ждут более 800 бизнес-задач, основанных на реальных ситуациях, а также обсуждение новых инструментов и функций. Где бы вы ни работали — в крупной корпорации, небольшой компании, государственной или некоммерческой структуре, — это поможет вам увеличить прибыль, снизить издержки или эффективно управлять производством. Прочитав эту книгу, вы сможете cпрогнозировать результаты выборов, научитесь определять точки безубыточности, рассчитывать вероятность выигрыша в кости или победы любимой команды в турнире. Хотите обогнать конкурентов? Решайте в Excel реальные задачи!

Инструмент Получить и преобразовать данные

Обсуждаемые вопросы

• Как загрузить актуальный курс биткойна и сделать так, чтобы эти данные обновлялись каждый день?

• Как загрузить цифры текущего населения городов США?

Бизнес-аналитикам нередко нужен простой способ импортировать в Excel данные из интернета, текстового файла, базы данных или другого источника. Эти данные нужно упорядочить или обработать. Наконец, импортированные данные должны обновляться, не отставать от изменений в их источнике. В этой главе мы познакомим читателя с потрясающими возможностями инструмента Excel 2019 «Получить и преобразовать данные» (Get & Transform), который позволяет аналитикам эффективно импортировать, по-новому упорядочивать и преобразовывать данные. Как видно из рис. 40.1, начиная с Excel 2016 инструмент «Получить и преобразовать данные» находится непосредственно на вкладке Данные (Data).

Как показано на рис. 40.2 и 40.3, нажав на кнопку Получить данные (Get Data), вы увидите подробный список источников данных, поддерживаемых инструментом Получить и преобразовать данные.

По причине ограниченности печатных площадей мы остановимся только на применении инструмента «Получить и преобразовать» к данным, добытым из интернета: как их импортировать, упорядочивать и преобразовывать.

Ответы на вопросы

Как загрузить актуальный курс биткойна и сделать так, чтобы эти данные обновлялись каждый день?

Многие люди завороженно наблюдают за ежедневными вариациями в стоимости биткойна. В этой главе мы покажем, как импортировать свежие ежедневные курсы биткойна в Excel. Наш рабочий лист будет отражать курс биткойна за последние 100 дней. Данные можно будет в любой момент обновить, чтобы отразить самые свежие.

Для начала нам нужен веб-адрес, по которому находятся ежедневные курсы биткоийа. К счастью, эти сведения есть на Yahoo Finance. Нужный нам URL — finance.yahoo.com/quote/BTC-USD/history. Если вам нужны сведения по акциям (например, «Microsoft»), просто замените текст после /quote условным биржевым обозначением (тикером). Например, цены на акции «Microsoft» можно импортировать с адреса finance.yahoo.com/quote/MSFT/history. В пустой книге Excel нажмите Получить данные (Get Data) на вкладке Данные (Data), в разделе Получить и преобразовать данные (Get & Transform) на ленте и выберите Из других источников (Other Sources). Выбрав Из Интернета (From Web), заполните диалоговое окно, как показано на рис. 40.4. Вот вы и создали поисковый запрос! Нажав OK, вы увидите список всех таблиц, содержащихся по указанному веб-адресу (рис. 40.5). Щелкнув по таблице 2 (Table 2), вы увидите предпросмотр того, что будет импортировано. В нашем случае таблица 2 содержит необходимую информацию о курсе биткойна.

Теперь, если хотите, можете выбрать Загрузить (Load) и сразу же загрузить данные в свою книгу. Мы же, однако, вместо этого решили упорядочить импортируемые данные иным образом, поэтому выбираем Преобразовать данные (Edit), что вызывает окно Редактор Power Query (Power Query Editor), представленное на рис. 40.6.

Допустим, вам нужно импортировать только столбцы Дата (Date) и Скорректированная цена закрытия (Adj Close). Тогда с помощью клавиши Control мы выделяем столбцы, которые хотим удалить. Щелкнув правой кнопкой мыши, выберите Удалить столбцы (Remove Columns), и у вас останутся только столбцы Дата (Date) и Скорректированная цена закрытия (Adj Close). Или, предположим, вам также надо импортировать неделю года. Для этого сначала выполните щелчок правой кнопкой мышки по столбцу Дата (Date) и выберите Создать дубликат столбца (Duplicate Column). Выполнив на нем щелчок правой кнопкой мышки, выбираем Переименовать (Rename) и переименовываем дубликат столбца с датами именем Неделя года (Week of Year).

Выделив столбец Неделя года (Week Of Year), выберите из контекстного меню Преобразование (Transform), затем Неделя (Week) и Неделя года (Week Of Year). Как видим на рис. 40.7, теперь у нас есть столбец Неделя года (Week Of Year).

Теперь мы готовы импортировать нужные нам данные в Excel. Просто выберите Закрыть и загрузить (Close And Load) на вкладке Главная (Home). Вы увидите курс биткойна за 100 последние дней, как показано на рис. 40.8 и в файле Bitcoinquery.xlsx.

Чтобы в любой момент обновить данные, просто поместите курсор в ячейку внутри импортированных данных, выполните щелчок правой кнопкой мышки и выберите Обновить (Refresh). Если вы хотите, чтобы информация обновлялась через заданные промежутки времени или при каждом открытии файла, то выберите Обновить все (Refresh All) на вкладке Данные (Data), в группе Запросы и подключения (Queries and Con nections), выберите Запросы и подключения и затем на появившейся панели в Запросах щелкните по Table 2 и из контекстного меню выберите Свойства…. Теперь в диалоговом окне Свойства запроса (Query Properties) вы можете настроить параметры его обновления. Как показано на рис. 40.9, мы задали период обновления — каждые 60 минут.

Как загрузить цифры текущего населения городов США?

Допустим, вы хотите импортировать в Excel население 100 крупнейших городов США. Необходимые сведения содержатся на сайте worldpopulationreview.com/us-cities. Применяя тот же метод, что мы использовали для загрузки курсов биткойна, мы выбрали Таблицу 0 (Table 0) и получили результаты, представленные на рис. 40.10.

Предположим, мы хотим, чтобы город и штат находились в одном столбце, а население каждого города — в другом. Также предположим, что иные сведения нам не нужны, и мы не хотим их импортировать. Чтобы добиться этого, нажимаем Преобразование (Transform), удалим с помощью клавиши Control последние четыре столбца. Затем выбираем столбцы Город (City) и Штат (State). Выбираем Объединить столбцы (Merge Columns) из контекстного меню и выбираем запятую в качестве символа-разделителя между городом и штатом. Переименование получившегося столбца предложено выполнить здесь же — называем его Город и штат (City and state). Теперь из меню Файл (File) мы сможем загрузить требующиеся нам данные и поисковый запрос в файл UScityquery.xlsx. Конечный результат представлен на рис. 40.11.

Если вы хотите увидеть, из каких шагов состояло выполнение нашего интернет-запроса, поместите курсор в ячейку внутри импортированных данных и выберите Запрос (Query) в правой части меню ленты. Щелкнув Редактировать (Edit) в правой части экрана, вы увидите шаги, которые потребовались для реализации вашего запроса (рис. 40.12). Панель Параметры запроса также, как правило, отображается сама — где мы видим Примененные шаги. Разумеется, выбрав Закрыть и загрузить (Close And Load), вы вернетесь к книге Excel.

» Более подробно с книгой можно ознакомиться на сайте издательства
» Оглавление
» Отрывок

Для Хаброжителей скидка 25% по купону — Microsoft Excel

По факту оплаты бумажной версии книги на e-mail высылается электронная книга.