Как наложить одну таблицу на другую в Excel и выделить совпадения

Обновлено: 22.11.2024

После того как вы добавили карту на лист, вы можете добавлять слои из данных в электронной таблице Excel или из ArcGIS. Когда вы начинаете рабочий процесс «Добавить данные», ArcGIS for Office анализирует данные в текущей выбранной таблице Excel или диапазоне ячеек и предлагает наилучшие способы их представления на карте. Вы можете быстро добавить слой на карту, используя параметры по умолчанию, или указать источник данных, тип местоположения и параметры стиля вручную.

Соображения

Форматы данных

  • Используйте таблицы Excel. В большинстве случаев рекомендуется форматировать данные как таблицу Excel перед добавлением их на карту. Использование таблицы Excel позволяет ArcGIS for Office добавлять в набор данных столбцы, содержащие новую информацию. Дополнительные сведения см. в разделе Подготовка данных.
  • Использовать текстовые значения. Столбцы в вашем наборе данных, которые будут использоваться для определения местоположения (например, почтовые индексы), должны быть отформатированы как текстовые, а не числовые значения. Если ваши данные содержат число, включающее начальный ноль, как это часто бывает с почтовыми индексами, Excel интерпретирует эти поля как числовые значения и удаляет начальный ноль, изменяя исходное значение. Форматирование таких столбцов как текста гарантирует, что ваши данные останутся точными.
  • Использовать форматы времени — если ваши данные содержат поля только для времени (в отличие от полей даты и времени), ArcGIS for Office преобразует эти значения в строки для отображения во всплывающих окнах. Из-за этого, в отличие от истинных значений даты и времени, значения только времени нельзя использовать в анимации времени. Чтобы убедиться, что значения времени отображаются правильно, используйте параметр «Формат ячеек» в Excel, чтобы выбрать формат времени, который будет применяться ко всем ячейкам в этом столбце перед созданием слоя.

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

Некоторые форматы времени ArcGIS for Office могут незначительно отличаться от стандартных форматов времени Excel. В следующей таблице показаны некоторые из этих различий в форматах:

Подготовить данные

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

Чтобы создать таблицу, щелкните вкладку "Вставка" на ленте и выберите "Таблица" . Выберите диапазон, который содержит ваши данные, и нажмите OK. Теперь вы можете использовать эту таблицу для добавления слоя на карту. Если вы не уверены, содержит ли ваша книга таблицы, используйте Диспетчер имен на вкладке Формулы Excel, чтобы просмотреть список таблиц.

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

Сводные таблицы — это особый тип таблиц в Excel, которые используются для суммирования и анализа данных. При добавлении данных ArcGIS for Office автоматически определяет сводные таблицы для использования в мастере добавления данных и включает их при необходимости.

Добавить слой из данных Excel

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

Список слоев появится на карте.

Появляется панель «Добавить из Excel», в которой отображаются доступные наборы данных и типы местоположений для слоя. В поле Набор данных ArcGIS for Office отображает таблицы и диапазоны ячеек на рабочем листе. В поле Типы местоположений отображаются доступные типы местоположений.

Нажмите Добавить на карту .

Выберите набор данных

По умолчанию ArcGIS for Office отображает набор данных, выбранный в данный момент в рабочей книге. В раскрывающемся меню перечислены доступные данные в таблицах Excel или выбранных ячейках. Если ваша электронная таблица содержит несколько таблиц или если ArcGIS for Office не может найти информацию о местоположении, вы должны указать данные, которые будут использоваться для создания карты.

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

  1. На панели "Добавить из Excel" откройте раскрывающееся меню "Набор данных".
  2. Выберите таблицу или диапазон ячеек, чтобы указать области на листе, содержащие данные, которые вы хотите использовать для создания слоя карты.

Чтобы выбрать диапазон ячеек, нажмите Выбрать диапазон ячеек и выберите ячейки на листе Excel. Раскрывающееся меню Набор данных автоматически заполняется идентификатором диапазона (например, $A$1:$D$5). Если выбранный диапазон содержит заголовки, установите флажок Первая строка содержит заголовки.

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

