Содержание

Как сделать карту в excel?

Динамическая карта в Excel из фигур и макросов VBA скачать

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

Как построить динамическую карту из фигур в Excel

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

  • надстроек (Power Map, Power View);
  • компонентов (Bing Maps);
  • сторонних приложений (MapCite, Esri Maps, MapLand).

Более того некоторые из них платные. А главный недостаток – это ограниченность при обмене информацией. То есть чтобы открыть файл с визуальным отчетом на динамической карте необходимо подключение дополнительных модулей для исправной работы в процессе визуального анализа.

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

Как рисовать в Excel сложные фигуры из векторной графики

В программе Excel можно создавать сложные фигуры векторной графики. Еще больше возможностей для создания сложных фигур предоставляет программа Power Point, но они все еще ограниченные. Сложную фигуру, созданную в программе Power Point, можно легко скопировать через буфер обмена и вставить в Excel для дальнейшего использования. Но чтобы нарисовать такую сложную фигуру как «карта мира» средствами Power Point – на это уйдет очень много нерационально потраченного времени, не смотря на то что теоретически это возможно. Хотя и микроскопом гвозди забивать, также теоретически возможно. Существуют более эффективные решения для данной задачи – это импорт векторной графики в Excel.

Например, можно быстро нарисовать векторную карту мира в специальных графических редакторах, предназначенных для векторной графики такие как Adobe Illustrator или CorelDRAW и др. Более того можно на просторах интернета бесплатно скачать уже готовые файлы векторного формата с нарисованной картой мира. Самый популярный формат векторной графики – это *.SVG. Он представляет из семя обычный текстовый XML файл который по правилам разметки описывает все кривые созданной фигуры по примитивам.

Несмотря на это программа Excel на сегодняшний день не импортирует такие, казалось бы, весьма близкие по духу XML форматы рисунков фигур в векторной графике. В то время как программные продукты из других офисных пакетов LibreOffice аналогично работающие с электронными таблицами LibreOffice Calc прекрасно поддерживают импорт файлов в формате SVG – самого популярного типа для хранения и обмена векторной графикой по стандарту XML. Более того в программе из данного пакета LibreOffice Impress предоставляются более широкие возможности редактирования фигур, импортированных из SVG файлов: преобразование в кривые, разбивка, разделение и др.

Но в Excel не все так печально с векторной графикой. Очень радует тот факт, что программа Excel прекрасно поддерживает импорт файлов векторной графики в формате EPS (Encapsulated PostScript). Данный формат был разработан в компании Adobe для обмена графическими данными между программами. Убедимся в этом на практике импортируя векторную графику в Excel из файла continents.eps, который был предварительно создан в редакторе Adobe Illustrator.

Создайте новый рабочий лист с названием «Визуализация» и выберите инструмент: «ВСТАВКА»-«Иллюстрации»-«Рисунки» и укажите путь к файлу continents.eps после чего нажмите на кнопку «Вставить». В результате у нас появится пока-что не векторная фигура, а только лишь рисунок, но из векторной графики:

Нам всего лишь нужно щелкнуть правой кнопкой мишки по вставленном рисунку и если он из векторной графики, то будет активна и доступна опция «Группировать»-«Разгруппировать» в появившемся контекстном меню. В результате мы получим следующее предложение: «Это импортированный рисунок, а не группа. Преобразовать его в рисунок Microsoft Office?».

Жмем на кнопку «Да» а потом комбинацию клавиш ALT+F10, чтобы убедиться в желаемом результате:

Я Вас поздравляю! Теперь мы можем работать с векторным рисунком как с обычными фигурами в Excel. Только представьте себе какие теперь отрылись новые широкие возможности в областях визуализации данных и создания сложной динамической инфографики в Excel.

Пример создания динамической карты для визуализации данных

Из внешних источников были импортированные данные на рабочий лист «Данные» для последующей их обработки и визуализации:

В данной таблице представлены количественные показатели ежемесячных продаж трех категорий товаров по шести континентам мира на протяжении трех лет с 2019 по 2021 года.

Подготовка исходных данных

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

Далее возвращаемся на лист «Визуализация» и создаем таблицу с графиком так как описано в статье:

В результате должно получиться так:

Важный момент! В ячейках B1, C1 и D1 мы указываем цвета заливки, в которые будут окрашены континенты соответственно популярности товаров в них. Макрос будет считывать цвета из фонов этих ячеек. Так сделано для удобства пользователя, который сможет самостоятельно изменять цвета для подсветки соответственных континентов по данным отчета. Например, серый цвет для товара-2 заменить на синий или фиолетовый.

Обработка входящих показателей статистического отчета

Далее создаем таблицу, по которой будет определятся номер товара 1-3 наиболее продаваемый на том или ином континенте по каждому месяцу. Чтобы вычислить каждое значение для всех ячеек второй таблицы рейтинга категорий товаров по континентам, мы будем использовать сложную формулу массива. В ячейку P2 вводим формулу массива и жмем комбинацию клавиш: CTRL+SHIFT+Enter при вводе. После чего растягиваем маркером формулу по всему диапазону ячеек P2:U37:

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

Вычисления для визуализации на динамической карте в Excel

Итоговые значения показателей популярности товаров на континентах карты у нас вычисляются в зависимости от указанного периода с помощью интерактивных элементов управления диаграммой и графиком отчета (2 счетчика Spinner). Популярность товара в определенный промежуток времени вычисляется с помощью функции МОДА. А полная версия формулы для третьей таблицы выглядит следующим образом:

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

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

Создание динамических изменений на карте с помощью макросов VBA

Чтобы создать макрос в Excel откройте редактор макросов VBA комбинацией клавиш ALT+F11 или через меню: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Там же создайте модуль через меню: «Insert»-«Module» и вставьте в него следующий код макроса:

Читать еще:  Как сделать этикетку в excel?

Для удобства просто скопируйте этот код в модуль макроса:

Dim list As Worksheet
Dim NorthAmerica As Shape
Dim SouthAmerica As Shape
Dim Europe As Shape
Dim Asia As Shape
Dim Africa As Shape
Dim Australia As Shape
Dim tovar1 As Long
Dim tovar2 As Long
Dim tovar3 As Long
tovar1 = Range( «B1» ).Interior.Color
tovar2 = Range( «C1» ).Interior.Color
tovar3 = Range( «D1» ).Interior.Color

Dim a() As Variant
a = Array(tovar1, tovar2, tovar3)

Set list = Sheets( «Визуализация» )
Set NorthAmerica = list.Shapes( «Freeform 1» )
Set SouthAmerica = list.Shapes( «Freeform 2» )
Set Europe = list.Shapes( «Freeform 3» )
Set Asia = list.Shapes( «Freeform 4» )
Set Africa = list.Shapes( «Freeform 5» )
Set Australia = list.Shapes( «Freeform 6» )

NorthAmerica.Fill.ForeColor.RGB = a(Range( «I39» ) — 1)
SouthAmerica.Fill.ForeColor.RGB = a(Range( «J39» ) — 1)
Europe.Fill.ForeColor.RGB = a(Range( «K39» ) — 1)
Asia.Fill.ForeColor.RGB = a(Range( «L39» ) — 1)
Africa.Fill.ForeColor.RGB = a(Range( «M39» ) — 1)
Australia.Fill.ForeColor.RGB = a(Range( «N39» ) — 1)

Важный момент! Для корректной работы макроса следует переименовать все фигуры континентов карты в:

  1. Северная Америка – Freeform 1.
  2. Южная Америка – Freeform 2.
  3. Европа – Freeform 3.
  4. Азия – Freeform 4.
  5. Африка – Freeform 5.
  6. Австралия – Freeform 6.

Жмем комбинацию клавиш ALT+F10 и переименовываем как показано ниже на рисунке:

И наконец для обоих интерактивных элементов типа «Счетчик» (Spinner) присвойте один и тот же макрос «continents»:

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

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

Визуализация геоданных на карте

Если ваша компания имеет филиалы по стране или продает не только в пределах МКАД, то рано или поздно вы столкнетесь с задачей наглядно отобразить числовые данные из Microsoft Excel (продажи, заявки, объемы, клиентов) на географической карте с привязкой к конкретным городам и регионам. Давайте вкратце пробежимся по основным способам визуализации геоданных, которые существуют в Excel.

Способ 1. Быстро и бесплатно — компонент Bing Maps

Начиная с 2013 версии в Excel встроен магазин приложений, т.е. появилась возможность докупать, скачивать и устанавливать дополнительные модули и надстройки с недостающими функциями. Один из таких компонентов как раз и позволяет наглядно отображать числовые данные на карте — он называется Bing Maps и, что особенно приятно, абсолютно бесплатен. Для его установки откройте вкладку Вставка — Магазин (Insert — Office Apps):

После вставки компонента на листе должен появится динамический контейнер с картой. Для визуализации на карте вашей информации нужно выделить диапазон с геоданными и нажать кнопку Показать местоположения (Show Locations):

При необходимости в настройках (иконка с шестеренкой в правом верхнем углу компонента) можно поменять цвета и тип отображаемых диаграмм:

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

Спокойно можно привязываться не только к городам, но и к другим объектам: областям (например, Тульская область), автономным округам (например, Ямало-Ненецкий АО) и республикам (Татарстан) — тогда кругляш диаграммы будет отображен в центре области. Главное, чтобы название в таблице совпадали с подписями на карте.

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

В минусах: нужен Excel 2013 с доступом в интернет, нельзя выделять области и районы.

Способ 2. Гибко и красиво — режим карты в отчетах Power View

Некоторые версии Microsoft Excel 2013 идут в комплекте с мощной надстройкой визуализации отчетов, которая называется Power View и позволяет (помимо всего прочего, а умеет она много!) наглядно отображать данные на карте. Для активации надстройки откройте вкладку Разработчик (Developer) и щелкните по кнопке Надстройки COM (COM Add-ins) . В открывшемся окне поставьте галочку напротив Power View и нажмите ОК. После всех этих манипуляций на вкладке Вставка (Insert) у вас должна появиться кнопка Power View.

Теперь можно выделить диапазон с исходными данными нажать на эту кнопку — в вашей книге будет создан новый лист (больше похожий на слайд из Power Point), где отобразятся выделенные данные в виде таблицы:

Превратить таблицу в географическую карту можно легко с помощью кнопки Карта (Map) на вкладке Конструктор (Design) :

Обратите особое внимание на правую панель Поля Power View — на ней, в отличие от примитивных Bing Maps, перетаскивая мышью названия столбцов (полей) из исходной таблицы и бросая их в разные области, можно очень гибко настраивать получившееся геопредставление:

  • В область Местоположения (Locations) нужно закинуть столбец из исходной таблицы содержащий географические названия.
  • Если у вас нет столбца с названием, но есть колонки с координатами, то их нужно поместить в области Долгота (Longitude) и Широта (Latitude) , соответственно.
  • Если в область Цвет (Color) закинуть товар, то каждый пузырек будет кроме размера (отображающего общую прибыль по городу) детализирован на дольки по товарам.
  • Добавление поля в области Вертикальных или Горизонтальных множителей(Dividers) разделит одно карту на несколько по этому полю (в нашем примере — по кварталам).

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

Если данных очень много, то на вкладке Power View можно включить специальную Область фильтров (Filters) , где с помощью привычных галочек можно выбрать какие именно города или товары нужно показать на карте:

Итого в плюсах: легкость использования и гибкость настройки, возможность дробить одну карту на несколько по категориям.

В минусах: Power View есть не во всех комплектациях Excel 2013, нет других типов диаграмм кроме пузырьковых и круговых.

Способ 3. Дорого и профессионально — надстройка Power Map

Это отдельная COM-надстройка для максимально тяжелых случаев, когда нужна сложная, профессионально выглядящая, анимированная визуализация большого количества данных на любой (даже пользовательской карте), причем с видео динамики процесса во времени. На этапе разработки она имела рабочее название GeoFlow, а позже была переименована в Power Map. К сожалению, в полноценном варианте эта надстройка доступна только покупателям либо полной версии Microsoft Office 2013 Pro, либо подписчикам корпоративных версий Office 365 с тарифным планом Business Intelligence (BI). Однако превьюшку этой надстройки товарищи из Microsoft дают скачать «на поиграться» совершенно бесплатно, за что им спасибо.

После скачивания и установки надстройку нужно подключить на вкладке Разработчик — Надстройки COM (Developer — COM Add-ins) аналогично Power View из предыдущего пункта. После этого на вкладке Вставка должна появиться кнопка Карта (Map) . Если теперь выделить таблицу с исходными данными:

. и нажать кнопку Карта, то мы попадем в отдельное окно надстройки Microsoft Power Map:

Если не вдаваться в детали (которых тут хватит на отдельный тренинг на полдня), то общие принципы работы с картой тут те же, что и в Power View, описанной выше:

  • Размер столбиков определяется столбцом исходной таблицы (Выручка), который мы закинем в поле Высота в правой панели. Принцип подсчета, как и в сводных таблицах, можно менять в раскрывающемся списке поля:
  • Чтобы детализировать каждый столбик продаж на отдельные товары, нужно закинуть поле Товар в область Категория (Category) .
  • Можно использовать различные типы диаграмм (гистограммы, пузыри, тепловую карту, заливку областей) с помощью кнопок на правой панели:
  • Если в исходных данных есть столбец с датами продажи, то его можно закинуть в область Время (Time) — тогда внизу появится ось времени, по которой можно перемещаться в прошлое-будущее и посмотреть процесс в динамике.
Читать еще:  Как сделать условное форматирование в excel 2010?

«Вау-моментом» надстройки Power Map можно, пожалуй, назвать предельную легкость создания анимированных видеообзоров на основе сделанных карт. Достаточно сделать несколько копий текущей сцены с разных углов просмотра и разным масштабом — и надстройка автоматически создаст 3D-анимацию облета вашей карты с акцентированием внимания на выбранных местах. Полученное видео потом легко сохраняется в mp4-формате в виде отдельного файла для вставки, например, на слайд Power Point.

Способ 4. Пузырьковая диаграмма с «доработкой напильником»

Самый «колхозный» способ из всех перечисленных, но зато работающий во всех версиях Excel. Построить пузырьковую диаграмму (Bubble Chart), отключить у нее оси, сетку, легенду. т.е. все, кроме пузырей. Затем вручную подогнать положение пузырей, подложив под диаграмму скачанное заранее изображение нужной карты:

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

Плюсы в том, что этот вариант будет работать в любой версии Excel, в отличие от следующих способов, где обязательно требуется Excel 2013. Да и подключение к интернету не требуется.

Способ 5. Сторонние приложения и надстройки

Раньше существовало несколько надстроек и плагинов для Excel, позволяющих с той или иной степенью удобства и красоты реализовать отображение данных на карте. Сейчас подавляющее большинство из них либо заброшены разработчиками, либо в стадии тихого отмирания — с Power Map конкурировать тяжело 🙂

Из оставшихся в живых достойны упоминания:

  • MapCite — пожалуй, самая мощная из всех. Умеет привязываться к карте по названиям населенных пунктов, областей, округов и координатам. Выводит данные в виде точек или тепловой карты. Использует карты Bing в виде основы. Автоматом умеет кидать созданную карту в презентации Power Point. Для скачивания доступна бесплатная trial-версия, полная версия стоит 99$/год.
  • Esri Maps — надстройка от компании Esri также позволяющая подгружать и анализировать геоданные из Excel на карты. Много настроек, различные типы диаграмм, поддерживает русский язык. Есть бесплатная демо-версия. Полная версия требует подписки на картографический сервис ArcGis.
  • MapLand— одна из первых надстроек на эту тему, созданных еще для Excel 97-2003. Идет с набором карт в виде графических примитивов, к которым и привязываются данные с листа. Дополнительные карты надо докупать. Для скачивания доступна демка под разные версии Excel, Pro версия стоит 299$.

Работа с картами в Excel 2013

Да, в Excel 2013 стало возможным работать с привлекательными диаграммами, которые содержат географические карты. В данной версии программы появился инструмент «Power View». Это очень мощный инструмент для создания отчётов. С его помощью мы можем создать отчёт о продажах и показать графически данные о продажах и о клиентах на карте.

Изучив эту статью, Вы научитесь создавать в Excel очень крутые отчёты. Мы будем показывать страны, города и заданные точки на географической карте. Статья ориентирована на начинающих пользователей, которые хотят лучше разобраться в данном вопросе.

Важно: Надстройка Power View доступна не во всех версиях Excel 2013.

Первые шаги

Начнём с простого листа Excel.

На этом листе Excel мы имеем названия стран (Country), данные о доходе (Revenue) и о количестве клиентов (Number of Customers). Мы хотим отобразить в Excel доход по каждой стране на карте мира. Сделаем это при помощи отчёта с картами.

Первым делом, выделите нужные данные. Затем нажмите кнопку Форматировать как таблицу (Format as Table) и выберите стиль таблицы.

Ваша таблица будет выглядеть приблизительно вот так:

Следующий шаг – создаём диаграмму при помощи Power View. Для этого на вкладке Вставка (Insert) кликните иконку Power View.

При первом запуске Power View будет предложено включить эту надстройку. Просто нажмите Включить (Enable).

Обратите внимание, что должен быть установлен Silverlight. Это инструмент, предназначенный для создания интерактивных приложений, он необходим для построения отчётов Power View. Появится сообщение с предложением установить Silverlight. Кликните по ссылке Установить Silverlight (Instal Silverlight), чтобы перейти к скачиванию и установке программы.

После включения надстройки для нашего отчёта Power View будет создан новый лист:

Кликните иконку Карта (Map).

Чтобы увеличить размер карты, потяните мышью правый нижний угол вниз и вправо.

