Выделить диапазон ячеек, в который должна входить ячейка, не содержащая данных

1. Выделить диапазон ячеек, в который должна входить ячейка, не содержащая данных

2. Нажать кнопку на панели инструментов

3. Снять выделение

Более сложные вычисления выполняются с помощью специальных функций, которые состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ. Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9 — ее аргументы.

Функции делятся на следующие виды: без аргумента; с одним аргументом; с фиксированным числом аргументов; с неопределенным числом аргументов; с необязательными аргументами. Если функция имеет два и более аргумента, то они разделяются между собой точкой с запятой. В одной формуле может быть как одна, так и несколько функций, объединенных между собой различными знаками операций. Если в качестве аргументов функции используются другие функции, то такие функции называются вложенными. Excel предлагает несколько сотен встроенных стандартных функций, которые разделены на категории. Функции имеют следующую форму у=f(x), где х – аргумент, f - функция, у – результат вычисления функции.

Список категорий доступен при выборе команды Функция в меню Вставка.

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

Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ.

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

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

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

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.

Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.

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

Для вставки функции в формулу необходимо:

1. Дать команду Вставка → Функция или щелкнуть по кнопке с изображением . Появится окно «Мастер функции».

2. В списке «Категория» выбирать нужную категорию функций, а затем в списке «Функция» выбирать конкретную функцию.

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

4. Задавать аргументы функции можно в числовом виде или как ссылки на ячейки. Некоторые функции могут принимать в качестве параметра диапазон ячеек.

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

1.8 Сортировка данных

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

Программа Excel позволяет производить сортировку и фильтрацию данных.

Сортировка – это упорядочение данных по возрастанию или по убыванию. Чтобы отсортировать данные необходимо:

а) Выделить область базы данных или поместить в нее курсор.

б) Щелкнуть по кнопке Сортировка по возрастанию или Сортировка по убыванию.

Более точную сортировку можно задать по команде Данные Сортировка. В диалоговом окне «Сортировка диапазона» выбрать от 1 до 3 полей сортировки и задать порядок сортировки по каждому полю.

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

При использовании Автофильтра необходимо:

а) Поместить курсор в область, содержащую базу данных, или выделить ее.

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

в) Щелкнуть по кнопке поля и выбрать критерии фильтрации. Отобразятся записи, удовлетворяющие условиям фильтрации.

Для отключения фильтрации необходимо дать команду Данные → Фильтр → Отобразить все. Это позволяет отобразить все записи.

Чтобы отменить использование Автофильтра надо повторно дать команду Данные → Фильтр → Автофильтр.

При использовании пользовательского фильтра необходимо:

1. Поместить курсор в область, содержащую базу данных, или выделить ее.

2. Дать команду Данные → Фильтр → Расширенный фильтр

3. Указать исходный диапазон, диапазон условий и диапазон, куда поместить результат.

4. В диапазоне условий по каждому полю выбрать критерии фильтрации.

1.9 Поиск данных

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

Автофильтр существуют два способа применения команды Автофильтр:

с помощью меню Данные - Фильтр - Автофильтр - раскрывающийся список команд автофильтра.

с помощью кнопки «Автофильтр» на панели инструментов стандартная.

2)Расширенный фильтр (Данные - Фильтр - Расширенный фильтр).

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

Использование автофильтра для поиска записей. Перед применением команды «Автофильтр» выделите ячейку в диапазоне списка. Далее выполните команду Данные - Фильтр - Автофильтр, справа от заголовков (подписей) столбцов появятся кнопки со стрелками автофильтра (раскрывающиеся списки команд автофильтра). Если щелкнуть стрелку автофильтра, отображается список различных вариантов фильтрации и сортировки по возрастанию и убыванию. При выполнении фильтрации стрелка активного автофильтра окрашивается в синий цвет.

Список команд автофильтра и их назначение:

Все - все записи будут отображаться на экране.

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

Условие... - откроется окно диалога «Пользовательский автофильтр». С помощью этого окна можно отсортировать записи, удовлетворяющие одному или двум условиям.

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

Алгоритм поиска записей отвечающих одному условию в одном столбце:

Выберите ячейку в списке.

Выполните команду Данные - Фильтр – Автофильтр.

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

Щелкните на одном из уникальных имен, редактор мгновенно скроет строки, которые не содержат выбранных данных.

На рисунке представлен отфильтрованный список по столбцу «Категории» при выбранном условии отбора «Конд. Продукты».

При необходимости отфильтрованный список по одному столбцу можно фильтровать по другим столбцам.

Алгоритм поиска записей отвечающих одному или двум условиям в одном столбце. Для поиска записей по двум условиям применяется «Пользовательский автофильтр»:

Выберите ячейку в списке.

Выполните команду Данные - Фильтр – Автофильтр.

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

Щелкните на команде «Условие», откроется окно диалога «Пользовательский автофильтр».

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

Щелкните на кнопке ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям. Для примера выберем следующие условия отбора: показать в списке строки, содержащие продукты, цены которых больше или равны 69,80 руб. и меньше 99,99 руб.

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

На рисунке представлен отфильтрованный список по столбцу «Цена» при выбранных условиях отбора.

Фильтрация по выделенному - это быстрый способ отфильтровать записи по одному условию с помощью кнопки «Автофильтр» на панели инструментов стандартная.

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

Создание диапазона условий отбора:

Выделите заголовки фильтруемых столбцов списка и выполните команду «Копировать» любым из известных методов

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

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

После создания диапазона условий отбора, можно применить к списку команду «Расширенный фильтр». На рисунке представлен образец «Расширенного фильтра» выбранными параметрами фильтрации.