Выберите тип местоположения

Чтобы добавить данные Excel на карту, данные вашего рабочего листа должны содержать хотя бы один из следующих типов местоположений: адреса улиц; значения широты и долготы (координаты); или географические названия, такие как город США, округ США, штат, город мира, страна, почтовый индекс или почтовый индекс. Вы также можете использовать пользовательский тип местоположения, основанный на размещенном сервисе в вашей организации ArcGIS, например, границы парка или территории продаж для вашей организации, или указать систему координат из карты или сервиса объектов в ArcGIS.

Тип местоположения определяет способ сопоставления ваших данных и управляет тем, что отображается в параметрах стиля. Если вы выберете «Адрес» или «Координаты», ваши местоположения будут отображаться в виде точек. Если вы выберете Geography или ESRI JSON Geometry, ваши данные будут отображены в виде полигонов. Вы также можете сопоставить свои данные с пользовательскими типами местоположений, такими как границы парков или территории продаж, которые не включены в список по умолчанию. Дополнительные сведения см. в разделе Типы местоположений.

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

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

  1. На панели "Добавить из Excel" выберите один из типов местоположений по умолчанию в раскрывающемся меню "Типы местоположений".

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

Координаты. Используйте раскрывающиеся меню «Долгота» (X) и «Широта» (Y) для сопоставления столбцов данных, содержащих координаты. При необходимости используйте раскрывающееся меню Пространственная привязка, чтобы изменить систему пространственной привязки.

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

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

Имена слоев

Если вы добавляете данные из Excel на карту с помощью параметра диапазона ячеек, имя слоя автоматически заполняется с использованием выбранного диапазона, например, $A$1:$I$21. Если вы планируете опубликовать карту или слой в ArcGIS, перед этим вы должны вручную изменить имя слоя. В противном случае, когда слой публикуется с размещенным сервисом объектов, неподдерживаемые символы в имени слоя могут вызвать непредвиденные проблемы при выполнении различных задач в ArcGIS.

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

Первая и четвертая записи выделены, поскольку диапазоны дат перекрываются. 04.01.2010 – 08.01.2010 совпадает с периодом 07.01.2010 – 09.01.2010.

Вот шаги, необходимые для создания формулы условного форматирования

  1. Выберите диапазон ячеек B6:D12.
  2. Нажмите левой кнопкой мыши на вкладке "Главная".
  3. Нажмите левой кнопкой мыши на кнопку "Условное форматирование".
  4. Нажмите левой кнопкой мыши на "Новое правило...".
  5. Нажмите левой кнопкой мыши на "Использовать формулу для определения форматируемых ячеек".
  6. Введите =СУММПРОИЗВ(($C6 =$C$6:$C$12))>1 в окне "Формат значений, где эта формула ИСТИНА".
  7. Нажмите левой кнопкой мыши на кнопку "Форматировать...".
  8. Нажмите левой кнопкой мыши на вкладке "Заливка".
  9. Выберите цвет. Этот цвет используется для выделения перекрывающихся записей.
  10. Нажмите левой кнопкой мыши на "ОК".
  11. Нажмите левой кнопкой мыши на "ОК".
  12. Нажмите левой кнопкой мыши на "ОК".

Объяснение формулы условного форматирования

Шаг 1. Отфильтруйте записи, где ($C6 =$C$6:$C$12) имеет значение TRUE

($D6>=$C$6:$C$12) возвращает массив значений TRUE и/или FALSE, если дата в ячейке D6 больше или равна каждой дате в $C$6:$C$12.

Возвращаемый массив (ИСТИНА, ЛОЖЬ, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ)

Шаг 3. Собираем все воедино

($C6 =$C$6:$C$12)) возвращает следующий массив (1,0,0,1,0,0,0).

