Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

6 способов создать список уникальных значений в Excel

Unikalnie znachenie 1 6 способов создать список уникальных значений в Excel Здравствуй уважаемый пользователь!

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

Список уникальных значений может быть создан 6 разными способами:

  1. Со специальной функцией;
  2. С расширенным фильтром;
  3. С формулами;
  4. Со сводными таблицами;
  5. С условным форматированием;
  6. С возможностями макроса.

Создать список уникальных значений с помощью специальной функции

Для пользователей Excel выше версии 2007 это простой способ отбора уникальных значений. В разделе «Работа с данными» необходимо воспользоваться командой «Удалить дубликаты» на вкладке «Данные».

Unikalnie znachenie 2 6 способов создать список уникальных значений в Excel

В появившемся диалоговом окне «Удалить дубликаты» выберите столбцы, в которых вы хотите удалить единичные значения, и нажмите «OK». Если выбранный диапазон включает заголовок таблицы, установите флажок «Мои данные содержат заголовки», чтобы избежать случайного удаления данных. Внимание: При удалении отдельных значений в таблице, где столбцов больше 2 и они связаны информацией, Excel попросит вас расширить диапазон выделения, что необходимо сделать, иначе логическая связь с другими столбцами будет нарушена.

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

Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте. Unikalnie znachenie 3 6 способов создать список уникальных значений в Excel После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». Unikalnie znachenie 4 6 способов создать список уникальных значений в Excel Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.

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

Создать список уникальных значений с помощью формул

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

Пример 1. Для нумерации и обеспечения уникальности значений в списке значений необходимо использовать функцию ЕСЛИ в формуле следующим образом:

Читайте так же:
Как перенести столбцы в строки и вставить новые строки для заполнения данных в Excel?

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)

Unikalnie znachenie 5 6 способов создать список уникальных значений в Excel

Суть формулы в том, что она проверяет, сколько раз текущее значение встречается в ее диапазоне (начиная с начала), и если это значение равно 1, т.е. если это первое уникальное значение, формула размещает возвращаемый номер в последовательном порядке. Теперь вы можете удалить уникальные значения, которые были ранее пронумерованы. Это можно сделать в любом соседнем столбце, используя функцию FFT и копируя ее вниз:

=ЕСЛИ(МАКС(A1:A100)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1); A1:B100;2))

Unikalnie znachenie 6 6 способов создать список уникальных значений в Excel

Эта формула перебирает весь диапазон сверху вниз по столбику с номерами и все позиции значений с номерами переносит в, другую таблицу. Пример 2. Вам нужно создать уникальный список значений, при этом, исходные, значение не изменяются, а если изменять начальные данные, то и конечные данные будут изменены. Допустим у вас список данных в диапазоне А1:А9, где А1— это заголовок. А вот полученные данные будут выводиться в столбик С, а формула для этого будет такая:

Как вы можете видеть, была использована разметка формулы массива; фигурные скобки указывают на это, и эта форма формулы может быть введена с помощью горячих клавиш Ctrl+Shift+Enter. Эта ошибка указывает на то, что уникальные значения закончились после извлечения формулы. Если вы хотите избежать этой ошибки, вы можете изменить и улучшить формулу так, чтобы она выглядела следующим образом:

Unikalnie znachenie 7 6 способов создать список уникальных значений в Excel

В этом случае, если вы получаете ошибку #NUMBER, система размещает пустые ячейки.

Создать список уникальных значений с помощью сводных таблиц

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

  • Необходимо выделить один или несколько столбцов таблицы и выбрать вкладку «Вставка», группу «Таблица», значок «Перекрестная таблица»;
  • В появившемся диалоговом окне «Создание перекрестной таблицы» дважды проверьте правильность выбранного диапазона значений или определите другой источник данных;
  • На следующем этапе укажите, где разместить перекрестную таблицу, есть два варианта: на новом листе или на существующем листе.
  • Последний шаг — подтвердить создание поворотной таблицы, нажав на кнопку «OK».

