Как удалить дубли в Excel 2003
Нет, не так. На самом деле подошел сотрудник и сказал — а не поставить ли нам эксель 2010? По опыту знаю, что ему требуется пару раз в день заполнять небольшую таблицу, ничего архисложного. Поэтому сразу возник логичный вопрос — а тебе зачем? На что вполне логичный ответ — а там можно одной командой дублирующиеся ячейки удалить. Угу. То есть 3-4 т.р. за то, чтобы дубли удалить. А надо сказать, я вообще очень плохо отношусь к неоправданным расходам в бизнесе. Одно дело, когда что-то требуется для непосредственного выполнения какой-либо функции, которую ни в чем другом выполнить нельзя. Или занимает столько времени, что дешевле оптимизировать, или написать под это специальную программу — вот сейчас, например, пишем за полторы штуки баксов одну такую. А другое дело, когда кто-то хочет на 10 минут подольше посидеть во вконтакте в рабочее время, и просто ленится разобраться, как пару кнопок нажать.
Ну ладно, сейчас расскажу, как удалить дубликаты в excel 2003, и можно идти дальше придумывать, зачем еще 2010-й тебе может понадобиться (не, для чего он нужен мне — я прекрасно знаю :-)).
Самый простой способ а) — как удалить повторяющиеся значения excel:
1. Берем, выделяем диапазон ячеек с дублями, нажимаем на Данные -> Фильтр -> Расширенный фильтр…
2. Дальше — в появившемся окошке отмечаем чек-бокс «Только уникальные записи», нажимаем ОК.
3. Получаем результат, который можно сделать Ctrl+C — Ctrl+V на нужное место/лист.
Теперь вариант B), для тех, кто не боится сложностей
1. Левее крайнего левого столбца нашей таблицы вставляем дополнительный столбец (допустим, у нас был А — вставим еще один А, чтобы наш стал B), и в нем проставляем порядковые номера (обычным вводом в ячейках цифр 1 и 2, выделяя эти две ячейки и двойным кликом на черной точке в правом нижнем углу все распространяется до конца диапазона). Это нам потребуется потом, если мы захотим восстановить порядок следования записей, если он не важен — так можно и не делать. Получится примерно так:
2. Дальше, выделяем две ячейки в строчке 2, с зажатым шифтом щелкаем на нижней границе выделения, таким образом — выделив все с A2 по B12. Жмем Данные ->Сортировка.
3. Сортируем список по столбцу B, скажем, по возрастанию.
4. В ячейку C2 вставляем формулу =ЕСЛИ(B2=B1;0;1), которая сравнивает каждое значение с предыдущим. Если строка — дубль, то ей будет присвоено значение 0, если нет — то 1. Ну, конечно, значения B2 и B1 — это на моем примере, все зависит, сколько столбцов в таблице.
5. Щелкаем на обведенную красным кружочком точку в правом нижнем углу ячейки, чтобы продлить формулу на всю колонку (аналогично, как мы вставляли порядковые номера):
6. С полученным результатом делаем Ctrl+C, идем в Правка -> Специальная вставка
7. В открывшемся диалоге выбираем — Вставить Значения
8. Теперь выделяем первые три ячейки в строке 2, с зажатым шифтом щелкаем на нижней границе выделения, таким образом — выделив все с A2 по С12
Жмем Данные ->Сортировка, сортируем по столбцу С, по убыванию (это важно — отсортировать именно по убыванию! Если бы мы дублям назначили 1, а не 0 — то надо было бы отсортировывать наоборот, по возрастанию). Скриншот приводить не буду, поскольку абсолютно аналогично шагам 2 и 3
9. Выделяем столбец С, нажимаем Ctrl-F, вводим в форму поиска 0, и ищем в этом столбце первую по порядку ячейку с нулем.
10. Выделяем всю строку, с А по С, в которой ноль впервые нашелся, с зажатым шифтом щелкаем мышкой на нижней границе выделения, таким образом — выделив все значения ниже. Далее делаем с ними все, что захотим: можем удалить к чертовой матери, а можем скопировать куда-либо эти дубли. Предположим, что удалили.
11. Удаляем значения из столбца С — он тоже свою роль сыграл.
12. Выделяем целиком столбцы А и B, жмем Данные ->Сортировка, и сортируем по столбцу А (в моем случае — по номерам) по возрастанию.
13. В получившемся списке удаляем колонку А, и получаем в результате таблицу, аналогичную исходной, но без дублей. Для сравнения в столбце D привел то, как она выглядела до всех этих итераций.
Описывать это все гораздо дольше, чем делать, в принципе — уходит максимум 30 секунд.
Альтернатива данной функции
Excel позволяет пользователям достичь правильного решения разными путями. Данная формула не исключение. Заменить данную функцию можно с помощью двух других: ДЛСТР и ПОДСТАВИТЬ. Чтобы определить количество заданного символа сначала определите длину всего текста функцией ДЛСТР, а затем отнимите от этого значения длину этого же текста, только с предварительно удаленными символами (которые вы ищите): =ДЛСТР(B13)-ДЛСТР(ПОДСТАВИТЬ(B13;» «;»»)). В файле для скачивания приведены конкретные примеры.
Найдем количество вхождений определенной Буквы или любого символа в ячейке EXCEL.
Определим, сколько букв Z в слове Zigzag (слово находится в ячейке A1).
Формула для подсчета символа Z в слове Zigzag (с учетом регистра), см. файл примера : =ДЛСТР(A1) — ДЛСТР(ПОДСТАВИТЬ(A1;»Z»;»»))
Формула для подсчета символа Z (или z) в слове Zigzag (без учета РЕгиСТра): =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A1);»z»;»»))
Для подсчета количества пробелов (код символа пробела — 32) в ячейке используйте формулу: =ДЛСТР(A1) — ДЛСТР(ПОДСТАВИТЬ(A1;СИМВОЛ(32);»»))
В статье Подсчет вхождений последовательности символов показано, как подсчитать количество вхождений некоторой последовательности символов в текстовой строке.
О поиске позиции n-го вхождения буквы в слове или в текстовой строке можно прочитать здесь.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки
Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы
Для удобства также приводим ссылку на оригинал (на английском языке) .
Если вам нужно подсчитать количество символов в ячейках, функция LENиспользовать — которого подсчитывает букв, чисел, знаков и все пробелы. Например, длина «Находится 98 градусов сегодня, поэтому я буду перейти swimming» (за исключением кавычки) — 42 символов — 31 буквы, числа 2, 8 пробелов, запятую и апострофы 2.
Чтобы использовать функцию, введите = ДЛСТР ( ячейку ) в строке формул, нажмите клавишу ВВОД на клавиатуре.
Нескольких ячеек: Чтобы применить такую же формулу в несколько ячеек, введите формулу в первую ячейку и затем перетащите маркер заполнения вниз (или через) диапазон ячеек.
Чтобы получить общее количество символов в нескольких ячейках является использование функций сумм вместе с ДЛСТР. В этом примере функция LEN подсчитывается количество символов в каждой ячейке и функция сумм прибавляет счетчиков:
= СУММ ((функция LEN ( cell1 ), функция LEN ( cell2 ), (функция LEN ( cell3 )) )).
Метод 1: отображение количества значений в строке состояния
Пожалуй, это самый легкий метод, который подойдет для работы с текстовыми и числовыми данными. Но он не способен работать с условиями.
Воспользоваться этим методом крайне просто: выделяем интересующий массив данных (любым удобным способом). Результат сразу появится в строке состояния (Количество). В расчете участвуют все ячейки, за исключением пустых.
Еще раз подчеркнем, что при таком методе учитываются ячейки с любыми значениями. В теории, можно вручную выделить только интересующие участки таблицы или даже конкретные ячейки и посмотреть результат. Но это удобно только при работе с небольшими массивами данных. Для больших таблиц существуют другие способы, которые мы разберем далее.
Другой минус этого метода состоит в том, результат сохраняется лишь до тех пор, пока мы не снимем выделение с ячеек. Т.е. придется либо запоминать, либо записывать результат куда-то отдельно.
Порой бывает, что по умолчанию показатель “Количество” не включен в строку состояния, однако это легко поправимо:
Щелкаем правой клавишей мыши по строке состояния.
В открывшемся перечне обращаем вниманием на строку “Количество”. Если рядом с ней нет галочки, значит она не включена в строку состояния
Щелкаем по строке, чтобы добавить ее.
Все готово, с этого момента данный показатель добавится на строку состояния программы.
Подсчет количества значений в столбце в Excel
Как подсчитать сумму значений в ячейках таблицы Excel, наверняка, знает каждый пользователь, который работает в этой программе. В этом поможет функция СУММ, которая вынесена в последних версиях программы на видное место, так как, пожалуй, используется значительно чаще остальных. Но порой перед пользователем может встать несколько иная задача – узнать количество значений с заданными параметрами в определенном столбце. Не их сумму, а простой ответ на вопрос – сколько раз встречается N-ое значение в выбранном диапазоне? В Эксель можно решить эту задачу сразу несколькими методами.
Какой из перечисленных ниже способов окажется для вас наиболее подходящим, во многом зависит от вашей цели и данных, с которыми вы работаете. Одни операторы подойдут только для числовых данных, другие не работают с условиями, а третьи не зафиксируют результат в таблице. Мы расскажем обо всех методах, среди которых вы точно найдете тот, который наилучшим образом подойдет именно вам.
Нулевые строки.
Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.
Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.
Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.
Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.
И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.
Если вы не хотите рассматривать их как пустые, используйте для подсчета реально пустых клеток следующее выражение:
Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:
- Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
- Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:
=ЕСЛИ(Е1=1;10;””)
В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.
Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.
Метод 2: применение функции СЧЕТЗ
Данная функция позволяет сделать то же самое, но с одной существенной разницей – полученный результат будет зафиксирован. Остальные минусы предыдущего метода сохраняются, т.е. СЧЕТ3 не работает с условиями.
Функция СЧЕТ3 выполняет задачу по подсчету всех заполненных ячеек в заданном диапазоне (пустые не учитываются). Формула функции может выглядет по-разному:
- =СЧЕТЗ(ячейка1;ячейка2;…ячейкаN)
- =СЧЕТЗ(ячейка1:ячейкаN)
В первом случае функция выполнит подсчет всех перечисленных ячеек. Во втором – определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N
Обратите внимание, что количество аргументов функции ограничено на отметке 255
Давайте попробуем применить функцию СЧЕТ3 на примере:
- Выбираем ячейку, где по итогу будет выведен результат подсчета.
- Переходим во вкладку “Формулы” и нажимаем кнопку “Вставить функцию”.
Также можно кликнуть по значку «Вставить функцию» рядом со строкой формул. - В открывшемся меню (Мастер функций) выбираем категорию «Статистические», далее ищем в перечне нужную функцию СЧЕТ3, выбираем ее и нажимаем OK, чтобы приступить к ее настройке.
- В окне «Аргументы функции» задаем нужные ячейки (перечисляя их или задав диапазон) и щелкаем по кнопке OK. Задать диапазон можно как с заголовком, так и без него.
- Результат подсчет будет отображен в выбранной нами ячейке, что изначально и требовалось. Учтены все ячейки с любыми данными (за исключением пустых).
Подсчет Уникальных ТЕКСТовых значений в MS EXCEL
.. вывод готовых данных.
СЧЁТЕСЛИ«Статистические» данный вариант предлагаетВыделяем элемент листа, в что полученный результат рассмотрим, как решить: Всем спасибо! предложеннымиNovaya ввода формулы не столбец слева. в диапазонах.Сколько ещё возвращает значение Пустой. Соответственно, подсчитываются остальные может содержать текстовые
Задача
В полеВыделяем ячейку, куда будем Щелкаем по значкупозволяет задавать условия,. Затем выделяем наименование выводить результат в который будет выводиться
нигде не фиксируется.
поставленные задачи различными
вариантами решила задачу:-)Отдельное: Пожалуйста:-) Enter, а сочетаниеSadiar непонятного! текст. Пользователь решает значения, т.е. «а»
значения и числа.«Диапазон» выводить результат, и«Вставить функцию»
отвечающие значения, которые
«СЧЁТ» конкретный элемент листа итоговый результат. Щелкаем
То есть, как способами. спасибо за макрос.Файл удален Ctrl+Shift+Enter.
: Как посчитать, количествоkim подсчитать количество уникальных и «b». ОтветЕсли исходный список содержит:вводим координаты третьего уже описанным ранее. будут принимать участие
и щелкаем по с возможным его
по значку только вы сниметеДанный способ самый простойНастенька- велик размерТехнически, эта формула пробегает повторяющихся данных, если: Так как подсчёт товаров в январе очевиден: количество уникальных
«Вставить функцию» выделение, он исчезнет. и требующий минимального: Спасибо и мне по всем ячейкам повторяющихся данных в осуществляется в одномерных (их всего 3: значений равно 2.11212
В поле аргументов оператораМастере функций остальные ячейки будутПосле того, как было к сожалению, функция
, который размещен слева Поэтому, при необходимости количества действий. Он помогли)) Добрый ВыMarchuk массива и вычисляет столбце около 30,
Как выполнить подсчёт ячеек со значением
Здравствуйте, дорогие читатели.
Перед началом данной темы я бы хотел вам посоветовать отличный обучающий продукт по теме экселя, по названием « Неизвестный Excel » , там всё качественно и понятно изложено. Рекомендую.
Ну а теперь вернёмся к теме.
Вы наверняка являетесь представителем одной из профессий, в которой не обойтись без программы эксель. Ведь она позволяет вести подсчеты, составлять списки, таблицы и диаграммы, заполнять ежедневники и решать многие другие задачи, связанные с цифрами.
Однако не все, кто работает с этим приложением, знает его полную функциональность и умеет ее применять на практике. Вы один из них? Тогда вы обратились по адресу. В частности, сегодня мы разберем, как в excel подсчитать количество ячеек со значением. Есть несколько способов, как это сделать. Они зависят от того, какое именно содержимое вам нужно посчитать. Разберем самые популярные из них.
Как подсчитать уникальные значения в Excel, используя СУММПРОИЗВ и СЧЁТЕСЛИ
Если вы хотите подсчитать уникальные значения в Excel, лучший способ сделать это — использовать формулу. Для этого можно использовать комбинацию функций СУММ и СЧЁТЕСЛИ.
Чтобы быстро объяснить, что делают эти функции, СУММПРОИЗВ умножит, а затем вернет сумму диапазона данных. COUNTIF подсчитает количество ячеек в диапазоне, соответствующих условию. Вы можете использовать обе функции вместе, чтобы вернуть количество уникальных значений для каждой ячейки в массиве.
Например, если у вас есть список чисел от одного до восьми, но число восемь повторяется дважды, возвращаемый массив будет содержать значение 1, а не восьмерку, где будет возвращено значение 2. Это связано с тем, что значение 8 повторяется дважды.
Чтобы подсчитать уникальные значения в Excel, используя СУММПРОИЗВ и СЧЁТЕСЛИ:
- Откройте электронную таблицу Excel.
- Выберите пустую ячейку рядом с вашим набором данных, убедившись, что столбец, в котором он находится, пуст.
- В строке формул введите следующую формулу: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон,диапазон)) при замене обоих значений диапазона диапазоном ячеек, содержащим ваши данные. (например, =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A8;A2:A8))).
- Нажмите Enter — формула вернет общее количество уникальных значений в диапазоне ячеек.
Метод 6: функция СЧИТАТЬПУСТОТЫ
В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.
По синтаксису функция крайне проста:
=СЧИТАТЬПУСТОТЫ(диапазон)
Порядок действий практически ничем не отличается от вышеперечисленных:
- Выбираем ячейку, куда хотим вывести итоговый результат по подсчету количества пустых ячеек.
- Заходим в Мастер функций, среди статистических операторов выбираем “СЧИТАТЬПУСТОТЫ” и нажимаем ОК.
- В окне «Аргументы функции» указываем нужный диапазон ячеек и кликаем по кнопку OK.
- В заранее выбранной нами ячейке отобразится результат. Будут учтены исключительно пустые ячейки и проигнорированы все остальные.
Работа со строками в Excel. Текстовые функции Excel
Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню. Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных. В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.
Функции категории «Текстовые»
Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.
- БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
- ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
- ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
- ЗНАЧЕН (Текст) – преобразует текст в число;
- ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
- ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
- НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
- ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
- ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
- СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;
Это в основном часто используемые функции при работе со строками. Теперь рассмотрим пару примеров, которые продемонстрируют работу некоторых функций.
Пример 1Дан набор строк:
Необходимо из этих строк извлечь даты, номера накладных, а так же, добавить поле месяц для фильтрации строк по месяцам.
Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :
= ПСТР (A2; НАЙТИ («№»;A2)+1;6)
Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.
Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:
= ПРАВСИМВ (A2;8)
но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:
= ЗНАЧЕН ( ПРАВСИМВ (A2;8))
а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».
Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:
= ЗНАЧЕН ( СЦЕПИТЬ («01»; ПРАВСИМВ (A2;6))) или = ЗНАЧЕН («01″& ПРАВСИМВ (A2;6))
Задайте формат ячеке «ММММ ГГГГ». Результат:
Пример 2 В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».
Формула будет следующая:
=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)
Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК, последней будет ПОДСТАВИТЬ.
Все. Если есть вопросы, задавайте, не стесняйтесь
Как собрать суточную мочу на анализ
Утренняя порция мочи выпускается в унитаз. При последующем мочеиспускании моча собирается в ёмкость. Нужно будет увидеть, во сколько это вышло. Дальше, вся моча в течение суток (до такого же времени последующего денька) обязана собираться в эту же ёмкость.
Очень принципиально, чтоб была собрана вся дневная моча, потому на денек сбора материала анализа не следует планировать далеких поездок. Ёмкость с мочой нужно держать в холодильнике.
По окончании сбора материала нужно будет без помощи других оценить общий объём собранной мочи. Опосля чего же моча перемешивается и часть её (до 200 мл) отливается в особый контейнер. Конкретно этот контейнер доставляется в лабораторию (весь объём мочи доставлять не нужно).
При сдаче мочи в лабораторию нужно указать с какого по какое время собиралась моча; каковой вышел общий размер мочи. В неких вариантах будет нужно указать собственный четкий рост и вес.