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

Как быстро перенести список адресов из строк в столбцы в Excel?

Сравнение двух таблиц в Excel на совпадение значений в столбцах

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

Сравнение двух столбцов на совпадения в Excel

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

Рабочий лист с таблицами.

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

  1. Выберите инструмент «ФОРМУЛА — Собственные имена»-«Присвоить имя».
  2. В появившемся окне введите значение Table_1 в поле «Name:».
  3. Щелкните левой кнопкой мыши в поле ввода «Диапазон:» и выберите диапазон: A2:A15, а затем нажмите кнопку OK.

Проделайте то же самое со вторым списком, но назовите его Table_2. Укажите диапазон C2:C15.

Таблица_2.

Совет: Имена диапазонов можно назначать быстрее, используя поле имени. Оно расположено в левой части панели формул. В поле имени введите подходящее имя для диапазона и нажмите Enter.

поле имен.

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

Эти элементы будут отображаться зеленым цветом, если их нет в Таблице_2. Кроме того, элементы, которые есть в Таблице_2, но отсутствуют в Таблице_1, будут выделены синим цветом.

  1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:». Создать правило.
  2. В поле ввода введите формулу: введите формулу.
  3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК. Заливка.
  4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:». Использовать формулу.
  5. В поле ввода введите формулу: СЧЕТЕСЛИ.
  6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

Принцип сравнения данных двух столбцов в Excel

При определении условий форматирования ячеек в столбце мы использовали функцию CALCULATE. В данном примере эта функция проверяет, сколько раз значение второго аргумента (например, A2) встречается в списке первого аргумента (например, Table_2). Если количество раз = 0, формула возвращает TRUE. В этом случае ячейке присваивается пользовательское форматирование, указанное в параметрах условного форматирования.

Этот второй аргумент делает ссылку на относительный диапазон, что означает, что каждая ячейка в диапазоне (например, Для A2-A15 будут выполняться одна за другой проверки. Например, для сравнения двух прайс-листов в Excel, даже на разных листах. Вторая формула работает аналогичным образом. По такому же принципу можно выполнять различные аналогичные задачи.

Объединение 3-х и более списков в EXCEL

Представим себе, что имеется пять списков разной длины (см. пример файла ).

Задача

Давайте объединим все пять списков значений в один. В этой же статье решается задача объединения двух списков.

Читайте так же:
Как быстро сохранить книгу в нескольких местах в Excel?

Решение1 (Простое)

Объединенный список будет составлен на основе функции mix()

=ЗАВЕРШЕНИЕ(заголовок первого списка;номер списка;номер списка;номер списка-1)

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

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

  • Выражение STRIKE()-STRIKE($H$11) генерирует последовательность 1; 2; 3; 4.
  • -функция GPR() является горизонтальным аналогом VPR() и выбирает номер исходного списка на основе позиции первого элемента каждого списка в связном.

Номер списка основан на смещении столбцов в формуле, основанной на HUMMESH()

Все подробности приведены в файле примера на листе Пример2.

Решение2 (сложное, с формулами массива и именованными формулами)

Создайте именованный диапазон, содержащий все значения из списков. Для этого:

  • Выберите диапазон A2:E10 ;
  • На вкладке Формулы в группе Выделенные имена выберите Назначить имя в группе Выделенные имена ;
  • В поле Имя введите Range_list ;
  • Убедитесь, что поле Range содержит формулу =example!$A$2:$E$10
  • Нажмите ОК.

Функция INDEX() используется для вывода всех значений из 5 списков в один столбец. На основании номера столбца и номера строки эта функция последовательно выводит значения из всех ячеек диапазона Range_Lists. Остается только определить адреса непустых ячеек.

Массивы (номер столбца, номер строки) создаются для всех ячеек диапазона. Для этого применим хитрость: парные значения будут храниться как обычные числа, но сформируем их по определенному правилу: правая часть числа будет содержать номер строки (для этого отводится две цифры, т.е. максимальная длина списка может быть 99), а левая часть числа будет содержать номер столбца. В качестве примера, 512 будет означать: 5-й столбец, 12-я строка. В зависимости от ваших потребностей, вы можете увеличить разрядность (формула в файле примера позволяет столбцам иметь до 9999 строк) для хранения номеров строк.

Технически это можно сделать следующим образом. Сначала определите номера столбца и строки левого верхнего угла нашего диапазона LIST_range. Затем нужно назвать формулы =MIN(column(LIST_range)) и =MIN(ROW(LIST_range)).

Создайте еще одну именованную формулу Адреса: =ЕСЛИ(ЕПУСТО(Диапазон_Списков);»»; —((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);»0″;»»)&СТРОКА(Диапазон_Списков)-Мин_Строка+1))

