Исходная таблица Найти и заменить Поиск данных в ячейках Excel
1 1 Исходная таблица id Фамилия Имя Пол Возраст Рост Задолженность 1 Петрова Даша ж 21 низкий нет 2 Ложкин Дмитрий м 45 средний да 3 Петухов Алксей м 14 средний нет 4 Петухов Алксей м 14 средний нет 5 Кошкин Андрей м 66 высокий да 6 Сидоров Алксей м 23 высокий да 7 Иванов Петя м 23 высокий нет Найти и заменить При работе с большим количеством данных в Excel, иногда достаточно трудно отыскать какую-то конкретную информацию. И, как правило, такой поиск занимает очень много времени. Excel предлагает прекрасный инструмент поиска. Вы можете с легкостью отыскать любую нужную информацию в рабочей книге Excel с помощью команды Найти, которая к тому же позволяет еще и изменить данные с помощью инструмента Найти и заменить. Поиск данных в ячейках Excel В нашем примере мы воспользуемся командой Найти, чтобы отыскать требуемое имя в длинном списке сотрудников. Если перед использованием команды Найти Вы выделите одну ячейку, то Excel будет выполнять поиск во всем листе. А если диапазон ячеек, то только внутри этого диапазона 1. На вкладке Главная воспользуйтесь командой Найти и выделить, а затем из раскрывающегося списка выберите пункт Найти. 2. Появится диалоговое окно Найти и заменить. Введите данные, которые необходимо отыскать. В нашем примере мы введем имя сотрудника. 3. Нажмите Найти далее. Если данные присутствуют на листе, то они будут выделены. 4. Если Вы еще раз нажмете на кнопку Найти далее, то увидите следующий вариант поиска. Вы также можете выбрать Найти все, что бы увидеть все варианты, которые для Вас отыскал Excel. 5. Закончив поиск, воспользуйтесь кнопкой Закрыть, чтобы выйти из диалогового окна Найти и Заменить. Вы можете получить доступ к команде Найти с помощью комбинации клавиш Ctrl+F на клавиатуре. Чтобы увидеть дополнительные параметры поиска и замены, нажмите кнопку Параметры в диалоговом окне Найти и заменить.
2 2 Замена содержимого ячейки в Excel Бывают случаи, когда допущена ошибка, повторяющаяся во всей книге Excel. Например, неправильно написано чье-то имя или необходимо изменить определенное слово или фразу на другую. Вы можете воспользоваться инструментом Найти и заменить, чтобы быстро внести исправления. В нашем примере мы воспользуемся командой Заменить, чтобы исправить список адресов электронной почты, а также имя «Алксей» заменим на «Алексей». 1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить. 2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти. 3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее. 4. Если значение будет найдено, то содержащая его ячейка будет выделена. 5. Посмотрите на текст и убедитесь, что Вы согласны заменить его. 6. Если согласны, тогда выберите одну из опций замены: o Заменить: исправляет по одному значению каждый раз. o Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени. 7. Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения. 8. Содержимое ячеек будет заменено.
3 Закрепление областей Закрепляем строки в Excel Закрепляя строки или столбцы, Вы сможете прокручивать содержимое, при этом закрепленные ячейки останутся в поле зрения. 1. Выделите строку ниже той, которую требуется закрепить. В нашем примере мы хотим зафиксировать строки 1 и 2, поэтому выделим строку Откройте вкладку Вид на Ленте -> Закрепить области и из раскрывающегося меню выберите одноименный пункт. 3. Строки будут закреплены, а область закрепления будет обозначена серой линией. Теперь Вы можете прокручивать рабочий лист Excel, но закрепленные строки останутся в поле зрения в верхней части листа. Столбцы закрепляются аналогично строкам. Чтобы снять закрепление строк или столбцов, нажмите Закрепить области, а затем из выпадающего меню выберите пункт Снять закрепление областей. Если Вам необходимо закрепить только верхнюю строку (Строка1) или первый столбец (Столбец А), можете выбрать соответствующую команду в раскрывающемся меню. 3
4 Фильтр Если необходимо отобразить только записи, удовлетворяющие определённому критерию, то используйте фильтр. Для этого: 1. Кликните по любой ячейке в наборе данных. 2. На вкладке Данные (Data) нажмите Фильтр (Filter). В заголовках столбцов появятся стрелки. 3. Нажмите на стрелку рядом с заголовком 4. Кликните по строке Выделить все (Select All), чтобы убрать все галочки, затем поставьте галочку в необходимой строке. 5. Чтобы отменить фильтрацию, на вкладке Данные (Data) кликните Очистить (Clear). Чтобы удалить фильтр полностью, т.е. убрать стрелочки, нажмите ещё раз кнопку Фильтр (Filter). 4 Числовой фильтр 1. Нажмите на стрелку в столбце, содержащем числовые значения. 2. Выберите Числовые фильтры (Number Filters). Эта опция доступна, поскольку рассматриваемый столбец содержит числовые данные. В открывшемся списке кликните по пункту Больше (Greater Than). 3. Введите число и нажмите ОК. Примечание: Ещё можно отобразить только данные, равные какому-либо значению, меньше определенного значения, между двумя значениями, первые n чисел из списка, значения выше среднего и т.д. Текстовый фильтр Чтобы применить текстовый фильтр, выполните следующие действия: 1. Нажмите на стрелку в столбце, содержащем текст. 2. Выберите Текстовые фильтры (Text Filters). Эта опция доступна, поскольку рассматриваемый столбец содержит текстовые данные. В выпадающем списке кликните по пункту Равно (Equals). 3. Введите, к примеру, *a и нажмите ОК. Примечание: Вопросительный знак (?) соответствует ровно одному любому символу. Звездочка (*) соответствует нескольким символам (от нуля и более). Расширенный фильтр в Excel При использовании расширенного фильтра необходимо ввести критерии на лист. Продублируйте заголовки столбцов, создайте диапазон условий справа от набора данных Критерий «И» Чтобы отобразить всех мужчин в возрасте от 25 лет, необходимо продублировать заголовок основной таблицы и заполнить новую таблицу данными следующим образом: 1. Выделите любую ячейку из основного набора данных. 2. На вкладке Данные (Data) в разделе Сортировка и фильтр (Sort & Filter) нажмите кнопку Дополнительно (Advanced).
5 5 3. Кликните по полю Диапазон условий (Criteria range) и выберите диапазон новой таблицы Обратите внимание на опцию Скопировать результат в другое место (Сopy to another location) и Только уникальные записи (Unique records only). Последняя пригодится, если набор данных содержит дубликаты. Критерий «ИЛИ» Для отображения всех мужчин, которым <25 или >60, выполните следующие действия: 1. Введите критерии, указанные на рисунке ниже. 2. На вкладке Данные (Data) нажмите кнопку Дополнительно (Advanced) и введите в поле Диапазон условий (Criteria range) созданный диапазон. 3. Нажмите ОК. Критерии с формулой Для отображения всех мужчин старше 25 и младше 60 выполните следующие шаги: Разместите формулу (как дополнительное условие) в новом столбце. Создайте относительную ссылку на первую ячейку в целевом столбце (в нашем случае это F2). Формула должна определить, является выражение истинным или ложным. 9. Отфильтруйте
6 Сортировка в Excel В Excel можно сортировать данные по одному или нескольким столбцам. Сортировка может быть выполнена в порядке возрастания или убывания. По одному столбцу Чтобы отсортировать данные по одному столбцу, выполните следующие действия: 1. Выделите любую ячейку в столбце, по которому нужно выполнить сортировку. 2. Для сортировки в порядке возрастания, откройте вкладку Данные (Data) и кликните по иконке АЯ (AZ). По нескольким столбцам Чтобы отсортировать данные по нескольким столбцам, выполните следующие действия: 1. На вкладке Данные (Data) нажмите Сортировка (Sort). Откроется диалоговое окно Сортировка (Sort). 2. В выпадающем списке Сортировать по (Sort by) выберите столбец для сортировки (в нашем примере это Фамилия) Нажмите кнопку Добавить уровень (Add Level). 4. В выпадающем списке Затем по (Then by) выберите следующий столбец для сортировки (мы выбираем Возраст). Результат: Записи отсортированы сначала по столбцу Фамилия, затем по столбцу Возраст. Пользовательская сортировка Иногда можно столкнуться с тем, что стандартные инструменты сортировки в Excel не способны сортировать данные в необходимом порядке. К счастью, Excel позволяет создавать настраиваемый список для собственного порядка сортировки. Мы хотим отсортировать данные в таблице по росту. Обычная сортировка расставит строчки с данными о росте в алфавитном порядке, что неправильно. Создадим настраиваемый список для сортировки размеров от меньшего к большему. 1. Выделите любую ячейку в таблице Excel, которую необходимо сортировать. 2. Откройте вкладку Данные, затем нажмите команду Сортировка. 3. Откроется диалоговое окно Сортировка. Выберите столбец, по которому Вы хотите сортировать таблицу. В данном случае мы выберем сортировку по росту. Затем в поле Порядок выберите пункт Настраиваемый список. 4. Появится диалоговое окно Списки. Выберите НОВЫЙ СПИСОК в разделе Списки. 5. Введите варианты роста в поле Элементы списка в требуемом порядке. В нашем примере мы хотим отсортировать рост от меньшего к большему, поэтому введем по очереди: низкий, средний, высокий, нажимая клавишу Enter после каждого элемента.
7 7 6. Щелкните Добавить, чтобы сохранить новый порядок сортировки. Список будет добавлен в раздел Списки. Убедитесь, что выбран именно он, и нажмите OK. 7. Диалоговое окно Списки закроется. Нажмите OK в диалоговом окне Сортировка для того, чтобы выполнить пользовательскую сортировку. Функция ЕСЛИ Проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет. ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]) Сортировка в Excel по формату ячейки Кроме этого Вы можете отсортировать таблицу Excel по формату ячейки, а не по содержимому. Данная сортировка особенно удобна, если Вы используете цветовую маркировку в определенных ячейках. В нашем примере мы отсортируем данные по цвету ячейки, чтобы увидеть, у кого имеются задолженности. 1. Выделите любую ячейку в таблице Excel, которому необходимо сортировать. 2. Откройте вкладку Данные, затем нажмите команду Сортировка. 3. Откроется диалоговое окно Сортировка. Выберите столбец, по которому Вы хотите сортировать таблицу. Затем в поле Сортировка укажите тип сортировки: Цвет ячейки, Цвет шрифта или Значок ячейки. В нашем примере мы отсортируем таблицу по столбцу Задолженность и по цвету ячейки. 4. В поле Порядок выберите цвет для сортировки. В нашем случае мы выберем светло-красный цвет. 5. Нажмите OK. Таблица теперь отсортирована по цвету, а ячейки светло-красного цвета располагаются наверху. Такой порядок позволяет нам четко видеть задолженности.
8 Поиск дубликатов в Excel с помощью условного форматирования 1. Выделите нужный диапазон 2. На вкладке Главная (Home) нажмите Условное форматирование > Правила выделения ячеек (Conditional Formatting > Highlight Cells Rules) и выберите Повторяющиеся значения (Duplicate Values) Определите стиль форматирования и нажмите ОК. Результат: Excel выделил повторяющиеся данные в ячейках. Как удалить дубликаты в Excel 1. Выделите любую ячейку из набора данных. 2. На вкладке Данные (Data) нажмите кнопку Удалить дубликаты (Remove Duplicates). 3. Ставим нужные галочки и нажимаем ОК. Результат: Excel удаляет все дубликаты строк, кроме первой найденной идентичной строки.
9 Структурирование данных в Excel Структурирование делает информацию более удобной для просмотра. В этом примере мы научимся подсчитывать строки со связанными данными и сворачивать группу столбцов. 1. Для начала отсортируем данные по столбцу Рост. 2. На вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal). 3. В списке При каждом изменении в (At each change in) выберите Рост. Этот столбец мы будем использовать, чтобы структурировать нашу таблицу. 4. В списке Операция (Use function) выберите Количество (Count). 5. Поставьте галочку напротив Рост (см. рисунок ниже). 6. Нажмите ОК. 7. Чтобы свернуть группу ячеек, нажмите знак " " (минус). Можно использовать цифры, чтобы свернуть или развернуть группы по уровню. К примеру, нажмите 2, чтобы отображались только подуровни. 9 Примечание: Нажмите 1, чтобы отображать только итоги, или 3, чтобы отобразить всё. Чтобы свернуть группу столбцов, выполните следующие действия: 8. Выделите столбцы A и B. 9. На вкладке Данные (Data), кликните по Группировать (Group). 10. Нажмите на знак " ". Результат: 11. Чтобы удалить структуру, выделите любую ячейку из набора данных и на вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal). Затем кликните по Убрать все (Remove All). Чтобы удалить только группы, оставив промежуточные итоги, воспользуйтесь пунктом Удалить структуру из выпадающего меню команды Разгруппировать.