Уроки MS Excel

Фильтры для столбцов

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

Как фильтровать данные в Excel 2007

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

После нажатия на кнопку «ОК», мы увидим только строки с заданным минимальным остатком.

Теперь применим еще один фильтр. Отберем детали с весом более 850 грамм.


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

Как это сделать в Excel 2003

Здесь автофильтр включается через меню «Данные».

Покоряйте Excel и до новых встреч!

Удаление пустых строк

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

Как избавиться от пустых строк в Excel 2007

• Добавьте к таблице еще один столбец и проставьте в нем номера строк по порядку.
• Выделите всю таблицу, включая добавленный вспомогательный столбец.
• Вкладка «Данные», кнопка «Сортировка».
• Отсортируйте диапазон в любом порядке.

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

Как это сделать в Excel 2003

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

Покоряйте Excel и до новых встреч!

Умножаем столбец на число

И снова нам поможет специальная вставка, которую мы уже применяли в двух темах: «Как в Excel скопировать данные, а не формулу» и «Как в Excel транспонировать таблицу».
Имеем табличку со стоимостью заказов, и хотим увеличить эту стоимость на 7%.

• Вводим в любой свободной ячейке «1,07».
• Копируем это значение в буфер обмена («Ctrl+C»).
• Выделяем столбец со стоимостью заказов.
• Правый щелчок – «Специальная вставка».

В группе «Операция» выбираем «Умножить». «ОК» — и вот результат:

Покоряйте Excel и до новых встреч!

Убираем пробелы в числах

Обычные пробелы проще всего удалить при помощи команды «Найти и заменить».

• Выделите диапазон, в котором вам нужно удалить все пробелы.
• «CTRL+H».

Войдя в поле «Найти», нажмите на пробел, а поле «Заменить на» оставьте пустым. «Заменить все» — и пробелы удалятся.
При копировании таблиц из интернета или импорте из баз данных, в числах могут встретиться не обычные, а так называемые неразрывные пробелы. Их тоже можно удалить по этой методике, но вам нужно будет ухитриться выделить, скопировать и вставить в поле «Найти» этот самый неразрывный пробел.

Если же вам нужно убрать разделительные пробелы в числах, то заходим в настройки формата ячейки и выбираем «Числовой». При этом, снимаем галочку «Разделитель групп разрядов».

Покоряйте Excel и до новых встреч!

Транспонируем таблицу

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

• Выделяем таблицу и копируем ее в буфер обмена («Ctrl+C»).
• Выделяем на свободном месте вертикально три ячейки, так, чтобы вставляемая таблица не пересекалась с существующей.
• Правый щелчок, специальная вставка.
• Ставим флажок «Транспонировать».

Любуемся на результат.

Этот способ универсальный и годится для всех версий Excel.
Покоряйте Excel и до новых встреч!

Как скопировать данные, а не формулу

Это можно сделать во всех версиях Excel с помощью специальной вставки.

• Выделяем ячейку, с результатом вычисления.
• «Ctrl+C».
• Переходим в ячейку, в которую нам нужно вставить значение.
• Правый щелчок.
• Специальная вставка.
• Выбираем «Значения».

Вместо контекстного меню в Excel 2007 можно воспользоваться кнопкой «Вставить»

А в Excel 2003 обратиться в меню «Правка», выбрав «Специальная вставка».
Покоряйте Excel и до новых встреч!

Располагаем числа в порядке возрастания

Для этого воспользуемся сортировкой.

Как отсортировать данные в Excel 2007

Чтобы отсортировать числа в столбце, выделим этот столбец.

• Вкладка «Данные».
• Кнопка «Сортировка».

Если на листе имеются другие данные, появится такое сообщение:

Нам нужно сортировать только выделенный диапазон. Жмем «Сортировка» и соглашаемся с параметрами, показанными в следующем окне.

После этого числа выстроятся в нужном нам порядке.
Аналогичным образом можно сортировать данные в строке, только понадобится нажать на кнопку «Параметры» и указать, что мы хотим сортировать столбцы диапазона, а потом в поле «Сортировать по» выбрать нужную строку.

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