Эта формула вернет массив адресов из нашего диапазона <101;201;301;401;501: 102;202;302;"";502: 103;203;303;"";503: 10…>. Вместо адресов пустых ячеек в массиве содержатся значения Пустой текст («»). Номера столбцов и строк отсчитываются от левой верхней ячейки Диапазона_список .

Заключительный этап. Формируем объединенный список. Запишем в ячейке следующую формулу: =ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков; —ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));2);—ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)); ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-2));»»)

Функция ЧИСЛО() должна последовательно извлечь все числа, содержащие адреса ячеек. RIGHTSIMB() извлечет из этих чисел номер строки, а LEFTSIMB() — номер столбца. Обе эти функции возвращают текстовые значения, поэтому для преобразования текста в числа необходимо применить двойное отрицание (—) (см. Преобразование чисел из текстового формата в числовой (часть 1) ).

Сделав это, вы сможете объединить нужное количество столбцов, каждый из которых не длиннее 99 строк.

Чтобы объединить столбцы, длина каждого из которых не превышает 9999 строк, используется формула

Также необходимо внести изменение в формулу для названного адреса:

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

Примечание : при объединении большого количества столбцов с количеством строк >100, расчет формулы может притормаживать.

Как преобразовать столбец текста URLs в активные гиперссылки в Excel?

Урлы сайтов хранятся в столбце в excel. Я хочу превратить значения URL в активные ссылки. В этом столбце около 200 записей с разными URL в каждой ячейке. Есть ли способ создать активные гиперссылки на все ячейки без написания макроса?

22 ответа

  • Как реализовать DocumentFilter, который вставляет URL в качестве гиперссылок в JTextPane?

Есть JTable, где JTextPane используется как редактор и рендерер. Я добавил keyListener в редактор, который прослушивает символ пробела и проверяет, является ли последнее слово URL, и если да, то добавляет его в редактор как гиперссылку, используя этот атрибут.

Я предоставляю данные с сервера SSAS, к которому пользователи могут подключиться с помощью выбранного ими инструмента (на практике обычно это Excel 2007). Мы хотим предоставить URL-адреса в некоторых ячейках и хотим, чтобы Excel распознал их как гиперссылки и сделал их активными и отформатированными.

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

Заполните новый столбец формулой=HYPERLINK(A1) (заменив A1 на ячейку, на которую вы хотите сделать ссылку). Затем скопируйте формулу в оставшиеся 200 записей.

N OTE: Это решение не работает, если ячейка A1 содержит строку длиной более 255 символов. Вы получите ошибку #VALUE!

Создайте макрос, подобный этому:

В меню Сервис в Microsoft Excel выберите команду Макросы, а затем команду Редактор Visual Basic. В меню Вставка выберите Модуль. Скопируйте и вставьте этот код в окно кода модуля. Он автоматически присвоит себе имя HyperAdd.

После завершения вставки макроса нажмите Закрыть и вернитесь в Microsoft Excel.

Затем нужно выделить необходимые ячейки, нажать кнопку Макрос, а затем нажать кнопку Выполнить.

ПРИМЕЧАНИЕ: Не выделяйте весь столбец! Выберите ТОЛЬКО те ячейки, которые вы хотите превратить в кликабельные ссылки, иначе вы окажетесь в бесконечном цикле и будете вынуждены перезапустить Excel. Готово!

Вот как я это обнаружил. Я работаю на Mac, используя Excel 2011. Если в столбце B есть текстовые значения, на которые вы хотите сделать гиперссылку, поместите эту формулу в ячейку C1 (или D1, или что-то еще, если это свободный столбец): =HYPERLINK(B1,B1) Это вставит гиперссылку с местоположением в качестве текста ссылки и «дружественным именем» в качестве текста ссылки. Кроме того, вы можете использовать еще один столбец с четкими именами для каждой ссылки. Вы можете скрыть текстовый столбец, если не хотите его видеть.

Если у вас есть список IDs чего-то, и все URL-адреса были http://website.com/folder/ID , например:

вы можете использовать что-то вроде =HYPERLINK(«http://website.com/folder/»&A1,A1) , и вам не понадобится список URL-адресов. Такова была моя ситуация, и она прекрасно сработала.

Согласно этому сообщению: http://excelhints.com/2007/06/12/hyperlink-formula-in-excel/ этот метод будет работать и в Excel 2007.

Это хулиганское решение, но я просто не могу понять, как заставить Excel оценить столбец URLs как гиперссылки в большом количестве.

  1. Создайте формулу, =»=hyperlink(«»» & A1 & «»»)»
  2. Тяните вниз
  3. Скопировать новый столбец формулы
  4. Вставьте специальные значения-только поверх исходного столбца
  5. Выделите столбец, нажмите Ctrl — H (для замены), найдите и замените = на = (каким-то образом вызывает переоценку ячеек).
  6. Теперь ячейки должны быть доступны для кликабельности в виде гиперссылок. Если вам нужен стиль синий/подчеркивание, просто выделите все ячейки и выберите стиль гиперссылки.
Читайте так же:
Как в Excel убрать лишние пробелы между словами в ячейках?

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

  • При экспорте в Excel гиперссылки не нажимаются

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

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

Сравнительно простой метод для достаточно коротких списков:

  1. Дважды щелкните по полю, в котором находится url
  2. Войти в систему

У тебя есть ссылка 😉

Если нет возможности добавить дополнительный столбец гиперссылок, альтернативой является использование внешнего редактора для включения гиперссылки в =hyperlink(» и «) для получения =hyperlink(«originalCellContent»).

С помощью Notepad++ вы можете делать это полуавтоматически:

  • Скопируйте колонку адресов в Notepad++
  • Удерживая клавишу ALT-ShIFT, переместите курсор из верхнего левого угла в нижний левый угол и введите =hyperlink(» . Это добавляет =hyperlink(» в начало каждой записи.
  • Откройте меню «Заменить» ( Ctrl — H ), включите регулярные выражения ( ALT — G ) и замените $ (конец строки) на «)» . Это добавляет в конец каждой строки закрытую перевернутую запятую и закрытую круглую скобку (которая должна быть экранирована при включении регулярных выражений).
  • Вставьте данные обратно в Excel. На практике просто скопируйте данные и выберите первую ячейку в столбце, в котором вы хотите, чтобы появились данные.

Использование гиперссылок помогает мне:

Здесь B10 содержит текстовую версию URL (в данном примере).

Я использовал Excel 2007 на Windows и нашел эти шаги простыми;

  1. Выберите ячейки с неактивными URL-адресами
  2. Копировать
  3. Вставить как гиперссылку.

Я удивлен, что Excel не делает этого автоматически, поэтому вот мое решение, которое, я надеюсь, будет полезно другим,

  1. Скопируйте весь столбец в буфер обмена
  2. Откройте его в Chrome или Firefox

data:text/html,

Вместо второго шага можно использовать страницу ниже, сначала нажмите «Выполнить фрагмент кода» и вставьте в столбец

Я просто скопировал весь столбец URL-адресов в текстовом формате во внешнее приложение (например, Evernote) и вставил их туда; они стали ссылками, и я затем скопировал их обратно в Excel.

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

  1. Создайте временный новый столбец гиперссылок с помощью формулы =HYPERLINK()
  2. Скопируйте этот столбец в Microsoft Word (копируйте его в буфер обмена только после запуска Word).
  3. Скопируйте все в новый документ Word (ctrl+a, затем ctrl+c).
  4. Вставьте в Excel, заменив исходный текстовый столбец. Удалите временный столбец с формулой.

Замените A1 текстом ячейки адреса электронной почты.

Я обнаружил, что ни один из методов здесь не работает, если гиперссылка не включает http://, поскольку они связаны с локальными местоположениями.

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

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

Формулу =HYPERLINK(,) можно вставить в соседнюю ячейку и перетащить в самый низ. Таким образом, вы получите все ссылки в одном столбце. Если вы нажмете на заголовок и щелкните правой кнопкой мыши, вы можете выбрать скрытие исходного столбца.

Если вы скопируете содержимое текста в новую колонку и используете

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

Это единственный способ, который сработал для меня в Excel 2010 под управлением Windows 7. Вы можете скопировать формулу.

Поместите URL-адреса в таблицу HTML, загрузите страницу HTML в браузере, скопируйте содержимое страницы и вставьте его в Excel. На этом этапе URL-адреса сохраняются как активные ссылки.

Я проверил, что оно работает.

У меня были следующие URL:

Я поместил их в файл HTML (links.html) следующим образом:

Используя links.html, я скопировал, вставил в Excel, и ссылки сработали.

Кассиопея, спасибо за код. Чтобы заставить код работать с локальными адресами, я внес небольшие изменения в его условия. Я удалил следующие условия:

  1. Изменено http:/ на file:///
  2. Удалены все типы условий интервалов
  3. Изменено условие диапазона ячеек 10k на 100k.

Это список номеров, входящих в url, на которые я хочу сделать горячую ссылку. Например, у меня есть столбец A с номерами вопросов (например, я хочу превратить эти номера вопросов в горячие ссылки и отобразить только номера вопросов (2595692, 135171).

Поэтому я создал гиперссылку, содержащую только текст, указывающий на столбец A, и скопировал ее на все номера вопросов:

Затем я копирую-вставляю значение этой колонки с текстовыми гиперссылками в другую колонку.

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

Затем я выделил эти вставленные элементы и запустил макрос F2Entry, описанный ниже:

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

Затем я удалил столбец для ввода текста и столбец А.

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

В Excel 2016 можно выделить столбец, а затем перейти на вкладку Гиперссылка, расположенную в разделе Стили на главной ленте.

enter image description here

Редактировать: К сожалению, он обновляет только стиль ячейки, а не функцию.

Самый простой способ — здесь

  • Выделите весь столбец
  • Нажмите кнопку «Вставить»
  • Нажмите кнопку «Гиперссылка».
  • Нажмите кнопку «поместить в этот документ»
  • Нажмите кнопку «поместить в этот документ»
  • Нажмите OK

Самый простой способ сделать это в Excel 2010: выделите текстовый столбец URL, затем на вкладке Главная выберите Стиль гиперссылки. Все URL-адреса в колонке теперь являются гиперссылками.

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

Существует очень простой способ сделать это. Создайте гиперссылку, а затем с помощью Formatting Artist скопируйте форматирование. Гиперссылки будут созданы для каждого элемента.

Похожие вопросы:

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

Один документ Excel содержит сотни ячеек с гиперссылками по формуле hyperlink =HYPERLINK( , ). Из них мне нужны обычные текстовые URL-адреса. Большинство.

Можно ли создать кликабельные ссылки при сохранении таблиц Excel в формате PDF с помощью формулы гиперссылки? Вот электронная таблица, на которую я сделал ссылку.

У меня есть JTable, которая использует JTextPane в качестве редактора и рендеринга. Я добавил в редактор keyListener, который прослушивает символ пробела и проверяет, является ли последнее слово URL.

На моем сервере SSAS размещены данные, к которым пользователи могут получить доступ из любого инструмента по своему выбору (на практике это обычно Excel 2007). Пожалуйста, предоставьте URL-адреса.

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

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

Может ли Excel интерпретировать URL-адреса в моем CSV как гиперссылки? Если да, то как?

Я использую Excel 2010, и у меня есть, например, столбец (a), содержащий около 500 чисел, все разные. Числа в каждой ячейке немного отличаются. Я хочу превратить их в гиперссылки (это.

Чтобы поделиться файлом Excel с коллегами, мне нужно убедиться, что он защищен. Текст URL-адреса включен в столбец ссылок; однако доступ к нему возможен только при двойном щелчке.

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