Это означает, что массивы номер один и четыре перекрываются с C6:D6), но в этом случае мы просто хотим знать, перекрывается ли какой-либо диапазон дат.

=СУММПРОИЗВ(($C6 =$C$6:$C$12)) суммирует массив (1,0,0,1,0,0,0) и возвращает 2.

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

=СУММПРОИЗВ(($C6 =$C$6:$C$12))>1 возвращает ИСТИНА или ЛОЖЬ.

Функция СУММПРОИЗВ вычисляет произведение соответствующих значений, а затем возвращает сумму каждого умножения.

Получить файл Excel *.xls

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

адам спрашивает: Привет, у меня возникла ситуация, когда я хочу подсчитать, является ли это значение дубликатом, и если оно […]

Вопрос. Как выделить даты, соответствующие критериям, с помощью условного форматирования? Содержание Выделить значения в столбце […]

На изображении выше показаны строки, выделенные в зависимости от значения в столбце C, являющегося наибольшим или наименьшим в данном конкретном […]

В этой статье демонстрируется код события в сочетании с условным форматированием, которое выделяет перекрывающиеся диапазоны дат на основе выбранного диапазона дат. […]

Цвет нечетных месяцев. Формула условного форматирования: =MOD(МЕСЯЦ($B6),2) Объяснение формулы CF в ячейке B6. Шаг 1. Подсчет количества месяцев. […]

Следующая формула условного форматирования выделяет даты в зависимости от дня недели. =ТЕКСТ(B3,"DDDD")=$D$3 Функция ТЕКСТ преобразует значение […]

Диапазон ячеек B3:B14 имеет условное форматирование, формула проверяет, является ли дата сегодняшней. Формула условного форматирования: =СЕГОДНЯ()=B3 A […]

На изображении выше показано условное форматирование, примененное к диапазону ячеек C8:C20, оно выделяет ячейки, содержащие даты, выпадающие на субботу […]

31 ответ на вопрос «Выделение перекрывающихся диапазонов дат с помощью условного форматирования»

Здравствуйте, у меня возникла ситуация, когда я хочу подсчитать, повторяется ли это значение и перекрываются ли даты. Можно ли как-то решить эту проблему?

например,
Серийный номер | Дата начала | Дата окончания
ABC | 01.01.2009 | 31.12.2009
БКД | 01.06.2009 | 31.12.2009
Азбука | 01.07.2009 | 30.06.2010

Поэтому он должен отображать первую и третью запись.

[. ] Выделите повторяющиеся значения и перекрывающиеся даты в Excel Зарегистрировано в Excel 09 октября 2010 г. Отправить эту статью другу по электронной почте adam asks: [. ]

Я хочу сделать что-то подобное, но только если значение в столбце B такое же, как в предыдущей или последующей строке. Другими словами, столбец B может быть чем-то вроде номера сотрудника (в данном случае 5), и я хочу искать только перекрывающиеся даты для одного и того же номера сотрудника, как здесь.

5 11.02.1995 22.10.2002
5 22.10.2002 31.12.9999

Вышеупомянутое перекрывается, как и нижнее

5 11.02.1995 22.10.2002
5 19.10.2002 31.12.9999

Могу ли я сделать это в Excel при условии, что ключ номера сотрудника (в данном случае 5) должен совпадать?

Формула условного форматирования:

У меня есть проблема в том, как вычислить общий интервал даты и времени. И я хотел бы, чтобы вывод был таким: 1 (для дня) 02:00 (для часов). Пожалуйста, помогите.

Пример.
А-начало Б. Конец В. Выход
23.01.2012 13:00 24.01.2012 15:00 1 14:00

Я пробую формулу, но появляется какая-то ошибка.
"Введенная вами формула содержит ошибку"
Пожалуйста, Оскар, помогите мне.

Я пробую формулу, но появляется какая-то ошибка.
«Формула, которую вы набрали, содержит ошибку» — это ошибка, появившаяся в моем листе.
Пожалуйста, помогите

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

Большое спасибо.