Это значит, что при выборе порядка сортировки «От А до Я», первыми будут выводиться текстовые данные, начинающиеся с цифры «0», а последними – с буквы «Я».

Как это сделать в Excel 2003

Отличие только во внешнем виде окна, в котором мы задаем диапазон и направление сортировки.

Покоряйте Excel и до новых встреч!

Скрываем/показываем данные в столбцах и ячейках

Спрятать ненужные строки и столбцы совсем просто. Выделите их, правым щелчком откройте контекстное меню и нажмите «Скрыть».
А вот снова их показать будет немного сложнее. Для этого нужно выделить с обеих сторон строки или столбцы соседние со скрытыми. И после этого выбрать в контекстном меню «Показать».
Впрочем, в случае со строками можно поступить проще – выбрать все («Crtl+A»), а потом «Показать», а вот со столбцами этот фокус не срабатывает.
Самый неприятный случай – это когда у вас скрыт первый столбец. Действовать приходится следующим образом.
Чтобы выделить скрытый столбец, нужно указать его адрес «C1» в поле, где обычно показывается адрес активной ячейки, и нажать «Enter».

После этого жмем на кнопку «Формат».

Как это сделать в Excel 2003

Отличие только в расположении пункта «Отобразить».

Покоряйте Excel и до новых встреч!

Ищем дубликаты значений в ячейках

Воспользуемся возможностями условного форматирования. Эту тему мы уже рассматривали в статье «Закрасить ячейку по условию или формуле», а теперь применим для решения другой задачи.

Ищем повторяющиеся записи в Excel 2007

Выделим столбец, в котором будем искать дубликаты (в нашем примере это столбец с каталожными номерами), и на главной вкладке ищем кнопку «Условное форматирование». Далее по пунктам, как на рисунке.

В новом окне нам остается только согласиться с предлагаемым цветовым решением (или выбрать другое) и нажать «ОК».

Теперь повторяющиеся значения у нас окрашены в красный цвет. Но они разбросаны по всей таблице и это неудобно. Нужно отсортировать строки, чтобы собрать их в кучку. Обратите внимание, что в приведенной таблице есть столбец «№ п/п», содержащий номера строк. Если у вас его нет, его следует сделать, чтобы мы потом смогли восстановить исходный порядок данных в таблице.
Выделяем всю таблицу, переходим на вкладку «Данные» и жмем на кнопку «Сортировка». В новом окне нам нужно задать порядок сортировки. Выставляем нужные нам значения и добавляем следующий уровень. Нам нужно отсортировать строки сначала по цвету ячеек, а потом по значению в ячейке, чтобы дубликаты оказались рядом друг с другом.

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

Обратите внимание, что по мере удаления дубликатов красные ячейки возвращают себе белый цвет.
Избавившись от цветных ячеек, снова выделим всю таблицу и отсортируем ее по столбцу «№п/п». После этого останется только поправить сбившуюся из-за удаленных строк нумерацию.

Как это сделать в Excel 2003

Здесь будет немного сложнее – придется использовать логическую функцию «СЧЕТЕСЛИ()».
Войдите в ячейку с первым значением, среди которых вы будете искать дубликаты.

• Формат.
• Условное форматирование.

В первом поле выберите «Формула» и введите формулу «=СЧЕТЕСЛИ(C;RC)>1». Только не забудьте вовремя переключить раскладку – «СЧЕТЕСЛИ» набирается в русской раскладке, а «(C;RC)>1» в английской.

Цвет выберите, нажав на кнопку «Формат» на закладке «Вид».
Теперь нам нужно скопировать этот формат на весь столбец.

• Правка.
• Копировать.

Выделяем весь столбец с проверяемыми данными.

• Правка.
• Специальная вставка.

Выбираем «Форматы», «ОК» и условное форматирование скопировалось на весь столбец.
Покоряйте Excel и до новых встреч!

Как получить модуль числа

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

Покоряйте Excel и до новых встреч!