Средства Excel для удобного ввода и отображения информации

1.2. Выбор вводимых данных из списка
Чтобы организовать ввод данных в таблицу путем выбора из открывающегося списка допустимых значений необходимо:
выделить ячейку (ячейки), в которых предполагается организовать выбор данных;
дать команду п. м. Данные → Проверка;
на вкладке Параметры в поле Тип данных выбрать опцию Список;
в поле Источник ввести диапазон со списком допустимых значений.

Замечания:
Если список данных находится на другом рабочем листе, следует задать имя диапазону данных и указать его в поле Источник, нажав клавишу F3 для выбора нужного имени.
Если список данных формируется из фиксированного набора значений, то этот список вводится непосредственно в поле Источник диалогового окна Проверка вводимых значений через точку с запятой.

1.3. Проверка вводимых данных
1. Выделить ячейку (диапазон ячеек), на которые нужно наложить ограничения;
2. дать команду п. м. Данные → Проверка;
3. на вкладке Параметры в поле Тип данных выбрать нужное поле для проверки и в поле Значение задать условие проверки.
Чтобы вывести подсказку, а также дать пояснения по поводу некорректных данных или предотвращать их ввод, необходимо указать типы сообщений на вкладках Сообщение для ввода и Сообщение об ошибке.
Замечание: Чтобы предотвратить ввод данных если ячейки, на которые наложены ограничения, пусты или отсутствует список корректных данных, надо снять флажок Игнорировать пустые ячейки.

1.4. Условное форматирование
Условное форматирование – это форматирование, которое проявляется только при выполнении заданных условий.
Например, можно отобразить шрифт в ячейке белым цветом, если значение формулы в данной ячейке равно 0 (нулю), для этого:
1. Выделить ячейки, к которым необходимо применить условное форматирование (выделение несмежных диапазонов в Excel можно осуществлять при нажатой клавише Ctrl ).
2. Выбрать п. м. Формат → Условное форматирование.

3. Выбрать параметр значение для задания Условия1, выбрать операцию сравнения, а затем в соседнее поле ввести значение.
Ввести можно заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=).
4. Нажать кнопку Формат… .
5. Выбрать нужные элементы условного форматирования (тип шрифта, его цвет, подчеркивание, рамку, затенение ячеек или узоры).
Для добавления условий можно нажать кнопку А также >> а затем повторить шаги 3 - 5 (может быть задано до трех условий).

Замечания:
1. Условные форматы остаются примененными к ячейке до тех пор, пока они не будут удалены, даже если не выполняется ни одно из условий и ни один из указанных форматов ячейки не отображается.
2. В Excel есть возможность копировать форматы в другие ячейки, для чего надо выделить ячейки, содержащие копируемый условный формат и нажать кнопку Формат по образцу на ПИ «Стандартная», а затем выделить ячейки, которые должны иметь тот же условный формат.
3. Отображение 0 (нуля) в таблице отключается с помощью настроек: п. м. Сервис → Параметры → вкл. Вид, в группе переключателей Параметры окна отменить флажок нулевые значения.

1.5. Настройка документа Excel перед печатью
Для настройки параметров печати:
– п. м. Файл ⇒ Параметры страницы ⇒ …
для установки ориентации страницы – вкладка Страница, где также можно установить режим:
◉ разместить не более чем на 1 стр. в ширину и 1 стр. в высоту),
для изменения размеров полей – вкладка Поля,
для вывода заголовков строк и столбцов – вкладка Лист (в области параметров Печати включить флажок
√ заголовки строк и столбцов
и отключить флажок сетка).
Для настройки печати в формульном виде:
п. м. Сервис ⇒ Параметры ⇒ на вкл. Вид установить флажок √ Формулы

§ 2. Логические функции Excel
2.1. Функция ЕСЛИ (категория Логические) — возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ (используется при проверке условий для значений и формул).
Синтаксис:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)
Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА.
Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ.
Замечание: До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок!!!

2.2. Функция И (категория Логические) — возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Синтаксис:
И(логическое_значение1; логическое_значение2; ...)
2.3. Функция ИЛИ (категория Логические) — возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис:
ИЛИ(логическое_значение1; логическое_значение2; ...)
Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

§ 3. Функции поиска и просмотра данных в Excel
3.1. Функция ПРОСМОТР (категория Ссылки и массивы) — находит значение в одной строке или одном столбце значений, отсортированных по возрастанию, и возвращает значение из соответствующей позиции другой строки или столбца. Функция имеет две синтаксические формы: векторную и массива.
Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки.

Синтаксис векторной формы:
ПРОСМОТР(искомое_значение; вектор_просмотра; вектор_результата)
Синтаксис формы массива:
ПРОСМОТР(искомое_значение; массив)

Замечания:
1. Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе вектор_просмотра, которое меньше, чем искомое_значение.
2. Если искомое_значение меньше, чем наименьшее значение в аргументе вектор_просмотра, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

3.2. Функция ПОИСКПОЗ (категория Ссылки и массивы) – возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом и используется вместо, если нужна позиция элемента в диапазоне, а не сам элемент.
Синтаксис:
ПОИСКПОЗ(искомое_значение, просматриваемый_массив, тип_сопоставления)
Искомое_значение – это значение, используемое при поиске значения в таблице (т.е. это значение, которое сопоставляется со значениями в аргументе просматриваемый_массив).
Просматриваемый_массив – это непрерывный интервал ячеек, возможно, содержащих искомые значения.
Тип_сопоставления – это число -1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию.
Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть в любом порядке.
Если тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию.
Если тип_сопоставления опущен, то предполагается, что он равен 1.

Замечания:
ПОИСКПОЗ возвращает позицию соответствующего значения в аргументе просматриваемый_массив, а не само значение.
Например: ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 -- относительную позицию буквы "б" в массиве {"а";"б";"в"}.
ПОИСКПОЗ не различает регистры при сопоставлении текстов.
Если функция ПОИСКПОЗ не находит соответствующего значения, то возвращается значение ошибки #Н/Д.

3.3. Функция ЕНД (категория Проверка свойств и значений) –– используется для проверки типа значения или ссылки.
Данная функция проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ.
Возвращает значение ИСТИНА, если значение = сообщению об ошибке #Н/Д!
Синтаксис: ЕНД(значение)
Значение — это проверяемое значение.
Замечание: Эта функция полезна в формулах и макросах для проверки результатов вычислений. Комбинируя эту функцию с функцией ЕСЛИ, можно локализовать ошибки в формулах.

3.4. Функция ВПР (категория Ссылки и массивы) –– ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы, т.е. используется, когда сравниваемые значения расположены в столбце слева от искомых данных.
Синтаксис:
ВПР(искомое_значение; инфо_таблица; номер_столбца; интерв_просмотр)
Искомое_значение – это значение, которое должно быть найдено в первом столбце массива.
Инфо_таблица – это таблица с информацией, в которой ищутся данные.

Номер_столбца – номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение.
Если номер_столбца < 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца >, чем количество столбцов в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!
Интерв_просмотр – логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие.
Если этот аргумент имеет значение ИСТИНА (1) или опущен, то возвращается приблизительно соответствующее значение; т.е., если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.

В этом случае значения в первой строке аргумента инфо_таблица должны быть расположены в возрастающем порядке; в противном случае функция ВПР может выдать неправильный результат.
Если этот аргумент имеет значение ЛОЖЬ (0), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
В этом случае инфо_таблица не обязана быть сортированной.
Замечание: Если искомое_значение меньше, чем наименьшее значение в первой строке аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.

3.5. Функция ГПР (категория Ссылки и массивы) –– ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы или массива .
Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже.
Синтаксис:
ГПР(искомое_значение; инфо_таблица; номер_строки; интерв_просмотр)
Искомое_значение – это значение, которое должно быть найдено в первом столбце массива.
Инфо_таблица – это таблица с информацией, в которой ищутся данные.
Номер_строки – это номер строки в массиве инфо_таблица, из которой будет возвращено сопоставляемое значение .
Если номер_строки меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_строки больше, чем количество строк в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интерв_просмотр (то же, что и для функции ВПР) – это логическое значение, которое определяет, нужно ли, чтобы ГПР искала точное или приближенное соответствие.
Если этот аргумент имеет значение ИСТИНА (1) или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.
В этом случае значения в первой строке аргумента инфо_таблица должны быть расположены в возрастающем порядке; в противном случае функция ВПР может выдать неправильный результат.
Если этот аргумент имеет значение ЛОЖЬ (0), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
В этом случае инфо_таблица не обязана быть сортированной.
Замечание: Если искомое_значение меньше, чем наименьшее значение в первой строке аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.