Здравствуйте, Оскар,
большое спасибо за вашу формулу, она мне очень помогла.
Я работаю в небольшой компании по аренде автомобилей, и у меня возникла еще одна проблема с применением вашего кода.
я постараюсь объяснить как можно понятнее, не раскрывая весь файл.

У меня есть таблица, которая сворачивает столбцы.
например.
Номерной знак транспортного средства - Водитель - Дата выезда - Дата прибытия - Пункт назначения.
Эти заголовки сгруппированы для облегчения поиска автомобиля.
эти столбцы повторяются 25 раз.
пока все хорошо,
у меня есть 4 входа для каждой машины, так что это будет выглядеть так:
------------(A)--| -(В)-|(С)|(Г)|
(1)-------ЛИЦЕНЗИЯ|ЧАУФ|ВЫХОД| В|
(2)ВХОД 1
(3)ВХОД 2
(4)ВХОД 3
(5)ВХОД 4

теперь он корректно отмечает совпадение дат для одного и того же автомобиля,
(" =SOMPRODUCT(($G3=$G$3:$G$6))>1 "), так как нужно только искать в небольшой кластер.
но теперь у меня есть следующая проблема, у нас много личных водителей.. (файл из 35 водителей на 25 транспортных средств)
которые должны быть проверены в разных (разделенных) столбцах.
Я предотвратил планирование одного и того же автомобиля дважды в один и тот же период времени.
Но теперь мне нужно, чтобы он искал, если шофер не планируется дважды! (не зависит от автомобиля!)

ЕГ.
Лицензия указана в столбцах A, E и I.
Шофер указан в столбце B, F и J.
Дата окончания указана в столбце C, G и K.
Дата указана в столбце D, H и L.

Мой инстинкт подсказывает мне следующее:
=СУММПРОИЗВ(($C2=$C$2:$C$5+$G$2:$G$5+$K$2:$K$5)*($B2= $B$2:$B$5+$F$2:$F$5+$J$2:$J$5))>1

не могли бы вы исправить мой код?

Пример с восемью столбцами:

Формула условного форматирования в диапазоне ячеек A6:D12:

Формула условного форматирования в диапазоне ячеек G6:J12:

Было бы намного проще, если бы все записи располагались в 4 столбцах.

Большое спасибо, Оскар, эта формула работает,
я использую ее в диапазоне нескольких сотен, и она дает отличный результат. Я немного подкорректировал свои требования

Я использовал формулу, как показано ниже

Я рад, что вам понравилось.

Отличный сайт! Продолжайте в том же духе.

У меня есть вопрос, как расширить это до следующего шага. В вашем первоначальном примере мы видим, что Джефф в строке 1 и Шон в строке 4 перекрывают свой рабочий график. Я хотел бы знать, есть ли способ, скажем, в диапазоне E6: E12, отображать номер Джеффа Роу рядом с именем Шона и наоборот. Я вижу, как это легко отобразит, какие номера записей перекрываются, когда в диапазоне есть только одно перекрытие. Можем ли мы различать записи, когда есть два перекрытия? Предположим, что Теодор в строке 6 также пересекается с Томасом в строке 2.

Теперь данные становятся запутанными, потому что нам нужно определить по 4 серым строкам, кто с кем пересекается.

Может ли формула вернуть значение строки «совпадающей» перекрывающейся записи?
В ячейке E6 Джеффа будет указано «Строка 4» в отношении совпадающей записи и наоборот для Шона. В ячейке E9 Шона будет указана «Строка 1» как совпадающая запись.
И в то же время ячейка E11 Теодора будет отображать «Ряд 2» для записи Томаса, а ячейка E7 Томаса будет отображать «Ряд 6» для Теодора.

Я даже не коснулся более сложного, например того, что происходит, когда у Джона в ряду 7 есть дата окончания 07.01.2010, таким образом, пересекаясь с Джеффом и Шоном!

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

Выделить пересекающиеся даты

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