Unikalnie znachenie 8 6 способов создать список уникальных значений в Excel

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

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

Я также не собираюсь классифицировать этот метод как серьезный или сложный. Чтобы использовать условное форматирование для выбора отдельных значений, перейдите на вкладку Главная, затем в группе Стили выберите кнопку меню Условное форматирование. Из списка нам нужна опция Create Rule (Создать правило). В открывшемся диалоговом окне «Создание правила форматирования» активируем опцию «Использовать формулу для определения форматируемых ячеек» и вводим нашу условную формулу в поле «Редактировать описание правила»:

=ПОИСКПОЗ(D2;$D$2:$D$9;0)=СТРОКА(D2) -СТРОКА($D$1)

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

Unikalnie znachenie 9 6 способов создать список уникальных значений в Excel

После выполнения условий возвращаются нужные вам значения.

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

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

Читайте так же:
Как подсчитать количество ячеек больше или меньше 0 (нуля) в Excel?

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

Не забывайте благодарить автора.

По мере роста благосостояния растут и заботы. Гораций

Создание связи между двумя таблицами в Excel

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

Все таблицы в книге указаны в списках полей в сводной таблице и Power View.

Браузер не поддерживает видео.

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

В книге должно быть две таблицы, и каждая таблица должна содержать столбец, который может быть сопоставлен с другим столбцом другой таблицы.

Вы можете оформить данные в виде таблицы или импортировать внешние данные в виде таблицы в новую таблицу.

Назовите каждую из таблиц четко: на вкладке Работа с таблицами нажмите Конструктор > Имя таблицы и введите имя.

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

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

Щелкните Данные> Отношения.

Если команда Relationship недоступна, то книга содержит только одну таблицу.

Чтобы создать новую связь, нажмите New (Новая) в окне Manage Relationships (Управление связями).

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

Для элемента Column (foreign) выберите столбец, который содержит данные, связанные с элементом Related Column (primary key). Например, если у вас есть столбец даты в обеих таблицах, вы можете выбрать этот столбец сейчас.

Читайте так же:
Как получить текущий часовой пояс и отобразить его в ячейке Excel?

В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, который связан с таблицей, выделенной в поле Таблица.

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

Нажмите кнопку ОК.

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

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

Создание ссылок похоже на использование VLOOKUP: вам нужны столбцы с совпадающими данными, чтобы Excel мог связать строки из одной таблицы со строками из другой таблицы. В примере со временем таблица «Клиент» требует значений данных, которые также находятся в таблице анализа времени.

Табличные связи в модели данных могут быть один к одному (у каждого пассажира один и тот же посадочный талон) или один ко многим (у каждого рейса много пассажиров), но не многие ко многим. Связь с зависимостями «многие ко многим» может вызвать ошибку «Обнаружена циклическая зависимость». Эта ошибка может возникнуть, если вы создаете прямую связь между двумя таблицами с отношением «многие ко многим» или если вы создаете косвенные связи (цепочка связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней таблицей формируется отношение «многие ко многим»). Для получения дополнительной информации см. раздел «Связывание таблиц в модели данных».

Для того чтобы использовать данные в обоих столбцах, они должны быть совместимы. Более подробная информация содержится в разделе «Типы данных в моделях данных».

Другие способы создания ссылок могут быть более интуитивными, особенно если вы не знаете, какие столбцы использовать. Узнайте больше о создании ссылки в представлении диаграммы в Power Pivot.

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

Бесплатные данные в Microsoft Azure Marketplace помогут вам изучить взаимосвязь таблиц и логику операций с течением времени. Многие из этих наборов данных очень большие, поэтому для их загрузки за разумное время необходимо быстрое подключение к Интернету.

Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace. Мастер импорта таблиц откроет главную страницу Microsoft Azure Marketplace.

В разделе Цена нажмите на Бесплатно.

В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).

Найдите поток данных и нажмите кнопку Save (Сохранить).

Введите свои учетные данные Microsoft и нажмите Вход. Откроется предварительный просмотр данных.

