1. Фильтры

Команду ФИЛЬТР можно использовать как для сортировки, так и для отображения значений, удовлетворяющих определённому условию. Чтобы применить команду ФИЛЬТР нужно на ленте вкладки ДАННЫЕ щёлкнуть по кнопке ФИЛЬТР и далее по кнопке раскрывающегося списка в заголовке того столбца, по которому необходимо осуществить фильтрацию. В результате получим окно диалога, отображённое ниже.

С помощью этого окна можно отобразить данные, удовлетворяющие одному из условий перечисленных на выпадающем меню строки ЧИСЛОВЫЕ ФИЛЬТРЫ .

Чтобы отобразить значения, удовлетворяющие условиям, составленным с использованием логических операторов and(и) илиor(или) используют строку НАСТРАИВАЕМЫЙ ФИЛЬТР на выпадающем меню.

Отобразим сотовые телефоны с гарантийными сроками, равными 1 или равными 6 месяц.

1. Поместите указатель курсора в список.

2. Выберите пункт ФИЛЬТР на вкладке ДАННЫЕ

3. Нажмите кнопку со стрелкой в заголовке столбца ГАРАНТИЙНЫЙ СРОК

4. Со строки ЧИСЛОВЫЕ ФИЛЬТРЫ откройте ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР и сделайте установки как ниже

2. Расширенный фильтр

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

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

Выражения, введённые в разные столбцы одной строки, объединяются логическим «и», в разные строки одного столбца – логическим «или».

Задание. Требуется отфильтровать записи, содержащие товары фирмыSamsungценой не более 3000 руб, с гарантийным сроком не менее 3 мес.

• На листе Фильтры выделите заголовок таблицы и поместите его в буфер обмена.

• Сделайте активной ячейку А37 и вставьте содержимое буфера обмена.

• В следующие ячейки введите условия фильтрации:

А38 – Samsung

С38 - <=10000

I38 ->=1

• Поместите курсор в таблицу, являющуюся списком, и примените команду ДОПОЛНИТЕЛЬНО

• В открывшемся окне диалога введите следующие параметры:

Фильтры с вычисляемыми условиями. Отфильтруйте записи на основании условия создаваемого как результат выполнения формулы. Например, создайте список товаров, прибыль от продажи которых превышает среднюю прибыль. Для этого:

• Скопируйте заголовок исходного списка в ячейку А50.

• Щелкнув в ячейке G51, введите условие отбора:

=G2> СрЗнач ($G$2:$G$35)

• Удалите подпись Прибыль в столбце заголовка условий отбора. У вас должно получиться следующее:

• Поместите курсор в исходный список и примените команду ДАННЫЕ / ДОПОЛНИТЕЛЬНО

• Сделайте установки в окне диалога РАСШИРЕННЫЙ ФИЛЬТР. Поместите результат в ячейку А53.

• Выделив два несмежных столбца МОДЕЛЬ и ПРИБЫЛЬ в образовавшемся списке, постройте круговую диаграмму, на которую поместите подписи данных.

2. Осуществление межтабличных связей с помощью формулы

Для ввода формул с вложенными функциями, например tg(sin(x²), следует выделить, например ячейку О, с помощью мастера функций выбрать функцию TAN (тангенс) не выбирая аргумент функции, в левой части СТРОКИ ФОРМУЛ раскрыть список функций, выбрать ДРУГИЕ ФУНКЦИИ с помощью Мастера функций выбрать SIN не выбирая аргумента функции, выбрать функцию СТЕПЕНЬ и её аргументы - адрес ячейки с переменной и показатель степени, нажать Ок. В результате будет введена формула =TAN(SIN(СТЕПЕНЬ(В1;2))).

Для решения задачи увеличения цены тех сотовых телефонов, объём продаж которых меньше средней величины объёма продаж всех сотовых телефонов, а прибыль по ним за первый квартал больше средней прибыли по всем телефонам за тот же период, добавим лист и скопируем первые три столбца с листа СПИСОК. Отрегулируйте ширину столбцов. Листу дайте название НОВАЯ ЦЕНА.

В ячейку С1 введите название НОВАЯ ЦЕНА. С помощью форматной метлы отформатируйте ячейку С1 подобно В1.

В ячейку С2 введите формулу: =ЕСЛИ(список!D2<среднее_количество_продаж;список!С2;ЕСЛИ(список!G2>средняя_прибыль;список!C2*1,3;список!C2))

Ввод ссылок на ячейки таблицы СПИСОК в окне мастера функций осуществляется непосредственно щелчком на указанных в формуле ячейках листа СПИСОК.

Вложенную функцию ЕСЛИ вводите либо из списка использованных функций ПОЛЯ ИМЕНИ ЯЧЕЙКИ, либо открыв мастер функций со строки ВСТАВИТЬ ФУНКЦИЮ того же списка.

Ввод ссылок на ячейки СРЕДНЕЕ_КОЛИЧЕСТВО_ПРОДАЖ и СРЕДНЯЯ_ПРИБЫЛЬ можно осуществить из окна ВСТАВКА ИМЕНИ, открыв его функциональной клавишей F3.погревать.

После ввода формулы в ячейку С2, скопируйте её на диапазон С3:С35 с помощью значка автозаполнения.