Excel открывает большие возможности в обработке рядов чисел и строк. Сегодня мы разберем, как обрабатывать большой объем данных в Excel. В этой части мы не будем вдаваться в макросы. Цель этой статьи — научиться работать с наиболее доступными и простыми формулами Excel, которые помогут нам выполнять свою работу в большинстве случаев.
Как в excel найти повторяющееся значение
В сводке данных мы можем столкнуться с проблемой, когда нам нужно быстро найти повторяющиеся строки из большого количества строк. Фактически, в одной строке может быть значение, а во второй, для того же наименования продукта, двойное значение или другое.
Возьмем стол. В столбце E ставим равно, а затем в поиске «Другие функции» ищем нужную формулу (см. Рис. 1)
Рис 1. Поиск формулы
Чтобы найти повторяющееся значение, в данном случае в коде продукта для столбца A, мы будем использовать простую формулу = СЧЁТЕСЛИ
Рис 2. Формула СЧЁТЕСЛИ
Мы выбираем весь столбец «A» и в диапазоне аргументов функции (маленькое синее окошко в центре экрана) у нас есть A: A, то есть весь диапазон, выбранный для этого столбца (см. Рисунок 3).
Рис 3.
Перейдите в окно «критерии» и выберите только первую строку рядом с кодом продукта. Здесь это будет отображаться как A2, см. Рис. 3.
Рис 4
Затем нажмите «ок» и в столбце «Д» отобразится цифра 1. Это означает, что для продукта 100101200 Молоко Вологда 1% жирности, есть только один продукт этого типа, дубликатов нет. См. Рисунок 5.
Рис 5.
Мы «растягиваем» значения в столбце «E» вниз и получаем результат, то есть, какие продукты у нас есть дубликаты в нашем списке, см. Рис. 6. У нас есть 2 идентичных продукта (в Excel они обозначены цифрой 2), которые для наглядности вручную выделены желтым.
рис 6. Результат поиска повторяющихся кодов продуктов
Если бы у нас в списке было три одинаковых товара, excel, соответственно проставляйте цифру 3. И так далее. Уже через простой фильтр вы можете выбрать все, что больше 1, и увидеть полную картину.
Как в excel удалить дублирующиеся строки
Фактически, вышеуказанный метод уже отвечает нашим требованиям. Однако, если вам не нужны данные из повторяющихся строк, а просто нужно их удалить, есть самый простой способ быстро удалить дубликаты.
Мы будем использовать функцию, которая уже встроена в панель Excel. См. Вкладку ДАННЫЕ на панели. Наша функция называется «Удалить дубликаты».
Выбираем область поиска, у нас снова столбец A. См. Рисунок 7.
(В более поздних версиях Excel вы можете найти все через поле поиска.)
Рис 7. Удаление дубликатов
Далее нам просто нужно подтвердить удаление. Однако для ясности я выделил эти двойные линии зеленым цветом. Это строки 7 и 21. См. Рисунок 8.
Рис 8
Теперь на панели нажимаем кнопку «удалить дубликаты». У нас есть окно. Здесь автоматически предлагается удалить всю горизонтальную линию, т.е. «автоматически расширить выделенный диапазон». Нажмите кнопку «удалить дубликаты». См. Рис.9
Рис 9
Далее видим, что отмечены столбцы, отмеченные флажками, которые будут удалены по дублированному горизонтальному ряду. См. Рисунок 10. Нажмите ОК».
Рис 10
Все. Теперь мы видим окно с уведомлением об удалении однострочного дубликата. Теперь вместо 21-й строки для дублирующего товара появился следующий товар из нижнего списка. См. Рисунок 11.
Рис 11
Судя по описанию, может показаться, что на это уходит не меньше времени, чем на первую версию, но на самом деле это не так. Я только что очень подробно описал эту функцию.
Как в excel обработать большой объем данных, сводная таблица
Сводная таблица используется для сведения разрозненной информации. Сегодня мы также узнаем, как это сделать. Здесь нет ничего сложного. Например, нам нужно, сколько у нас одного и того же продукта, но не по марке, а по типу продукта.
Посмотрим на нашу таблицу. На панели инструментов найдите вкладку ВСТАВИТЬ. Под панелью инструментов, в верхнем левом углу, отображается значок, называемый «Сводная таблица», см. Рис. 12. (или путем поиска в поиске новой версии Excel)
Мы выбираем все столбцы или столбцы интересующих нас значений.
рис 12.
Затем щелкните значок «сводная таблица». Появится окно, в котором выбран диапазон столбцов. По умолчанию Excel предлагает вам переместить сводную таблицу на новый лист (см. Рисунок 13). Это то, что мы делаем.
рис 13
Подтверждаем команду нажатием кнопки «ок». Мы получаем на новом листе нашей страницы Excel возможность построения сводной таблицы, см. Рис. 14.
рис 14
Теперь давайте выберем нужные нам значения в правом верхнем углу. Как только мы решили, что нам нужно знать, сколько активов у нас есть для имени, мы выбираем имя актива с помощью галки. См. Рисунок 15.
рис 15
По аналогии ставим галку перед количеством (лом за штуку, склад 1).
При этом данные с количеством перемещаем не в окно «Имена строк», а в окно «Значения», см. Рис. 16
Здесь мы видим, что у нас есть дополнительный столбец, но пока не для количества штук каждого продукта, а для количества строк. Далее выполняем следующую операцию.
рис 16
Щелкните правой кнопкой мыши столбец с суммой. См. Рис. 17. Открывается окно, в котором в строке ИТОГО ПРОГРАММНОГО ОБЕСПЕЧЕНИЯ мы выбираем поле не для числа (строк), как на рисунке, а для суммы.
рис 17
Теперь мы получаем именно консолидированное количество по каждому товару. См. Рисунок 18.
рис 18. Сводка товаров по количеству
Для сравнения и наглядности вернемся к исходному листу (см. Рис. 19) и посмотрим:
идентичный товар по наименованию, отмеченный синим цветом 3 + 3 = 6 штук.
идентичные товары обозначены зеленым цветом 5 + 56 = 61 шт.
То же самое у нас в сводном столе (рис. 18) 6 и 61 шт.
рис 19
Вы можете добавить поставщика в сводную таблицу и так далее. Вы можете сделать его более сложным с точки зрения количества учитываемых столбцов. Это уже вопрос необходимости и практики. Сделайте это один-два раза, вы поймете суть. Следовательно, вы никогда не забудете возможность обрабатывать большие объемы данных на уровне сводной таблицы в Excel.
Как в excel подтянуть данные из одного диапазона в другой, с помощью функции ВПР
Будет логично, если я сразу покажу вам, как «подтянуть» данные в excel с другого листа или файла на другой. Для этого есть отличная функция ВПР. Мы узнаем, как использовать его с данными, которые мы уже знаем.
Например, вам нужно собрать числа из другого магазина, заказать запас для листа Excel. Это делается по значению ключа, которое должно быть во всех источниках данных. Это может быть уникальный код товара или его название.
Сразу оговорюсь по имени или текстовому значению, функция ВПР бескомпромиссная.
Если в названии продукта есть пробел или точка (любое отклонение), то для него уже будет другое значение.
также необходимо, чтобы все источники были в одном формате. Если мы говорим о числах, то в числовом формате.
Итак, у нас есть исходный файл на листе 1 (см. Рисунок 20)
рис 20
Из листа 2 (рис. 21) мы извлечем числа из листа 1. Обратите внимание, что количества на листах различаются. Строки также можно перемещать в списке или перемешивать, поэтому мы не можем обойтись простым добавлением одной цифры к другой.
рис 21
Для нас данные листа 1 — это те, до которых мы должны поднять другие значения. Также действуем через знак равенства «=». Вверху слева, ища другие функции, мы находим ВПР, см. Рис. 22.
рис 22
Затем открывается окно, и мы выбираем весь столбец A, то есть желаемое значение. В новом окне он выделен как A: A, см. Рис. 23.
рис 23
Затем мышкой в самом окне переходим во вторую строку «таблица», только потом переходим на 2 лист нашего файла.
рис 24
А из столбца «А» выбираем и растягиваем до столбца с количеством. В этом случае в столбце «D» см. Рис. 25.
рис 25
Столбец D — это четвертый столбец, начиная с желаемого значения, то есть с кода товара столбца A.
Поэтому мы помещаем цифру 4 в третье поле поля «номер столбца» и только ноль в поле «вид диапазона». В результате мы получаем полное окно, см. Рис. 26.
рис 26
Нажимаем «ок», и со второго листа у нас получается узкая цифра, по коду товара 100101200 см. Рис. 27.
рис 27
Мы растягиваем значение вниз, столбец D заполняется числами из листа 2 (см. Рис. 28). Здесь нам просто нужно сложить одни числа с другими с помощью простой формулы сложения и растянуть их вниз.
рис 28
Следовательно, мы можем извлечь значение из большого массива данных, что требует много времени для ручного поиска и не рекомендуется, если присутствует функция ВПР.
Важный момент. Если вы извлекаете из другого файла, файлы необходимо сохранить. А потом. Точные формулы ценности остаются. Вы должны привести числа в значения или не удалять и не изменять значения, которые вы выбрали.
Как в excel обработать большой объем данных, функция правсимв и левсимв
Бывает, что вам нужно поработать с функцией ВПР, чтобы привести желаемые значения и значения, которые мы извлекаем, в одну форму. Как мы уже говорили выше, для ВПР любое отклонение, даже пробел, — это уже другое значение.
В этом нам поможет функция excel: правая и левая стороны. То есть с помощью этой функции вы можете убрать ненужные знаки слева или справа от нашего значения, например, названия товара.
Итак, нам просто нужно взять часть полного имени. Давайте посмотрим на наш Рис. 29, например, нам просто нужно слово «молоко». Также ищем = levsymb в окне поиска формул.
рисунок 29
Появится окно, см. Рис.30.
рис 30
Выбираем интересующий нас столбец «B», в строке «текст» он отображается как B: B, см. Рис. 31.
рис 31
Также в строке «количество символов» мы помещаем это число, сколько букв или символов содержат слово или слова с пробелом, начиная с левой стороны. Если нам нужно только слово «молоко», то в нем с учетом пробела 7 букв, поэтому ставим цифру 7. См. Рис. 32.
рис 32
Итак, наше имя было сокращено только до нужного нам слова, см. Рис. 33.
рис 33
Теперь остается «растянуть» вниз, и все значения с первыми 7 символами слева окажутся в нашей таблице, см. Рис. 34.
рис 34
По аналогии можно использовать функцию ВПРАВО. Здесь все так же, только символы слева слева. Эта функция часто используется для числовых значений, когда код содержит дополнительные символы или разделен, например, точкой.
Надеюсь, материал был полезен, всего наилучшего для вас. Удачи!