<р>1. Выберите ячейки, содержащие даты начала и окончания, и нажмите Главная > Условное форматирование > Новое правило. Смотрите скриншот:

<р>2. Затем в диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения ячеек для форматирования» в списке «Выбор типа правила» введите эту формулу = СУММПРОИЗВ (($ I2 = $ I $ 2: $ I $ 7))> 1 в поле «Формат». значения, где эта формула является истинным текстовым полем, и нажмите Формат. Затем в диалоговом окне на вкладке «Заливка» выберите один цвет, который вы хотите использовать для выделения. Смотрите скриншот:

В формуле I2 – это первая дата начала, J2 – первая дата окончания, I2:I7 и J2:J7 – столбцы дат начала и даты окончания.

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

Если вы хотите выделить дубликаты и перекрывающиеся даты, как показано на снимке экрана ниже, вы можете выполнить следующие шаги:

<р>1. Выберите диапазон данных, включая даты начала, окончания и столбец программы с дубликатами, и нажмите Главная > Условное форматирование > Новое правило.

<р>2. Затем в диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения ячеек для форматирования» в списке «Выбор типа правила» и введите эту формулу =СУММПРОИЗВ(($B2 =$B$2:$B$7)*($A2= $A$2:$A$7))>1 в текстовое поле «Значения формата», где эта формула является истинной, и нажмите «Формат». Затем в диалоговом окне «Формат ячеек» включите вкладку «Заливка» и выберите один цвет, который вы хотите использовать для выделения. Смотрите скриншот:

В формуле B2 – это первая дата начала, C2 – первая дата окончания, B2:B7 и C2:C7 – столбец дат начала и окончания, A2 – первая ячейка в столбце, который нужно найти. дубликатов, A2:A7 содержит дубликаты.

В этом посте рассказывается, как найти перекрывающиеся диапазоны дат в Excel, а затем выделить перекрывающиеся даты. Как выделить перекрывающиеся даты с помощью функции условного форматирования в Excel.

Выделить пересекающиеся даты

Если вы хотите выделить перекрывающиеся даты, вам нужно сначала найти перекрывающиеся диапазоны дат, поэтому вам нужно создать формулу на основе функции СУММПРОИЗВ, а затем ввести эту формулу в поле условного форматирования. Просто выполните следующие действия:

Если вы хотите выделить повторяющиеся значения и перекрывающиеся даты в Excel, вам просто нужно повторить описанные выше шаги для достижения результата. И единственное отличие состоит в том, что вам нужно использовать следующую формулу на шаге 3:

Связанные функции


    Функция СУММПРОИЗВ Excel умножает соответствующие компоненты в заданном одном или нескольких массивах или диапазонах и возвращает сумму этих произведений. Синтаксис функции СУММПРОИЗВ следующий: = СУММПРОИЗВ (массив1,[массив2],…) …

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

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

Сегодня мы покажем вам, как использовать СУММПРОИЗВ и ТОЧНОЕ для выполнения точного совпадения с учетом регистра. В этой статье мы приводим простой пример расчета премии для сотрудников, имена которых чувствительны к регистру. Если вы встретите подобные сценарии .

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

Если мы хотим суммировать числа из диапазона с критериями, мы часто выбираем функции СУММЕСЛИ или СУММЕСЛИМН в качестве первого выбора для создания формулы. Критерии могут быть числом, массивом или набором некоторых .

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

Иногда нам может понадобиться просуммировать N самых больших чисел или первых N чисел в диапазоне. В этой статье мы покажем вам метод «СУММ самых больших N чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ.

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

Иногда нам может понадобиться суммировать первые наименьшие N чисел в диапазоне в Excel. В этой статье мы покажем вам метод «СУММ наименьших N чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ и .

Иногда мы можем встретить случай, когда нужно суммировать числа на основе одного определенного критерия. В этой статье мы покажем вам способ решения этой проблемы по формуле с помощью функции СУММПРОИЗВ Excel. СУММПРОИЗВ может фильтровать .

Читайте также: