Ответы по информатике. Ответы к экзамену по дисциплине Информатика
 Скачать 1.47 Mb.
|
|
38. Функция проверки условия. Использование логических функций в табличном процессоре. Использование логических функций. Логические функции являются неотъемлемым компонентом многих формул. Всякий раз, когда необходимо реализовать те или иные действия в зависимости от выполнения каких-либо условий, следует использовать логические функции. ^ Логическая функция ЕСЛИ. Функция проверки условия ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Функция ЕСЛИ используется при проверке условий для значений и формул. До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок. ^ Логические функции И/ИЛИ, НЕ. Логические функции И и ИЛИ предназначены для проверки выполнения нескольких условий: И(логическое_значение1;логическое_значение2; ...) Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. Когда условия соединены логическим И, результатом проверки нескольких условий считается значение ИСТИНА, если все условия имеют значение ИСТИНА; значение ЛОЖЬ, если хотя бы одно из условий имеет значение ЛОЖЬ. Например, ^ И(ИСТИНА; ИСТИНА) равняется ИСТИНА, И(ИСТИНА; ЛОЖЬ) равняется ЛОЖЬ, И(2+2=4; 2+3=5) равняется ИСТИНА. Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то И(B1:B3) равняется ЛОЖЬ. Если ячейка B4 содержит число между 1 и 100, то И(1 равняется ИСТИНА. Предположим, что нужно вывести на экран содержимое ячейки ^ B4, если она содержит число строго между 1 и 100 и сообщение "Значение вне интервала" в противном случае. Тогда, если ячейка B4 содержит число 104, то выражение ЕСЛИ(И(1 равняется "Значение вне интервала", а если ячейка B4 содержит 50, то на экране будет выведено число 50. Когда условия соединены функцией ИЛИ, результатом проверки условий считается: значение ИСТИНА, если хотя бы одно из условий имеет значение ИСТИНА; значение ЛОЖЬ, если все условия имеют значение ЛОЖЬ. Синтаксис логической функцией ИЛИ: ИЛИ(логическое_значение1;логическое_значение2; ...) Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. Например, ИЛИ(ИСТИНА;ЛОЖЬ) равняется ИСТИНА, ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ. Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то ИЛИ(A1:A3) равняется ИСТИНА. Логическая функция НЕ меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине: НЕ(логическое_значение) Логическое_значение - это значение или выражение, которое при вычислении дает ИСТИНА или ЛОЖЬ. Если логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; если логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ. Например, НЕ(ЛОЖЬ) равняется ИСТИНА, НЕ(1+1=2) равняется ЛОЖЬ. Функции поиска данных в некотором диапазоне. Рассмотрим две функции поиска данных в некотором диапазоне: функцию ПРОСМОТР и функцию ПОИСКПОЗ. Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки. Синтаксис: ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов) Искомое_значение — это значение, которое функция ПРОСМОТР ищет в первом векторе, может быть числом, текстом, логическим значением или ссылкой. Просматриваемый_вектор — интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Вектор_результатов — интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор. Если функция ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение. Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д. Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент. Синтаксис: ПОИСКПОЗ(Искомое_значение,просматриваемый_массив,тип_сопоставления) Искомое_значение — это значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Просматриваемый_массив - это непрерывный интервал ячеек, возможно, содержащих искомые значения. Тип_сопоставления - это число -1, 0 или 1. Если тип=1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Если тип=0, то функция находит первое значение, которое в точности равно аргументу искомое_значение. Если тип=-1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше, чем искомое_значение. Если тип опущен, то предполагается, что он равен 1. 39. Функция поиска данных в некотором диапазоне. Пример финансовых функций в табличном процессоре. Функция ВПР позволяет найти данные в исходной таблице и вывести их в любой ячейке новой таблицы. Основные условия работы данной функции: Наличие одинаковых элементов в сопоставляемых таблицах (например, код товара, фамилия сотрудника и т.д.). Сопоставляемые данные в исходной таблице должны быть отсортированы по возрастанию. Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Буква «В» в имени функции ВПР означает «вертикальный». Синтаксис ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр) Искомое_значение — это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой. Таблица — таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список. Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной. Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию. Значения в первом столбце аргумента «таблица» могут быть текстовыми строками, числами или логическими значениями. Текстовые строки сравниваются без учета регистра букв . Номер_столбца — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_столбца» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!. Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента «таблица», то функция ВПР возвращает значение ошибки #Н/Д. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д. 40. Операции над рабочими листами. Связывание листов в табличном процессоре. Ярлыки листов Excel располагаются внизу слева в области рабочей книги. Имя каждого рабочего листа появляется на соответствующем ярлыке. Однако любому ярлыку может быть дано другое имя, длиной не более 31 символа. Это имя может быть использовано при адресации листа в формулах Excel. Адресация листа Для использования в формуле данных, расположенных на определенных листах, необходимо после названия листа поставить восклицательный знак, затем адрес ячейки. Диапазон листов в Excel задается через двоеточие. Примеры адресации листов: Лист1!А1 Sheet12!G23 Квартал1!В4:Е7 Лист1!:ЛистЗ!А2 Во вновь создаваемую книгу по умолчанию Excel вставляет три листа. Операции, выполняемые с рабочими листами, доступны при вызове контекстного меню . Щелкнув правой кнопкой мыши по ярлыку рабочего листа, можно выбрать нужную операцию из меню. Имя рабочего листа Excel можно ввести не используя контекстное меню, для этого: 1. Щелкните дважды по ярлыку рабочего листа. 2. Введите имя листа в область ярлыка и нажмите Enter. Для перемещения между листами Excel щелкните ярлык необходимого листа. Имя текущего листа выделится жирным шрифтом. Кнопки прокрутки ярлыков листов могут быть использованы для перехода к другим листам. Для перемещения по списку листов щелкните кнопки прокрутки ярлыков. Для изменения количества выводимых ярлыков перетащите разделитель поля ярлыков вправо Операции удаления, перемещения и переименования листа Excel отменить нельзя. Добавление нового листа Excel Существует несколько способов добавления новых листов в рабочую книгу Excel. Команда Вставка, Лист используется для добавления нового листа в текущую рабочую книгу. Новый лист будет вставлен перед текущим листом. Любые формулы, использующие ссылки на листы Excel, будут автоматически обновляться при добавлении нового листа. Процедура, показанная ниже, также может быть использована для добавления нового листа Excel. 1. Щелкните правой кнопкой мыши по ярлыку текущего рабочего листа для открытия контекстного меню. 2. Выберите Добавить. Появится диалоговое окно Вставка. 3. Выберите Лист из диалогового окна и нажмите ОК. Удаление листов Excel Для удаления листов из рабочей книги можно воспользоваться командой Правка, Удалить лист или выбрать соответствующую команду из контекстного меню. Копирование и перемещение листов Excel В процессе обработки электронных таблиц Excel часто бывает необходимо перемещать и копировать листы как в пределах рабочей книги, так и в другую книгу. 1. Выделите лист для перемещения или копирования. 2. Выберите Правка, Переместить или скопировать лист или вызовите контекстное меню и выберите Переместить или скопировать . Появится диалоговое окно Переместить или скопировать. 3. В поле В книгу выберите книгу, в которую листы будут скопированы или перемещены. Книга должна быть открыта, чтобы ее можно было выбрать, 4. Выберите лист, перед которым лист(ы) будет вставлен в поле Перед листом. 5. Включите опцию Создавать копию для копирования. В противном случае данные будут перемещены. 6. Нажмите ОК после завершения. Как и ячейки таблицы Excel, листы также могут быть перемещены или скопированы с помощью перетаскивания. Этот метод может быть использован для перемещения или копирования листов в пределах одной рабочей книги или для открытых книг из одной в другую. Для перемещения листа перетащите ярлык выделенного листа на новую позицию. Указатель (треугольник) отмечает положение, куда будут помещены листы, когда кнопка мыши будет отпущена. Для копирования листов Excel перетащите выделенные листы на новую позицию при нажатой клавише Ctrl. Появление символа плюс (+) на указателе при копировании показывает, что выделенные листы будут скопированы. Каждая ячейка в Excel связана двумя координатами - номер строки и номер столбца. Поскольку рабочая книга состоит из нескольких рабочих листов (по умолчанию из трех), то ячейке можно присвоить третью координату - номер листа, которая представляет собой третье измерение, что позволяет связать данные из разных листов книги Excel. Использование рабочих книг с несколькими листами обеспечивает следующие преимущества: гибкость в оформлении рабочих листов; возможность связывать данные. В рабочей книге с несколькими листами с помощью связывания данных можно изменять данные на одном листе таким образом, что они одновременно изменятся и на других листах. Книга с несколькими листами может быть использована для размещения каждого сегмента данных на разных листах, позволяя выполнять операции вычислений независимо или объединять и связывать данные. Например, если однотипная информация по кварталам размещается последовательно на четырех рабочих листах, то пятый лист может содержать итоговые данные за год. Для суммирования значений по кварталам создается формула связывания данных с использованием ссылок с именами листов. Трехмерные ссылки Excel Excel использует ярлыки листов для определения адреса листа. Имя каждого листа последовательно увеличивается на единицу, начиная с Лист!. Трехмерные формулы включают области, которые охватывают более одного листа, т. е. определяют диапазон листов, содержащих данные. Лист!: Лист2!В11 Квартал!: Квартал4!А1:А10 41. Консолидация данных. Обмен данными в табличном процессоре. При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в корпоративный лист по расходам. Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами. Использование трехмерных формул При использовании в формулах трехмерных ссылок не существует ограничений на расположение отдельных диапазонов данных. Консолидацию можно изменять любыми способами. Консолидация автоматически обновляется при изменении данных в исходном диапазоне. Добавление данных в консолидацию с трехмерными ссылками. Если все исходные листы имеют одинаковую разметку, в трехмерных формулах можно использовать диапазон имен листов. Чтобы включить другой лист в консолидацию, просто переместите лист внутрь диапазона, на который ссылается формула. Консолидация по расположению Консолидацию по расположению следует использовать в случае, если данные всех исходных областей находятся в одном месте и размещены в одинаковом порядке; например, если имеются данные из нескольких листов, созданных на основе одного шаблона. Если установлено автоматическое обновление консолидации при изменении исходных данных, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную. Консолидация по категории Консолидацию по категории следует использовать в случае, если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов. Если установлено автоматическое обновление консолидации при изменении исходных данных, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную. Другие способы объединения данных Можно создать отчет сводной таблицы из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий. Обмен данными позволяет пользователю Excel импортировать в свои таблицы объекты из других прикладных программ и передавать (экспортировать) свои таблицы для встраивания в другие объекты. Концепция обмена данными является одной из основных концепций среды Windows. Между объектами, которые обрабатываются различными прикладными программами, создаются информационные связи, например, между таблицами и текстами. Эти информационные связи реализованы динамически, например, копия таблицы, встроенная в текст, будет обновляться (актуализироваться) всякий раз, когда в ее оригинал вносятся изменения. К сожалению, не все прикладные программы поддерживают механизм обмена данными, но программа Excel относится здесь к лидерам. Она поддерживает новейший стандарт в обмене данными, который называется OLE 2.0 (Object Linking and Embedding). |