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

Удаление дубликатов в Excel с помощью таблиц

Удаление дубликатов в Excel с помощью таблиц

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

Как удалить дубликаты в Excel

Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

Устранение дубликатов на основе значений колонки с текстом:

  1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке: Прайс.
  2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис». Удалить дубликаты.
  3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

Альтернативные способы удаления дубликатов

В Excel существуют и другие средства для работы с дублированными значениями. Например:

  1. «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel. Способ 1.
  2. «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа. Способ 2.
  3. «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом. Способ 3.
  4. Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A. Способ 4.
  5. Формула в массиве: 1;0;1);0));»»)’ >. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках <>, а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.

Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

Памятка по работе с Google Таблицами

Примечание. Чтобы улучшить совместимость с быстрыми клавишами Microsoft Excel, вы можете переопределить быстрые клавиши в браузере.

2. Работайте со строками, столбцами и ячейками.

Как добавить строки, столбцы и ячейки

  1. Выделите строку или ячейку, рядом с которой вы хотите добавить элемент.
  2. Нажмите правой кнопкой мыши на выделенные строку, столбец или ячейкуВставитьукажите место.

Как удалить, очистить или скрыть строки и столбцы: нажмите правой кнопкой мыши на номер строки или букву столбцаУдалить, Очистить или Скрыть.

Читайте так же:
HP LaserJet 1160 Drivers Download

Как удалить ячейки: выделите ячейки, нажмите на них правой кнопкой мышиУдалить ячейки Сдвинуть влево или Сдвинуть вверх.

Как переместить строки или столбцы: выделите номер строки или букву столбца и перетащите в новое место.

Как переместить ячейки

  1. Выделите ячейки
  2. Наведите указатель мыши на верхнюю часть выделенной области. Появится значок руки.
  3. Перетащите ячейки в нужное место.

Как сгруппировать строки или столбцы

  1. Выберите строки или столбцы.
  2. Нажмите ДанныеСгруппировать строки или Сгруппировать столбцы.

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

3. Нажмите Общий доступ, чтобы поделиться таблицей, и выберите, какие действия будут доступны соавторам. Указанные пользователи получат уведомление по электронной почте.
4. Работайте с коллегами в режиме реального времени.
5. Создавайте разные версии и копии своей таблицы.

Создать копию: сделайте копию своей таблицы, чтобы создавать шаблоны на ее основе.

Скачать: скачивайте таблицы в разных форматах, например Excel или PDF.

Отправить по электронной почте: отправляйте копии таблиц другим пользователям.

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

Публикация в Интернете: вы можете опубликовать копию таблицы в виде веб-страницы или встроить ее на сайт.

6. Работайте с функциями.

Ваши любимые функции Excel есть и в Таблицах. Вот какие функции вам доступны.

Статистическая: возвращает среднее цифровое значение набора данных, не учитывая текст.

Статистическая: возвращает среднее значение из диапазона, ограниченного несколькими критериями.

Поиск: возвращает объект из списка вариантов на основе индекса.

Статистическая: возвращает количество цифровых значений в наборе данных.

Статистическая: возвращает условное количество в диапазоне.

Дата: преобразует введенные год, месяц и день в дату.

Текст: возвращает первую найденную позицию указанной строки в тексте.

Текст: возвращает агрегированное значение из сводной таблицы, соответствующее указанным заголовкам строки и столбца.

Логическая: возвращает одно значение, если логическое выражение является истинным, и другое, если оно ложное.

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

Математическая: округляет число вниз до ближайшего целого.

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

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

Статистическая: возвращает максимальное значение в числовом наборе данных.

Статистическая: возвращает минимальное значение в числовом наборе данных.

Читайте так же:
Включение блокировки экрана на Android

Дата: возвращает текущую дату и время.

Математическая: округляет число до определенного количества знаков после запятой по стандартным правилам.

Математическая: возвращает сумму чисел и/или значений в ячейках.

Математическая: возвращает условную сумму в диапазоне.

Дата: возвращает текущую дату.

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

Google, Google Workspace, а также другие связанные знаки и логотипы являются товарными знаками компании Google LLC. Все другие названия компаний и продуктов являются товарными знаками соответствующих компаний.

Удаление формул из ячеек таблицы Excel

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

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

Удаление формул

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

Метод 1: копируем значения с помощью параметров вставки

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

  1. Для начала с помощью зажатой левой кнопки мыши нужно выделить область ячеек, которую мы хотим скопировать.Выделенный диапазон ячеек в Эксель
  2. Правой кнопкой мыши щелкаем по любой точке выделенного диапазона и в открывшемся контекстном меню выбираем пункт “Копировать”. Также, можно вместо этого действия можно просто нажать сочетание клавиш Ctrl+C (после того, как выполнено выделение).Копирование диапазона ячеек в Эксель через контекстное менюДля копирования данных можно также воспользоваться кнопкой “Копировать”, которая расположена на лента программы во вкладке “Главная”.Копирование диапазона ячеек в Excel с помощью кнопки на ленте
  3. Переходим в ячейку, начиная с которой мы хотим вставить скопированные данные (эта ячейка станет самой верхней левой точкой диапазона данных, который мы будем вставлять из буфера обмена). Затем правой кнопкой мыши щелкаем по выбранному элементу, в появившемся меню в группе “Параметры вставки” кликаем по варианту “Значения” (значок в виде цифр “123”).Вставка скопированных значений в Эксель

Метод 2: используем специальную вставку

Если требуется скопировать-вставить данные с сохранение первоначального вида (форматирования) ячеек, можно воспользоваться “Специальной вставкой”.

Копирование выделенной области ячеек в Эксель с помощью кнопки на ленте

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

Метод 3: удаляем формулы в исходной таблице

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

  1. Копируем нужный диапазон ячеек любым удобным способом, например, воспользовавшись контекстным меню.Копирование ячеек в Excel через контекстное меню
  2. Как и в ранее рассмотренном методе, вставляем скопированную область в новое место с сохранением исходного форматирования. После этого, не снимая выделение, копируем только что вставленные данные, например, нажав комбинацию клавиш Ctrl+C (или любым другим удобным способом).Вставленная из буфера обмена таблица в Excel
  3. Переходим в самую верхнюю левую ячейку исходной таблицы (или выделяем в ней тот диапазон ячеек, который был скопирован в 1 шаге), кликом правой кнопки мыши вызываем меню, в котором выбираем вставку “Значений”.Вставка скопированных значений в Excel
  4. Теперь, когда содержимое ячеек без формул скопировано в исходное место на листе, удаляем созданные дубликаты. Для этого, выделяем нужный диапазон данных, щелкаем по нему правой кнопкой мыши и в раскрывшемся меню выбираем пункт “Удалить”.Удаление ячеек в Эксель через контекстное меню
  5. Появится окно удаления ячеек. Выбираем то, что нужно удалить. Исходя из нашего примера, ставим отметку напротив пункта “строку”, после чего жмем кнопку OK.Удаление строк в ЭксельТакже, так как справа от выделенного диапазона нет заполненных ячеек, можно выбрать вариант – “ячейки, со сдвигом влево”.Удаление ячеек со сдвигом влево в Excel
  6. Задублированный диапазон данных удален. На этом работа по замене формул на конкретные значения в исходной таблице завершена.Выделенный диапазон ячеек в Эксель

Метод 4: удаляем формулы без копирования в другое место

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

  1. Как обычно, сперва нужно выделить область ячеек, в которых нужно удалить формулы. Затем копируем данные любым удобным способом. Проще всего это сделать, нажав на кнопку “Копировать” на ленте программы (вкладка “Главная”).Копирование выделенной области ячеек в Excel
  2. Теперь, оставляя скопированную область выделенной, щелкаем по ней правой кнопкой мыши и в появившемся меню в группе команд “Параметры вставки” кликаем по варианту “Значения”.Вставка скопированной области ячеек в Excel
  3. В итоге, мы в том же самом месте таблицы в выделенных ячейках вместо формул вставим конкретные значения (результаты вычислений).Вставленный диапазон ячеек в Эксель

Метод 5: применяем макрос

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

  1. Щелкаем по меню “Файл”.Переход в меню Файл в Excel
  2. В открывшемся окне в боковом перечне слева в самом низу выбираем раздел “Параметры”.Переход в Параметры Excel
  3. В параметрах программы переходим в подраздел “Настроить ленту”. В правой части окна ставим галочку напротив пункта “Разработчик”, после чего щелкаем OK.Включение режима Разработчика в Параметрах Excel

Теперь все готово, чтобы выполнить поставленную задачу:

  1. Переключаемся во вкладку “Разработчик”, в которой щелкаем по кнопке “Visual Basic” (группа “Код”).Переход в редактор Visual Basic в Эксель
  2. Выбрав нужный лист книги нажимаем на кнопку “View Code” (или дважды щелкаем левой кнопкой мыши по выбранному листу), чтобы запустить редактор макросов, куда вставляем код ниже, после чего закрываем данное окно.
    Sub Удаление_формул()
    Selection.Value = Selection.Value
    End Sub
    Код для замены формул значениями в редакторе Visual Basic в Excel
  3. Выделяем диапазон ячеек, содержащих формулы, и нажимаем кнопку “Макросы”, которая расположена во вкладке “Разработчик” (блок инструментов “Код”).Применение макросов в Эксель
  4. В открывшемся окне выбора макросов отмечаем нужный и щелкаем по кнопке “Выполнить”.Выбор макроса в Excel
  5. После того, как процедура будет выполнена, все формулы в выбранных ячейках будут заменены результатами расчетов по ним.

Метод 6: удаляем формулу вместе с результатом вычислений

В некоторых ситуациях перед пользователем встает задача – не только удалить формулы в ячейках, но и результаты вычислений по ним. Данная процедура довольно простая:

  1. Начинаем с того, что выделяем область ячеек, содержащих формулы. Кликом правой кнопки мыши по выделенному диапазону вызываем меню, в котором щелкаем по команде “Очистить содержимое”.Очистка содержимого ячеек в Эксель через контекстное менюТакже, вместо этого, после того, как нужные элементы отмечены, можно просто нажать клавишу Del (Delete) на клавиатуре.
  2. В итоге все данные в ячейках, в том числе, формулы будут стерты.Диапазон ячеек в Эксель с удаленными данными

Заключение

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

Excel, как удалять листы, не нарушая ссылки на формулы

Мне нужно удалить листы из книги без разрыва ссылок на формулы; Есть ли способ сделать это с помощью кода или настроек?

8 ответов 8

Мой бесплатный Mappit! Надстройка, доступная по адресу http://www.experts-exchange.com/A_2613.html, имеет в качестве одного из своих выходов карту, на которой показаны отношения листа

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

Скопируйте все формулы. Вставьте как формулы в другую область (например, лист, чтобы сохранить их, которые затем можно будет скрыть). На листе с формулой резервных копий найдите «=», замените на «|» (или любой другой редко используемый символ). Всякий раз, когда вам нужны формулы, вам нужно только вставить их туда, где они должны быть, затем найдите «|» и заменить на «=» (не включать ни одного из «кавычек»).

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

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

Как указано выше, невозможно удалить лист без разрыва ссылки. Однако вот несколько вариантов в зависимости от ваших обстоятельств:

  1. Специальное копирование / вставка: значения, чтобы сохранить результат формулы и удалить лист.
  2. Скройте лист и защитите книгу вместо удаления листа.
  3. Измените ссылки на лист в другой книге.

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

  1. Вырежьте (CTRL+X) ячейку и ее содержимое, на которое ссылается ваша формула с 1-го листа (лист, который будет удален, например: Лист!$ A $ 1)
  2. Вставьте вырезанную ячейку на 2-й лист в новом или том же месте (лист, который будет сохранен, напр .: Вставьте его в Лист2!A1)
  3. Сделайте это для всех ячеек, которые должны быть переданы
  4. Проверьте формулы, чтобы убедиться, что они ссылаются на 2-й лист (Лист2! вместо Sheet1!)
  5. Удалить 1-й лист
  6. При необходимости переименуйте 2-й лист в то же имя, что и 1-й лист, который у вас был

Надеюсь это поможет

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

Я знаю, что опаздываю на вечеринку, но я нашел полезный и простой обходной путь. Допустим, вы пытаетесь VLOOKUP в лист, который постоянно заменяется (Sheet2). Это ваша формула:

Когда вы удалите Sheet2, ссылка будет прервана. Но если вы продолжите заменять Sheet2 на листы, названные так же, сделайте следующее:

На случайной ячейке, скажем, AA1, напишите:

Не используйте знак равенства. Вы хотите это как чистый текст.

Затем измените свой VLOOKUP на:

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

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