Юрий Лубягин - MS Excel. Приемы работы с данными

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

Рассмотрим строку «123456789100 Фамилия Имя Отчество 01.01.1900» текст «Фамилия Имя Отчество» находится между первым и четвертым пробелом.

Алгоритм действий: Чтобы получить текст «Фамилия Имя Отчество» из строки «123456789100 Фамилия Имя Отчество 01.01.1900» необходимо вернуть строку от первого пробела до четвертого, чтобы узнать длину строки, надо отнять от позиции четвертого пробела позицию второго пробела минус один.

Порядок действий:

1. Находим позицию первого пробела – в ячейку B1 вводим формулу « =НАЙТИ(" ";A1;1)»;

2. Имея позицию первого пробела, находим позицию второго пробела и в ячейку C1 вводим формулу « =НАЙТИ(" ";A1;B1+1)»;

3. Так же находим позицию третьего пробела – в ячейку D1 вводим формулу « =НАЙТИ(" ";A1;C1+1)»;

4. И получаем позицию четвертого пробела – в ячейку E1 вводим формулу « =НАЙТИ(" ";A1; D 1+1)»;

5. Для получения текстовой строки «Фамилия Имя Отчество» в ячейку G1 вводим формулу « =ПСТР(A1;B1+1;E1-B1-1)».

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

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

В Excel массив данных представлен в виде диапазона или нескольких диапазонов. Диапазон в Excel состоит из одной и более ячеек. В Excel ограничения на количество столбцов и строк на листе определены версией приложения. Версия 2010, на которой рассматриваются примеры, позволяет создать на одном листе диапазон из 16384 столбца и 1048576 строк.

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

Операции с одномерным массивом

1. Проверка на наличие дублей записей и их удаление;

2. Поиск дублирующих записей;

3. Сравнение (поиск разности и пересечения).

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

Выделяем весь диапазон данных. Для быстрого выделения необходимо выделить первую запись в списке (заголовок можно не выделять) и одновременно нажать на клавиатуре кнопки Ctrl+Shift+↓(стрелочка вниз). Это прием работает и с конца списка, достаточно выделить последнюю ячейку и одновременно нажать клавиши Ctrl+Shift+↑(стрелочка вверх). И так же и вправо и влево.

Выделив диапазон, на вкладке «Данные» нажимаем кнопку «Удалить дубликаты»

Выходит диалоговое окно.

Нажимаем «ОК»

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

Дубли из массива удалены, все записи в нем уникальны.

Теперь необходимо узнать, какие записи и сколько раз повторяются . Вернем массив, в исходное состояние, через кнопку отмена, или используя сочетание клавиш Ctrl+Z.