Прокрутите страницу вниз и нажмите Выбрать запрос.

Нажмите на следующую кнопку.

Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром интернет-соединении импорт займет около минуты. Как только это будет сделано, вы увидите отчет о состоянии — 73 414 перемещенных строк. Нажмите кнопку Закрыть.

Читайте так же:
Как посчитать количество ячеек с текстом или числом в Excel?

Чтобы импортировать второй набор данных, нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace.

В разделе Тип нажмите пункт Данные).

В разделе Цены нажмите на пункт Бесплатно.

Найдите задержки рейсов американских авиакомпаний и нажмите Select.

Прокрутите вниз и нажмите кнопку Выбрать запрос.

Нажмите кнопку Next (Далее).

Чтобы импортировать данные, нажмите Готово. Импорт займет около 15 минут при быстром подключении к Интернету. После завершения вы увидите отчет о состоянии перемещенных 2 427 284 строк. Нажмите Закрыть. Теперь модель данных состоит из двух таблиц. Вам нужны совместимые столбцы в обеих таблицах, чтобы связать их.

Убедитесь, что значения в столбце DateKey таблицы BasicCalendarUS имеют формат 01.01.2012 00:00:00. Таблица On_Time_Performance также имеет столбец даты и времени FlightDate со значениями в том же формате: 01.01.2012 00:00:00. Эти два столбца содержат похожие данные одного типа, и по крайней мере один столбец (DateKey) содержит уникальные значения. В следующих шагах вы будете использовать эти столбцы для связи таблиц.

В окне Power Pivot нажмите Перекрестные таблицы, чтобы создать перекрестную таблицу на новом или существующем листе.

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

Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить ее в строку.

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

В разделе «Возможно, вам понадобятся связи между таблицами» нажмите New.

В поле «Связанная таблица» выберите On_Time_Performance, а в качестве «Связанного столбца (первичный ключ)» выберите flightdate.

Введите BasicCalendarUS в поле Table и DateKey в поле Column (Alien). Нажмите OK, чтобы создать связь.

В настоящее время время время задержки для каждого месяца разное.

В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.

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

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

В окне Power Pivot должна быть открыта таблица BasicCalendarUS.

В основной таблице нажмите кнопку Сортировка по столбцам.

Вы можете отсортировать по MonthInCalendar, выбрав поле Sort.

В поле «По» выберите MonthOfYear.

Теперь каждая комбинация «месяц» и «год» (октябрь 2011, ноябрь 2011) сортируется по номеру месяца года (10, 11). Благодаря тому, что канал DateStream предоставляет все столбцы, необходимые для работы этого сценария, изменить порядок сортировки несложно. Другая таблица временной логики повлияет на ваши действия.

«Могут потребоваться связи между таблицами»

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

Кнопка "Создать", отображаемая при необходимости создать связь

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

Читайте так же:
Как показать только первые n-е символы в строке ячейки?

Шаг 1. Определите, какие таблицы указать в связи

Если ваша модель содержит всего несколько таблиц, то понятно, какие из них использовать. Однако для больших моделей вам может понадобиться помощь. Представление диаграммы Power Pivot является одним из средств отображения диаграммы. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С его помощью можно быстро определить, какие таблицы отделены от остальной части модели.

Представление диаграммы, в котором показаны несвязанные таблицы

Примечание: Вы можете создать неоднозначные отношения, которые не будут приняты при использовании в сводной таблице или отчете Power View. Если все ваши таблицы как-то связаны с другими таблицами в модели, но когда вы пытаетесь объединить поля из разных таблиц, вы получаете сообщение «Между таблицами могут потребоваться связи». Наиболее вероятная причина заключается в том, что вы столкнулись с отношением «многие ко многим». Если вы проследите цепочку отношений между таблицами, которые связаны с нужными вам таблицами, вы, вероятно, обнаружите два или более отношений «один-ко-многим» между таблицами. Не существует простого решения, которое работает во всех ситуациях, но вы можете попробовать создать перечислимые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одной таблице.

Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой

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

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector