Как сделать картограмму в excel
Визуализация геоданных на карте
Если ваша компания имеет филиалы по стране или продает не только в пределах МКАД, то рано или поздно вы столкнетесь с задачей наглядно отобразить числовые данные из Microsoft Excel (продажи, заявки, объемы, клиентов) на географической карте с привязкой к конкретным городам и регионам. Давайте вкратце пробежимся по основным способам визуализации геоданных, которые существуют в Excel.
После вставки компонента на листе должен появится динамический контейнер с картой. Для визуализации на карте вашей информации нужно выделить диапазон с геоданными и нажать кнопку Показать местоположения (Show Locations):
При необходимости в настройках (иконка с шестеренкой в правом верхнем углу компонента) можно поменять цвета и тип отображаемых диаграмм:
Также возможно быстро отфильтровать города, отобразив только нужные (значок воронки в правом верхнем углу компонента).
Итого в плюсах этого способа: легкая бесплатная реализация, автоматическая привязка к карте, два типа диаграмм, удобная фильтрация.
В минусах: нужен Excel 2013 с доступом в интернет, нельзя выделять области и районы.
Превратить таблицу в географическую карту можно легко с помощью кнопки Карта (Map) на вкладке Конструктор (Design) :
Итого в плюсах: легкость использования и гибкость настройки, возможность дробить одну карту на несколько по категориям.
В минусах: Power View есть не во всех комплектациях Excel 2013, нет других типов диаграмм кроме пузырьковых и круговых.
Это отдельная COM-надстройка для максимально тяжелых случаев, когда нужна сложная, профессионально выглядящая, анимированная визуализация большого количества данных на любой (даже пользовательской карте), причем с видео динамики процесса во времени. На этапе разработки она имела рабочее название GeoFlow, а позже была переименована в Power Map. К сожалению, в полноценном варианте эта надстройка доступна только покупателям либо полной версии Microsoft Office 2013 Pro, либо подписчикам корпоративных версий Office 365 с тарифным планом Business Intelligence (BI). Однако превьюшку этой надстройки товарищи из Microsoft дают скачать «на поиграться» совершенно бесплатно, за что им спасибо.
. и нажать кнопку Карта, то мы попадем в отдельное окно надстройки Microsoft Power Map:
Если не вдаваться в детали (которых тут хватит на отдельный тренинг на полдня), то общие принципы работы с картой тут те же, что и в Power View, описанной выше:
Способ 4. Пузырьковая диаграмма с «доработкой напильником»
Самый «колхозный» способ из всех перечисленных, но зато работающий во всех версиях Excel. Построить пузырьковую диаграмму (Bubble Chart), отключить у нее оси, сетку, легенду. т.е. все, кроме пузырей. Затем вручную подогнать положение пузырей, подложив под диаграмму скачанное заранее изображение нужной карты:
Минусы этого способа очевидны: долго, муторно, много ручной работы. Да еще и вывод подписей к пузырям представляет собой отдельную проблему, когда их много.
Плюсы в том, что этот вариант будет работать в любой версии Excel, в отличие от следующих способов, где обязательно требуется Excel 2013. Да и подключение к интернету не требуется.
Способ 5. Сторонние приложения и надстройки
Из оставшихся в живых достойны упоминания:
VBA Projects
Programming and other
Новая версия карты России в Excel
Готова очередная версия Умных Карт в Excel.
Новое в этой версии: 
1. Возможность отображать подписи к регионам на карте: название региона или столицы на выбор и числовое значение. Числовое значение может быть отличным от используемого для раскраски карты.
2. Республика Крым.
Векторная карта России легко масштабируется и перекрашивается, хорошо подходит для включения в отчеты и презентации Word, PowerPoint, PDF и др.
Исходные коды как всегда открыты для доработки, если нужно что-то «заточить под себя».
Новая версия карты России в Excel : 8 комментариев
В версии 3.1: добавлен более подходящий, с простым очертаниями, Крым. На скрытом листе добавлена симпатичная хорошо проработанная Векторная карта Российской Федерации http://www.sklyarovstudio.ru/works/graphdesign/russia/. Работа над ее подключением к программе только началась.
clam, добрый день, помогите пож-та разобраться, что не так.
Скачала версию 3.1, запуск макросов и актив-х включен, после открытия файла кнопки не доступны, при переходе с листа данные на схему выдается ошибка 438 Object doesn’t support…..
ругается на это:
Public Sub Init()
‘Äîáàâëÿåì íàçâàíèÿ íàáîðîâ äàííûõ â «ñåëåêò»
Dim col As Integer
Dim tmp As String
With Worksheets(ListN3).ComboBox1
Здравствуйте.
Не получается изменить подписи на карте с областей на города. изменения на вкладке данные не помогают.
В новой версии 3.2 можно выбрать подписи на карте из двух вариантов — в примере это название региона или столица региона. Название региона должно точно соответствовать названию полигона региона на карте. Столбец с названием города можно редактировать по своему усмотрению.
Здравствуйте! Подскажите, пожалуйста, как можно вставить карту в Word. Не могу сам разобраться:-(.
А что конкретно не получается? Регионы сгруппированы, их надо скопировать и вставить в документ Word.
Добрый день!! очень хочется разобраться как работает данная разработка)) но пока не получается. может у вас есть какая-нибудь инструкция?! кнопки обновить не нажимаются… и скажите есть возможность вставить карту пермского края, а не России в целом??
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
Приступая к работе с 3D Maps
Примечание: Power Map — наша популярная надстройка для Excel 2013, предназначенная для трехмерной геопространственной визуализации, — теперь интегрирована в Excel. Мы также дали ей более понятное имя — 3D Maps. Вы найдете эту функцию наряду с другими возможностями визуализации на вкладке «Вставка».
Microsoft 3D Maps для Excel — это инструмент для трехмерной визуализации данных, который позволяет взглянуть на информацию по-новому. С помощью 3D Maps вы можете получить сведения, которые не были доступны при традиционных двухмерных таблицах и диаграммах.
В 3D Maps можно отобразить географические и временные данные на глобусе или пользовательской карте, показать их со временем, а также создать наглядные туры, которые можно будет показывать другим людям. С помощью 3D Maps можно:
Карта данных Визуально отобразить более миллиона строк данных на картах Microsoft Bing в 3D-формате из таблицы или модели данных Excel.
Обнаружение информации Повысьте понимание, просматривая данные в географическом пространстве и просматривая изменение данных с отметками времени с течением времени.
Делитесь историями Сделайте снимки экрана и создайте кинематографические управляемые видеотуры, которые можно сделать более интересными для широкой аудитории, как никогда раньше. Или экспортировать туры в видео и поделиться ими таким же образом.
Кнопка 3D Map есть в группе «Туры» на вкладке «Вставка» ленты Excel рядом с группой «Диаграммы».
Создание первой карты 3D Maps
Если у вас есть данные Excel с географическими свойствами в табличном формате или в модели данных (такие как строки и столбцы, содержащие названия городов, областей, краев, стран или регионов, почтовые индексы либо значения долготы и широты), вы готовы приступить к работе. Вот как это сделать:
В Excel откройте книгу, которая содержит таблицу или данные модели данных, которые вы хотите изучить в 3D Maps.
Сведения об использовании собственных данных см. в статье Подготовка данных для 3D Maps.
Если у вас еще нет данных для изучения, попробуйте скачать один из примеров наборов данных.
Click any cell in the table.
Щелкните Вставка > Карта 3D Maps.
(При первом нажатии кнопки Карта 3D Maps автоматически включается 3D Maps.)
Карта 3D Maps использует Bing для геокодирования данных на основании их географических свойств. Через несколько секунд появится глобус рядом с первым экраном области слоев.
В области слоев проверьте, правильно ли сопоставлены поля, и щелкните стрелку раскрывающегося списка для неправильных полей, чтобы сопоставить их с нужными географическими свойствами.
Например, убедитесь, что Красноярск распознается как Город в раскрывающемся списке.
Когда 3D Maps отображает данные, на глобусе появляются точки.
Изучение примеров наборов данных в 3D Maps
С помощью примеров наборов данных можно ознакомиться с 3D Maps. Скачайте одну из указанных ниже книг, в которых уже есть геопространственные записи, необходимые для начала работы:
Как сделать карту в excel?
Работа с географическими картами в Excel
Работа с географическими картами
Здесь приведено задание для Excel 2000. В других версиях может быть реализована несколько другая реализация.
Связанные статьи
Рекомендую прочесть статьи, связанные с данной:
Создание карт в Excel;
Excel.
Лекция № 14.
Табличный процессор – это комплекс взаимосвязанных программ, предназначенный для обработки электронных таблиц.
Электронная таблица – это программа обработки числовых данных, хранящая и обрабатывающая данные в прямоугольных таблицах.
Таблица – форма организации данных по столбцам и строкам.
Excel – это программа работы с электронными таблицами, которая предназначена для регистрации и анализа числовой, текстовой и другой информации, используемая в основном для ведения бухгалтерской и финансовой документации.
Электронная таблица состоит из столбцов и строк. Заголовки столбцов обозначаются буквами (A,B,C…), заголовки строк – числами. Ячейка – пересечение столбца и строки. Каждая ячейка имеет свой собственный адрес. Он составляется из заголовка столбца и заголовка строки (A1,C5…). Ячейка, с которой производятся какие-либо действия, выделяется рамкой и считается активной.
Электронные таблицы позволяют работать с тремя основными типами данных:
Табличный процессор Excel позволяет:
Числа могут быть представлены как в обычном виде, так и в экспоненциальном, и выравниваются по разрядам при размещении по столбцам, для удобства пользователя.
Электронные таблицы позволяют представить числовые данные в виде диаграмм или графиков:
В электронных таблицах существуют два вида ссылок:
Различия между ними проявляются при копировании формулы из активной ячейки в другую ячейку.
Относительнаяссылка в формуле используется для указания адреса ячейки, в которой находится формула. При перемещении или копировании формулы из активной ячейки относительные ссылки автоматически обновляются в зависимости от нового положения формулы.
Абсолютнаяссылка в формуле используется для указания фиксированного адреса ячейки. При перемещении или копировании формулы абсолютные ссылки не меняются. В абсолютных ссылках перед неизменяемым значением адреса ячейки ставится знак доллара ($A,$1).
Если символ доллара стоит перед буквой ($A,1), то координата столбца абсолютная, а строки – относительная. Если символ доллара стоит перед цифрой (A,$1), то координата столбца относительная, а строки – абсолютная. Такие ссылки называются смешанными.
Электронные таблицы позволяют осуществить сортировку данных, они могут сортироваться по возрастанию или убыванию, выстраиваются в определенном порядке. В электронных таблицах возможен поиск данных в соответствии с указанными условиями – фильтрами.
Ячейки рабочего листа электронной таблицы могут содержать:
Аргументами функции могут быть:
Excel содержит более 400 встроенных функции, условно разделенных на несколько категорий:
Режимы работы табличного процессора:
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. Преобразование строк в столбцы и обратно
Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:
7. Выпадающий список в ячейке
Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и так далее), то это можно легко организовать при помощи выпадающего списка.
8. Умная таблица
Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:
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 возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Вот самый быстрый и наглядный способ сделать это:
12. Подбор (подгонка) результатов расчёта под нужные значения
Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!
Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!
Как сделать карту в excel?
Добрый день, дорогие друзья! Иногда при подготовке информационных материалов возникает потребность подготовить карту России с окрашенными в различный цвет областями (в зависимости от показателей). Этот тип карт носит название Colormap (колормап, цветовая карта).
Чудеса иногда случаются
Несколько лет назад я пытался сделать такую карту. Как вы сами понимаете, рисовать такую карту вручную достаточно утомительно. Поиск программ для построения подобных карт показал, что такие программы существуют, но практически все они они требуют наличия серверного ПО + Microsoft SQL.
Это, как вы понимаете, сильно ограничивает возможность применения «колормап» на обычном рабочем компьютере, на котором, как правило, нет всей этой специализированной начинки. Я возиться тогда с этим не стал, ибо поджимали сроки.
Сам по себе макрос представляет собой файл формата MS Excel, состоящий из трёх листов. Первый лист содержит текстовую инструкцию по работе с макросом. Второй лист служит для ввода табличных данных по какому-то параметру в разрезе субъектов Российской Федерации. Поддерживается возможность одновременного ведения до 10 параметров и, соответственно, отображения последовательно, с помощью переключателя на карте, 10 вариантов карт (по этим введенным параметрам).
В принципе, в инструкции на первом листе всё достаточно подробно расписано, так что я опишу лишь дополнительные нюансы работы с макросом, которые мне удалось выявить в процессе его использования.
Выращиваем пингвинов
Давайте сделаем карту, на которой сделаем учебный пример, в котором у нас всем субъектам Российской Федерации спустили план по выращиванию пингвинов. Есть таблица с результатами по субъектам РФ от 0 до 100% выполнения плана. На карте России нам нужно отобразить субъекты, полностью, на 100% выполнившие план. После того, как необходимые цифры введены в столбцы листа «ДАННЫЕ», нам нужно определиться, на какое количество групп мы хотим разбить наши данные на карте. И, соответственно, в какое количество цветов окрасится наша цветовая карта. Исходя из условий задачи, нам нужно отобразить две группы (градации) — регионы, полностью выполнившие план и остальные регионы, не выполнившие плановые показатели. Соответственно, у нас будет две группы значений:
1 группа: от 0 до 99,99%;
2 группа: от 100 до 101%.
Управляющая панель, меняющая параметры отображения карты, расположена в левом верхнем углу листа «СХЕМА» (см. Рис.1)
Рисунок 1. Управляющая панель цветовой карты
Рисунок 2. Устанавливаем количество групп (количество цветовых групп на нашей карте)
На выходе получаем следующие изменения:
1. Количество ячеек, управляющих цветовой гаммой карты, сократилось до двух;
2. Такая же ситуация с числовыми диапазонами — их также стало два (см. Рис.3).
Рисунок 3. Новые числовые и цветовые диапазоны карты
Рисунок 4. Изменение диапазонов и цветовой окраски регионов
Соответственно, на выходе мы получаем вот такую карту (см. Рис. 5). На этой карте у нас синим цветом показаны субъекты Российской Федерации, полностью (показатель равен 100%) выполнившие план по выращиванию пингвинов.
Рисунок 5. Результат в виде двухцветной карты субъектов РФ
5 простых и эффективных возможностей Excel, о которых вы можете не знать
23 June 2015 | Пресс-Центр
По теме Новые функции в серии постов Советы по Office и Windows 10
Все знают, что Excel предлагает множество различных функций, таких как построение диаграмм или использование формул, галерей и библиотек. Все эти и множество других возможностей делают Excel одним из самых действенных инструментов бизнес-аналитики. Но среди всего этого многообразия есть несколько простых в использовании и одновременно эффективных функций, о которых вы, возможно, не знали. Речь пойдет о спарклайнах (инфокривых), условном форматировании, срезах и нескольких формулах.
Спарклайны
Спарклайны впервые появились в Excel 2010. Это – мини-графики, размещенные в одной ячейке и помогающие представить изменения данных в визуальной форме. Они могут быть исключительно полезны, хотя не многие аналитики используют спарклайны в своей повседневной работе.
Чтобы создать собственный спарклайн, достаточно выделить фрагмент данных, перейти на вкладку «Вставка» и выбрать один из трех типов спарклайнов: график, гистограмму и диаграмму выигрыш/проигрыш. Последняя показывает столбиками одинаковой высоты значения “+” и “-“.
Условное форматирование
Возможности доступные благодаря условному форматированию, позволяют быстро создавать тепловые карты, интерпретировать таблицы данных с помощью цветовой маркировки – для большей наглядности и удобного визуального анализа. Это – простой и эффективный способ изучать ваши данные и находить интересные закономерности.
Ниже приведена тепловая карта, на которой цветовые маркировки были применены к той же таблице, что и спорклайны выше. Карта позволяет моментально увидеть высокие (темно-зеленый) и низкие (белый) продажи по различным продуктам и датам.
Чтобы использовать условное форматирование, достаточно выбрать исходные данные, а затем в разделе «Главная» выбрать «Условное форматирование» – «Цветовые шкалы» и цвета. Вы также можете настроить параметры цвета путем изменения правил форматирования.
Функции «Наибольший» и «Наименьший»
Хотя все знают о возможностях функции «наибольший» и «наименьший», немногие используют их, чтобы найти первое, второе или N-е наибольшее (или наименьшее) количественное значение.
С помощью тех же данных о продажах из приведенных выше примеров, мы можем проиллюстрировать возможности использования функции «наибольший», которая позволит оперативно выявить два лучших продукта для каждого месяца.
Узнать больше об этой и других подобных возможностях вы сможете с помощью этого файла Excel (англ.).
Удаление повторений
Есть множество неизменных задач в работе офисных служащих. Организация и очистка данных – одна из них. Функция «Удаление повторение», появившаяся в Excel 2007, по-прежнему очень востребована.
Просто выберите фрагмент, а затем во вкладке «Данные» выберите «Удалить дубликаты» – это действительно просто!
Срезы
Срезы можно отлично использовать в качестве визуальных фильтров. Они помогут сфокусироваться при анализе и быстро сузить зону поиска соответствий. Эта возможность появилась в Excel 2010 и стала еще лучше в Excel 2013.
Например, используя срезы мы можем создать интерактивную диаграмму трендов продаж. При представлении данных о продажах, мы можем легко переключаться между различными продуктами с помощью той же диаграммы.
Чтобы добавить срез для диаграмм в Excel 2013, выделите диапазон данных и нажмите на ленте кнопку «Вставка», «Срез», затем выберите часть ваших данных, которые вы хотите использовать в качестве фильтра.
Узнать больше об этой и других подобных возможностях вы сможете с помощью этого файла Excel (англ.).