Фильтрация по конкретным числам или тексту

Методичка по Excel.

1.Настраиваем Excel «под себя».

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

 

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

Для добавления команды в существующую вкладку, необходимо выделить её и нажать кнопку «Создать группу». Группу можно переименовать. Далее из левого списка перетягиваете необходимые команды и нажимаете «ОК!»

Для добавления новой вкладки достаточно нажать «Создать вкладку». Автоматически под новой вкладкой создастся и новая группа. Далее всё также – обычным перетаскиванием, добавляем необходимые команды в группу.

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

 

2. Инструменты.

2.1. Специальная вставка.

Команда Специальная вставка (Paste Special) в Excel позволяет легко выполнять определенные манипуляции с данными в электронной таблице. Рассмотрим самые важные из них.

· Транспонировать.

У вас есть список номенклатур в отдельном столбце. Как сделать так, чтобы список отображался в строке, а не в столбце?

Для этого выделяем столбец и копируем его с помощью кнопки "копировать" или с помощью сочетания клавиш CTRL+C. Далее правой кнопкой мыши встаём на нужную ячейку и в контекстном меню выбираем пункт «Транспонировать». Данные транспонируются в строку, а не в столбец.

 

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

· Копирование значений формул.

Если нужно убрать формулы и вставить только значение ячейки, нужно при специальной вставке выбрать пункт"значения".

 

· Мгновенные математические вычисления.

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

Мгновенное вычисление также можно использовать, если вам необходимо перед каждым числом в столбце поставить знак минус. Для этого вам нужно умножить столбец на -1

 

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

· Сравнение со значением.

Если нам необходимо найти значение больше, меньше или равное какому-либо числу или значению определённой ячейке – нам поможет инструмент «Условное форматирование».

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

 

 

· Поиск повторяющихся и уникальных значений.

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

 

· Текст по столбцам.

Когда данные находятся в одной ячейке и необходимо их раскидать по столбцам, можно воспользоваться инструментом «Текст по столбцам». Находится он на вкладке «Данные».

Выделяем нужную ячейку и нажимаем на инструмент. Если данные в ячейке перечисляются, выбираем формат данных «с разделителями». Помечаем необходимый символ-разделитель, проверяем образец разбора данных и нажимаем «Готово».

При необходимости данные можно заполнить в столбец, воспользовавшись уже известным нам инструментом – «Транспонировать».

 

 

3. Сортировка и фильтр.

· Фильтр по значениям.

Фильтр можно установить по кнопке «Сортировка и фильтр» на главной вкладке.

1. Щелкните стрелку в заголовке столбца, который нужно отфильтровать.

2. В списке текстовых элементов или чисел снимите флажок (Выделить все) в начале списка и установите флажки элементов, которые нужно отобразить в таблице.

3. Нажмите кнопку ОК.

Стрелка фильтрации в заголовке столбца пример вид , означающий, что фильтр применен. Чтобы изменить или удалить фильтр, щелкните ее.

Фильтрация по конкретным числам или тексту

1. Щелкните стрелку в заголовке столбца, который нужно отфильтровать.

2. Если столбец содержит числа, выберите пункт Числовые фильтры. Если столбец содержит текстовые элементы, выберите пункт Текстовые фильтры.

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

Например, чтобы отобразить числа, превышающие определенное значение, выберите пункт больше или равно и введите это значение.

Чтобы выполнить фильтрацию по двум условиям, введите условия в оба набора полей и установите переключатель в положение И, если оба условия должны быть истинными, либо ИЛИ, если достаточно истинности одного из условий.

· Фильтр по цвету.

Если в таблице используются разные цвета ячеек или шрифтов или условное форматирование, можно выполнить фильтрацию по цветам или значкам, отображаемым в таблице.

1. Щелкните стрелку в заголовке столбца, к которому применено форматирование цветом или условное форматирование.

2. Выберите пункт Фильтр по цвету, а затем — цвет ячейки, цвет шрифта или значок, по которым нужно выполнить фильтрацию.

 

4. Функции.

Ссылка Excel – это адреса ячеек в формуле, которые необходимо подсчитать. Бывают относительные, абсолютные ссылки. По умолчанию ссылки относительные.

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

Абсолютная ссылка используется тогда, когда нам необходимо, чтобы адрес ячейки не менялся.

У смешанных ссылок "закреплены" либо столбец, либо строка, в зависимости от того, перед чем стоит знак доллара.

 

 