Как видите, страны, перечисленные на листе Excel (South Africa, France, Italy и Bolivia) теперь отмечены на карте:

Что означают синие круги?

Синие круги на карте показывают величину дохода из столбца Revenue. Чем больше круг, тем больше доход. Работая с Power View можно настроить, что именно должно быть показано на карте. Это можно указать в поле Размер (Size) панели Поля Power View (Power View Fields):

Может ли Power View распознавать города?

Power View отлично справляется с распознаванием городов. Проверим это на примере вот такой таблицы Excel:

Мы хотим показать на карте доход по каждому городу.

Создайте отчёт точно так же, как это было описано выше в примере со странами. Теперь это сделать проще, поскольку уже не нужно что-то включать или устанавливать.

Создаём новый лист Power View и кликаем иконку Карта (Map):

Как видно на карте, Power View отметил кругами города Houston, San Jose и Des Moines.

Иногда в разных странах встречаются города с одинаковым названием. Например, город Санта-Крус есть в США, Чили, Испании и в Боливии. В таком случае может потребоваться указать дополнительную информацию, чтобы выбрать нужный город.

Как я могу определить страну и город при помощи Power View?

Чтобы решить проблему городов с одинаковыми названиями, нужно указать в Excel название города и страны:

В столбце с названиями городов (столбец City) вводим название города и через запятую название страны.

Чтобы обновить данные, кликаем по карте правой кнопкой мыши и в контекстном меню выбираем Обновить лист (Refresh Sheet).

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

Города по всему миру находятся автоматически. Здорово, правда?

Как я могу обозначить определённые географические координаты на карте?

Если нужно указать на карте точные географические координаты, Excel 2013 снова приходит на помощь. Для этого нам понадобятся широта и долгота.

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

В Excel нам потребуется широта и долгота, чтобы отмечать на карте конкретные точки. Координаты можно узнать в Google Earth, их может подсказать Ваш смартфон или можете воспользоваться любым другим инструментом.

Примечание переводчика: В данном примере для получения координат использовался веб-сайт satelliteview.com, который, к сожалению, в момент перевода статьи уже не функционировал.

Как видите, на этом сайте можно получить широту и долготу любой точки на карте.

Теперь нам нужно создать таблицу Excel с широтой (Latitude) и долготой (Longitude), записанными в столбцах.

Как уже делали ранее, нажмите Power View на вкладке Вставка (Insert) и создайте карту.

Если Power View не обнаружит столбцы с широтой и долготой самостоятельно, укажите их вручную. Мы хотим отобразить на карте величину дохода по каждому магазину из столбца Revenue, для этого на панели Поля Power View (Power View Fields) в поле Местоположения (Locations) выберем Revenue. Посмотрите на рисунок ниже:

Теперь магазины показаны на карте!

Как Вы можете видеть, при помощи Power View мы создали в Excel очень мощный отчёт. Всё, что нам для этого потребовалось – это Excel 2013, Silverlight и немного знаний по географии.

Читать еще:  Как в excel сделать префикс?

Из этой статьи Вы научились отмечать графически страны, города и географические точки при помощи Excel и Power View.

12 простых приёмов для ускоренной работы в Excel

Как быстро добавить данные, создать умную таблицу или спасти несохранённый файл.

Автор проекта «Планета Excel», разработчик и IT-тренер.

1. Быстрое добавление новых данных в диаграмму

Если для построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

Предположим, у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы сделать это, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно. Подобным образом можно извлекать имена из email, склеивать ФИО из фрагментов и так далее.

3. Копирование без нарушения форматов

Вы, скорее всего, знаете о волшебном маркере автозаполнения. Это тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, так как копируется не только формула, но и формат ячейки. Этого можно избежать. Сразу после того, как потянули за чёрный крест, нажмите на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

5. Быстрый переход к нужному листу

Если в файле количество рабочих листов перевалило за 10, то ориентироваться в них становится трудновато. Щёлкните правой кнопкой мыши по любой из кнопок прокрутки ярлычков листов в левом нижнем углу экрана. Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.

6. Преобразование строк в столбцы и обратно

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

  1. Выделите диапазон.
  2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
  3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

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

  1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
  2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
  3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
  4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

8. Умная таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

  1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
  2. Введённые формулы автоматом будут копироваться на весь столбец.
  3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
  4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

10. Восстановление несохранённых файлов

Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.

На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

11. Сравнение двух диапазонов на отличия и совпадения

Иногда при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Вот самый быстрый и наглядный способ сделать это:

  1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
  2. Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
  3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

Ссылка на основную публикацию
Adblock
detector