Auditportal.ru

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

Как найти и подсчитать повторяющиеся ячейки / значения в одной строке в Excel?

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

Давайте рассмотрим, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016. Мы покажем различные способы поиска и удаления дубликатов в Excel, включая удаление дубликатов строк, удаление дубликатов столбцов, а также обнаружение абсолютных дубликатов и частичных совпадений. В статье рассматриваются следующие вопросы:

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.

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

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

  1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A .
  2. Далее перейдите на вкладку « ДАННЫЕ » —> группа « Работа с данными » и нажмите кнопку «Удалить дубликаты».

1-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel
  1. Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите « ОК ».
  • Чтобы удалить дубликаты строк, имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
  • Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку « Снять выделение », а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
  • Если в вашей таблице нет заголовков , уберите флаг с поля « Мои данные содержат заголовки » в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.

2-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

3-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов

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

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

Удалить дубликаты, скопировав уникальные записи в другое место

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

  1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
  2. Перейдите во вкладку « ДАННЫЕ » —> группа « Сортировка и фильтр » и нажмите кнопку « Дополнительно ».

4-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
  1. В диалоговом окне « Расширенный фильтр » выполните следующие действия:
  • Выберите пункт « скопировать результат в другое место ».
  • Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
  • В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
  • Выберите Только уникальные записи

5-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Фильтр дубликатов
  1. Наконец, нажмите « ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

6-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Другой способ удаления дубликатов в Excel — определить их с помощью формулы, отфильтровать и удалить дубликаты.

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

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

Формулы для поиска дубликатов в 1 столбце

  • Дублируется, кроме первой записи:
Читайте так же:
Как изменить ось X в диаграмме Excel?

= ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

  • Дубликаты первого появления:

$ A2 >1; «Дубликаты»; «Уникальные»)

Где A2 — первая ячейка, а A10 — последняя ячейка диапазона, в котором нужно искать дубликаты.

Формулы для поиска дубликатов строк

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

=If(CONTENTS($A$2:$A2 ; $A2 ; $B$2:$B2 ; $B2 ; $C$2:$C2 ; $C2)>1 ; «Дублирующий ряд» ; «Уникальный»)

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

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Колонки А, В и С проверяются на наличие дубликатов.

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

7-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев
  1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку « Фильтр » на вкладке « ДАННЫЕ », или « Сортировка и фильтр » —> « Фильтр » на вкладке « ГЛАВНАЯ ».

8-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
  1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок « Дубликат строки ».

9-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Фильтр дубликатов строки
  1. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите « Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей « Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

10-udalit-dublikaty-v-excel

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

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

Как в Excel найти повторяющиеся ячейки (текст) и их количество (см)?

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

Читайте так же:
Как зашифровать / расшифровать выбранные ячейки в Excel?

Каждое повторение должно иметь стату.

Т.е. это слово встречается 4 раза, это слово 3 раза, это слово 2 раза. и так далее. И так далее в том смысле, что четыре колонки — это не предел 🙂 В общем, надеюсь, задача понятна

Дополнительное условие (может облегчить вам задачу) в каждой колонке все слова уникальны, никаких повторений.

Следующую формулу можно использовать для определения количества повторений числа в диапазоне $A$2:$D$7:

=СЧЁТЕСЛИ($A$2:$D$­7;A1)

Затем эта формула растягивается на желаемый диапазон и записывается результат.

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

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

Для текстовых значений можно использовать эту формулу:

Число повторов

Если формула не копируется в Excel, необходимо использовать методы, описанные в ответах на вопрос Как скопировать формулу Excel из Интернета, чтобы она работала?

Не уверена, что правильно поняла задачу, но количество повторов по заданному диапазону легко прочитывается функцией =счетесли(диапазон;з­начение критерия, повторы которого требуется посчитать). Можете также поискать ответ или продублировать вопрос на этом сайте http://www.excel-vba­.ru/chto-umeet-excel/­kak-podschitat-kolich­estvo-povtorenij/

Время в Excel — это число, точнее, десятичная дробь меньше единицы. (Аналогично, дата — это число, большее 1. А дата со временем — это сумма этих чисел. ).

Для того чтобы число отображалось в ячейке в виде даты, времени или даты со временем, необходимо задать его формат в свойствах ячейки (щелкните по ячейке, выберите «Формат ячейки»). Таким образом, сложение и вычитание можно выполнять в любую дату и в любое время. Следует также помнить, что в Excel есть функция (TIME()), которая преобразует три значения (часы, минуты и секунды) в специальную десятичную дробь, которая на самом деле является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».

  1. Пусть в ячейке A1 у нас есть время «23:23:59«, тогда
  2. Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
  3. Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)

Скопировав его, диаграмму можно превратить в картинку.

Для того чтобы перенести график из EXCEL в Power Point. Откройте Excel, выделите график, затем нажмите «копировать». Затем откройте Power Point, откройте слайд и нажмите «вставить».

Вы можете сэкономить разными способами:

1 — клавиши быстрого доступа. Shift+F12 — сохранить. F12 — Сохранить как.

Contrl + S — Сохранить.

2. Нажимая клавишу Alt и клавиши со стрелками, выберите меню, которое хотите открыть, затем нажмите Enter.

Наверняка не все знают, что контакты можно копировать из таблиц Excel на телефон Android без лишних хлопот!

Моя электронная таблица содержит контакты; как мне перенести их на смартфон? Самое простое решение — установить из «Play Маркета» на свой смартфон приложение под названием: «Импорт Экспорт Контакты Excel».

Однако есть одна сложная вещь: Если вы хотите перенести контакты из таблицы Excel на смартфон, необходимо правильно составить таблицу! Самый простой способ — экспортировать контакты в файл электронной таблицы Excel. Это можно сделать в том же приложении, нажав на кнопку EXPORT. Полученную электронную таблицу можно редактировать на самом смартфоне или отправить по почте для последующего редактирования на компьютере. Вот таблица, которую я создал с моими контактами:

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

Как в EXCEL сложить числа в ячейках по определённому условию

Как в EXCEL сложить числа в ячейках по определённому условию

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

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

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

Как в EXCEL сложить числа в ячейках по определённому условию

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

Сначала мы напишем готовую формулу, вставим ее в ячейку D10, а затем начнем прорабатывать детали. Окончательная формула выглядит следующим образом (только для нашей статьи)

$ G$5 и $G$300 для лета («Покупки в магазинах»); $H$5 и $H$300 для зимы).

Различные условия были выделены цветом, чтобы сделать их более наглядными. Давайте разберем все по порядку. Чтобы лучше понять описание, смотрите рисунок выше. Буквы столбцов и номера строк были намеренно захвачены. Итак, начнем.

  • Этим условием мы говорим, что сумма значений в указанных ячейках должна быть записана в ячейку, если они удовлетворяют заданным условиям;
  • $G$5:$G$300 — здесь мы указываем EXCEL, в каком столбце он должен искать условие выборки. В нашем случае ищется столбец G, начиная со строки 5 и заканчивая строкой 300;
  • («Покупки в магазинах») — здесь мы указываем условие, которое мы ищем, и значения в следующих ячейках будут суммироваться в соответствии с этим условием;
  • $H$5:$H$300 — здесь мы указываем столбец, из которого будут взяты числа для суммирования. В нашем случае значения берутся из столбца H, начиная со строки 5 и заканчивая строкой 300.

В итоге EXCEL складывает только те значения в диапазоне H5:H300, для которых соответствующие значения в диапазоне G5:G300 являются «Покупки в магазине» и вводит результат в ячейку D10.

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

Знак $ в формуле используется для того, чтобы при копировании формулы из ячейки D10 в другие ячейки она не перемещалась. Давайте рассмотрим пример формулы без знака $. Например, у нас есть формула в ячейке D10 :

=СУММЕСЛИ(G5:G300;(«Покупки в магазинах»);H5:H300)

Затем в ячейке D11 мы запишем сумму обедов. Чтобы не переписывать формулу, мы можем скопировать ячейку D10 и вставить ее в ячейку D11. Это вставит формулу в D11, но здесь мы видим, что формула изменила значения, заменив 5 на 6 и 300 на 301:

=СУММЕСЛИ(G6:G301 ;(«Магазин в магазинах»);H6:H301)

Произошел сдвиг. В D12 смещение равно 2, и так далее. По этой причине мы пишем формулу со знаком $. Это особенности EXCEL.

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