4.1. Функция ЕСЛИ -выполняет проверку условия.

ЕСЛИ (ячейка, [значение_если_истина], [значение_если_ложь]) – Если значение в ячейке равно определённому числу, то это истина. Иначе это ложь.

Например, если сумма накладной больше 9тыс., то принимать значение «ИСТИНА», если меньше, то значение «ЛОЖЬ». =ЕСЛИ(G2<9000;"ИСТИНА";"ЛОЖЬ")

 

4.2. Функция СЧЁТЕСЛИ – подсчитывает количество непустых ячеек.

СЧЁТЕСЛИ (диапазон; критерий). Например, необходимо посчитать количество ячеек, где сумма будет равна 2576.65. =СЧЁТЕСЛИ(G2:G16;2758.72)

 

4.3. Функция СУММЕСЛИ - суммирует ячейки, удовлетворяющие заданному условию.

(диапазон, критерий, [диапазон суммирования]):

Диапазон – это диапазон ячеек, оцениваемых по критериям. То есть по этому диапазону EXCEl будет проверять условия.

Критерий – это условие, определяющее какие ячейки необходимо просуммировать. Например, код клиента.

!!ВАЖНО: все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки (“”).!!

Диапазон_суммирования – это ячейки, значения из которых суммируются.

Рассмотрим на примере. Нам необходимо посчитать сумму всех накладных в таблице только по клиенту К0023061 =СУММЕСЛИ(A2:A16;A13;F2:F17)

 

4.4. Функция ОКРУГЛ.

Функция ОКРУГЛ в Excel округляет значения до указанного количества разрядов, т.е. знаков до или после запятой. =ОКРУГЛ(число; число_разрядов).

 

Важно! Для точного округления необходимо использовать именно функцию, а не форматирование ячеек. Иначе может получится так, что 2+2 =5. То, что вы видите на экране не всегда соответствует тому, что находится внутри ячеек.

Наглядный пример: введите в одну ячейку число 2,45, а в другую 2,466. Сделайте форматирование ячеек, выберите числовой формат и поставьте число разрядов – 0. А теперь просуммируйте эти две ячейки. 2 +2 станет равно 5. Поэтому важно при округлениях использовать функцию ОКРУГЛ, а не форматирование ячейки.

 

4.5. Функция СЦЕПИТЬ - объединяет несколько текстовых элементов в один.

=СЦЕПИТЬ(A2;",";A3;) - если необходимо сцепить значения через знак препинания, то нужно взять его в кавычки ","

4.6. Функция СЖПРОБЕЛЫ – убирает лишние пробелы в тексте.

=СЖПРОБЕЛЫ(ячейка с текстом)

 

5. Сводные таблицы.

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

Чтобы создать сводную таблицу, необходимо перейти на вкладку «Вставка» и выбрать инструмент «Сводная таблица».

Рассмотрим пример. Нам нужно узнать сумму всех накладных по каждому заказу за определённый период. И добавить фильтр по менеджеру. Выглядеть это будет так:

 

 

6. ВПР вертикальный просмотр.

Используя функцию ВПР при работе в Excel, Вы можете извлекать требуемую информацию из электронных таблиц, которые могут находится на других листах, и даже в других файлах. Самое важное, чтобы в каждой таблице столбцы соответствовали друг другу. =ВПР(A2;ВПР2!A1:L125;7;0)

7. Поиск и удаление картинок на листах.

В случае, когда у вас есть файл с картинками и картинки необходимо удалить,можно воспользоваться следующей возможностью:

· Нажмите сочетание клавиш CTRL+G

· В открывшемся окне нажмите кнопку «Выделить» и выберите пункт «Объекты».

· Автоматически у вас выделяются все объекты на листе (картинки,видео и т.д.)

· Далее нажмите клавишу Delete. Все объекты удалятся.

8. Горячие клавиши.

Ctrl+S –сохранить документ

Ctrl+Z– отменить последнее действие

Ctrl+P –распечатать открытый документ

Shift+Ctrl +стрелка – выделяет всю таблицу или всю строку

Shift+Ctrl+End –выделяет всю таблицу, где заполнены ячейки

Alt + Enter –переход на новую строку в ячейке

Shift+Ctrl+4 – вставить дату

Shift+Ctrl+6 – вставить время

Shift+F2 – вставить комментарий