КОМПЬЮТЕРНЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ лектор — Аверина Ирина Николаевна

КОМПЬЮТЕРНЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ лектор - Аверина Ирина Николаевна 1 семестр: 4 ч – лк, 8 ч – лр, Экзамен 2 семестр: 4 ч – лк, 8 ч – лр, КР, Экзамен Путь к информационным материалам: U: VT&PMIpkФи. ККит(1 курс)

Темы для самостоятельного изучения в 1 -м семестре • Техническое обеспечение компьютерных информационных технологий • Системное программное обеспечение • Сервисное программное обеспечение • Системы обработки текстовых документов • Системы обработки графической информации. • Системы создания динамических презентаций. Power Point

Темы для аудиторного изучения в 1 -м семестре • Системы обработки табличной информации. MS Excel.

Темы для самостоятельного изучения во 2 -м семестре • Сетевые информационные технологии • Электронная почта • Базы знаний и модели представления знаний • Технологии и инструментальные средства программирования

Темы для аудиторного изучения во 2 -м семестре • Технологии баз данных. MS Access. • Internet/Intranet-технологии. Язык HTML для проектирования WEB-страниц.

§ 1. Адресация ячеек в MS Excel • Относительный адрес, например, D 5 – адрес ячейки A 1: C 12 – адрес блока (диапазона) ячеек Лист2!В 5 – адрес ячейки с Листа 2 Правило относительной адресации ячеек: Excel при копировании формулы с относительным адресом автоматически перенастраивает адрес в соответствии с относительным расположением исходной ячейки и создаваемой копии

• Абсолютный адрес, который создается двумя способами: 1 способ – путем вставки символов $ перед заголовками столбца и строки в относительном адресе, например, $C$5 $A$1: $C$12 2 способ – путем присвоения ячейке или диапазону ячеек собственного имени, например, Скидка – имя ячейки D 5 Цены – имя диапазона ячеек Товары!С 4: С 24 Правило абсолютной адресации ячеек: Excel при копировании формулы с абсолютным адресом оставляет этот адрес в формуле без изменения, т. е. не изменяет ни номер строки, ни номер столбца в адресе

• Смешанный адрес, в котором постоянным является только один из компонентов, например, $C 5 – при копировании формулы не будет изменяться номер столбца C$5 – при копировании формулы не будет изменяться номер строки Правило смешанной адресации ячеек: Если в формулах значение ячейки всегда берется из конкретной строки, значит для копирования исходной формулы в ней знаком $ должен быть зафиксирован номер этой строки в адресе ячейки Если в формулах значение ячейки всегда берется из конкретного столбца, значит для копирования исходной формулы в ней знаком $ должен быть зафиксирован номер этого столбца в адресе ячейки Пример – ТАБЛИЦА УМНОЖЕНИЯ

Изменение типа ссылки для редактируемого адреса производится с помощью клавиши F 4. Действие этой клавиши носит циклический характер: Относительный адрес С 5 F 4 Абсолютный адрес $C$5 F 4 Смешанный адрес $C 5 F 4 Смешанный адрес C$5

Присваивание имени ячейке или диапазону ячеек • • Выделить ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя. 1 способ: Щелкнуть Поле имени, которое расположено слева в строке формул 2 способ: Вставка Имя Присвоить Ввести имя для диапазона. Нажать клавишу Enter.

§ 2. Автоматизация ввода данных в MS Excel • Группирование листов • Использование элементов управления формы для автоматизации интерфейса (поле со списком, переключатели, флажки, кнопки и др. ) • Организация контроля вводимых данных и их выбора из списков допустимых значений • Использование формы для ввода данных в список

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

Если список данных находится на другом рабочем листе, следует задать имя диапазону данных и указать его в поле Источник, нажав клавишу F 3 для выбора нужного имени

Если список формируется из фиксированного набора значений, то этот список надо ввести в поле Источник диалогового окна через точку с запятой

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

§ 3. Понятие функции в Excel Функция производит определение действия с данными и выдает на основе этих действий результат.

• Каждая функция имеет свой набор и тип аргументов. • Большинство функций содержат в скобках один и более аргументов (для некоторых функций до 30). • Аргументы разделяются точкой с запятой.

Аргументами могут быть: числа; ссылки; текст; логические выражения; массивы.

Замечания: • У некоторых функций есть необязательные аргументы. Если такие аргументы опускаются, и они не стоят последними, то следует сохранить соответствующие запятые: Пример: = функц1 (арг 1; арг 2; арг 3 (необязат. арг. ); арг 4) = функц1 (арг 1; арг 2; ; арг 4)

• Если текстовое значение как аргумент используется непосредственно, то нужно заключать его в кавычки, • если используется ссылка на ячейку с текстом, то кавычки не используются. Текстовый аргумент может содержать до 256 символов.

Пример: = Функция(Арг 1; “Привет”; Арг 2) = Функция(Арг 1; В 4; Арг 2) Во втором случае текст Привет находится в ячейке В 4

Ввод функций рабочего листа: • Ввод функций вручную. • Использование кнопки автосуммирование ( ∑ ). • Использование мастера функций.

В Excel имеются следующие основные группы функций: Математические; Статистические; Логические; Ссылки и массивы; Дата и время; Текстовые; Работа с БД; Финансовые; Информационные.

Работа с Мастером функций Вызвать мастер функций можно: 1. Нажав кнопку fx 2. Вставка, Функция

Работа с Мастером функций Мастер функций имеет 2 шага: 1 шаг — выбор функции Выбираем необходимую категорию функции: их всего от 11 и более… (все, 10 недавно использовавшихся, мат. , стат, и т. д. ), а затем конкретную функцию. 2 шаг — задание аргументов функции

Альтернативным способом является ввод аргументов вручную в строке формул. Внимание!!! Несколько аргументов одной функции перечисляются через точку с запятой. Пример!!!

§ 4. Математические функции № Функция Действие Пример Возвращает абсолютное ABS(-75) --> 75 значение числа или формулы. Возвращает значение константы EXP(8, 3) --> 2, 7^8, 3 --> 2 EXP(число) е (~2, 72), возведенной в 4023, 87 заданную степень. Возвращает натуральный 3 LN(число) LN(2) --> 0, 69 логарифм (аргумент >=0). Возвращает логарифм по 4 LOG(число, базис) произвольному основанию LOG(50; 8) --> 1, 88 (аргумент >=0). Возвращает десятичный 5 LOG 10(число) LOG 10(100) --> 2 логарифм (аргумент >=0). Вычисляет факториал числа ФАКТР(4) --> 24, 6 ФАКТР(число) (аргумент >=0). ФАКТР(0) --> 1 Возвращает сумму аргументов (игнорирует аргументы, 7 СУММ(число 1; число 2. . . ) ссылающиеся на пустые ячейки, СУММ(2; 3) --> 5 текстовые или логические значения). ПРОИЗВЕД(число 1; Возвращает произведение 8 ПРОИЗВЕД(2; 3) --> 6 число 2. . . ) аргументов. 1 ABS(число)

§ 5. Тригонометрические функции Функция Действие SIN(X) Возвращает sin в радианах COS(X) Возвращает cos в радианах TAN(X) Возвращает tan в радианах ПИ() Возвращает 3, 14159286.

§ 6. Статистические функции. СРЗНАЧ(число 1; число 2; …) (до 30) – возвращает среднее значение аргумента. Аргументами могут быть диапазоны, значения. Диапазоны могут содержать: числа, ссылки, массивы.

Примеры: =срзнач (А 1: А 3) = 2, если А 1: А 3={1; 3; 2} =срзнач (А 1: А 3; 22) = 7, (1+2+3+22)/4=7

МАКС (число 1; число 2; …) (до 30 аргументов) – возвращает наибольшее значение из аргументов. =МАКС(10; 15; 9) = 15

МИН (число 1; число 2; …) (до 30 аргументов) – возвращает наименьшее из значений аргументов. =МИН(10; 15; 26) = 10

СЧЕТ (знач1; знач2; …) (до 30 аргументов) – возвращает количество чисел среди аргументов. =СЧЕТ(10; ”СТОЛ”; 15) = 2

СЧЕТЗ(знач1; знач2; …) (до 30 аргументов) – возвращает количество непустых значений среди аргументов. Функция считает и текст и числа. =СЧЕТЗ(10; ”СТОЛ”; 15) = 3

§ 7. Логические функции позволяют строить выражения, дающие разные результаты в зависимости от некоторых условий. В мастере функций в категории Логические предлагаются следующие функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ

Категория Логические включает следующие функции: 1. 2. 3. 4. 5. 6. ЕСЛИ(…) И(…. . ) ИЛИ(…. ) ИСТИНА( ) аргументов нет ЛОЖЬ( ) аргументов нет НЕ(…. )

7. 1. Функция ЕСЛИ( ) Синтаксис функции: ЕСЛИ (условие; значение 1; значение 2) функция оценивает логическое условие и если оно истинно, то возвращает в результате значение 1, в противном случае – значение 2.

Окно мастера функций для ЕСЛИ( ) Пример использования логических функций

Схематически это будет выглядеть так: ИСТИНА ЕСЛИ(условие; выражение 1; выражение 2) ЛОЖЬ

ЕСЛИ (условие; выражение 1; выражение 2) Логическое выражение строится с помощью операций отношений (<, >, <=, >=, =, <>) Могут быть: числом; текстом; адресом ячейки; еще раз функцией ЕСЛИ() 41

Функция ЕСЛИ допускает до семи уровней вложения!! В Excel 2007 – до 64 42

Примеры : Ложь = ЕСЛИ(5>7; ”Верно”; “Неверно”) = ЕСЛИ(5>7; ”Верно”; ЕСЛИ(10>8; ”Сейчас. Верно”; ”Неверно”))

Пример1 в Excel

7. 2. Функции И( ), ИЛИ( ), НЕ() Большую гибкость при формировании условий придает использование функций: и ( ), или ( ), не ( ). и (условие 1; условие 2; …) (не более 30 условий) – возвращает истина, если истина все аргументы и ложь, если хотя бы один аргумент ложь. Функцию И() еще называют функцией логического умножения. или (условие 1; условие 2; …) – возвращает истина, если истина хотя бы один из аргументов и ложь, если все ложь. Функцию ИЛИ() еще называют функцией логического сложения. не (условие) - возвращает истина, если аргумент ложь и ложь, если аргумент истина. Функция НЕ() называется функцией отрицания, т. к. ее значение всегда противоположно значению аргумента. Примеры.

и (условие 1; условие 2; …) возвращает : истина, если истина все аргументы и ложь, если хотя бы один аргумент ложь. 46

Примеры: И(6>5; -2<0; 3<>1) --> ИСТИНА И(6<5; -2<0; 3>1) --> ЛОЖЬ Функцию И() еще называют функцией логического умножения. (1*1*1*0*…. *1) 47

или (условие 1; условие 2; …) возвращает: истина, если истина хотя бы один из аргументов и ложь, если все ложь.

Примеры: =ИЛИ(6>5; 2=0; 3>10) -> ИСТИНА =ИЛИ(6<5; 2=0; 3>10) --> ЛОЖЬ Функцию ИЛИ() еще называют функцией логического сложения. (0+0+0+1+…. +0)

не (условие) - возвращает : истина, если аргумент ложь и ложь, если аргумент истина. Функция НЕ() называется функцией отрицания, т. к. ее значение всегда противоположно значению аргумента. 50

Примеры: =НЕ(6>5) --> ЛОЖЬ =НЕ(1+2=3) --> ЛОЖЬ =НЕ(6<5) --> ИСТИНА =НЕ(1+2=4) --> ИСТИНА 51

Примеры: =ЕСЛИ(И(5>3; 7<11); 100; 200) =ЕСЛИ(И(5>3; 7>11); 100; 200) =ЕСЛИ(ИЛИ(5<3; 7>11); 100; 200) =ЕСЛИ(ИЛИ(5>3; 7<11); 100; 200) =ЕСЛИ(И(5>3; 7<11; ИЛИ(4<2; 1=1); 100; 200)

Пример2: Рассчитать для студента: 1. повышенную стипендию в случае, если средний экзаменационный балл больше 8 баллов и число пропусков занятий меньше 5%, 2. обычную стипендию в случае, если средний балл больше 6 баллов и пропуски меньше 10 %, 3. в противном случае студент стипендию не получит.

Пример2 в Excel

Пример3 (Использование функции ЕСЛИ() в экономических расчетах Рассчитать скидку для покупателей по следующему правилу: если стоимость купленного товара до 100$ скидки нет; если >= 100$ но < 200$ скидка 5%; Если >= 200$ но < 400$ скидка 12%; если >= 400$ скидка 18%.

Таблица истинности логических функций. А не(А) не(В) ИСТИНА ЛОЖЬ ИСТИНА ЛОЖЬ ИСТИНА ЛОЖЬ ЛОЖЬ В ЛОЖЬ ИСТИНА ИЛИ(А, В)

7. 3 Функция СЧЕТЕСЛИ() Данная функция подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Синтаксис функции: СЧЁТЕСЛИ( диапазон; критерий) Диапазон — ячейки, которые нужно проверить на критерий; Критерий — аргумент представлен в форме числа, выражения или текста 58

7. 4 Функция СУММЕСЛИ( ) Данная функция суммирует ячейки, которые удовлетворяют заданному критерию. Синтаксис функции: СУММЕСЛИ(диапазон ; критерий; диапазон суммирования) 60

Диапазон — диапазон ячеек, которые проверяются на критерий. Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Диапазон_суммирования — диапазон ячеек для суммирования.

ЗАМЕЧАНИЕ: В случае, если мы имеем в наличии несколько критериев – предыдущие функции не работают. Необходимо использовать сочетание функций СУММ() и ЕСЛИ(). Этот недостаток устранен в версии Excel 2007 за счет добавления новых функций: СЧЕТЕСЛИМН( ) СУММЕСЛИМН( )

§ 8. Функции Даты в Excel Любую дату и время Excel хранит и обрабатывает как число с целой и дробной частью. Целая часть числа - это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. Дробная часть, соответственно, доля от суток (1 сутки = 1, 0) 64

Вывод: с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они - числа!

Функции обработки дат. СЕГОДНЯ ( ) – возвращает текущую системную дату компьютера. Пример: =СЕГОДНЯ ( ) 22. 09. 2014 ТДАТА ( ) – возвращает текущую системную дату и время. Пример: =ТДАТА ( )

=ДАТА (год; месяц; день) – возвращает дату из отдельных ее компонентов. Пример: =ДАТА(2012; 10; 18) 18. 10. 2012

=ДНЕЙ 360(нач_дата; кон_дата) – вычисляет количество дней между датами на основе 360 дневного года. Пример:

=ДЕНЬНЕД(дата; 2) – возвращает номер дня недели. Первый день недели – Понедельник. Если 2 -й аргумент не указывать или 1, то первый день недели- Воскресенье и функция записывается: =ДЕНЬНЕД(дата) или =ДЕНЬНЕД(дата; 1)

Примеры:

=ГОД (дата) – возвращает год указанной даты Пример: =ГОД(сегодня()) 2012 =МЕСЯЦ (дата) – возвращает месяц Пример: =МЕСЯЦ(сегодня()) 10 =ДЕНЬ(дата) – возвращает день месяца Пример: день (сегодня()) 18 72

=ДАТАЗНАЧ (дата_как_текст) – преобразует текстовую форму даты в числовую. Пример: =ДАТАЗНАЧ (“ 18. 10. 12”) = 41200 Число 41200 и дата 18. 10. 12– одно и то же, т. е. дата может быть отображена и в числовом формате и в формате даты. 73

ТЕКСТ(дата; параметр) Параметр может быть: “ддд” - (краткое название дня недели); “дддд” - (полное название дня недели);

Работа с функцией в Excel:

Задачи с использованием функций Дата и время: • Определить возраст сотрудника; • Определить стаж сотрудника; • Определить количество лет, месяцев и дней между датами.

§ 4. Функции для работы со справочными таблицами В мастере функций в категории Ссылки и массивы находится ряд функций для поиска заданных значений в определенной области и выбора из нее соответствующего результата. Тем самым эти функции предназначены для автоматизации работы со справочными данными, которые оформлены в таблице. ПРОСМОТР( ), ПОИСКПОЗ( ), ВПР( ), ГПР( )

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

4. 1. ФУНКЦИЯ ПРОСМОТР() Синтаксис векторной формы ПРОСМОТР(искомое_знач; диап_просмотра; диап_результата)

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

ВНИМАНИЕ!!!: Значения аргумента: Вектор _ просмотра и первый столбец или первая строка аргумента массив должны быть упорядочены в возрастающем порядке.

Действия функции ПРОСМОТР( ) Функция ищет в просматриваемом векторе наибольшее значение, не превышающее искомого и выбирает результат из соответствующей позиции в диапазоне результата. Если искомое значение меньше 1 -го значения диапазона просмотра, функция ПРОСМОТР( ) возвращает сообщение об ошибке #Н/Д

Примеры использования функции Просмотр( ): • Курсы валюты • Калькуляция меню

4. 2. ФУНКЦИЯ ПОИСКПОЗ( ) Синтаксис: ПОИСКПОЗ(искомое_знач; область_поиска; режим_поиска)

Искомое значение – текст, число, дата или адрес ячейки, значение которой должно быть найдено в диапазоне просмотра Область поиска (просматриваемый массив) – непрерывный интервал строки или столбца, возможно, содержащих искомые значения Режим поиска (тип сопоставления) – может принимать числовые значения: 1 искомого значения. Область поиска должна быть упорядочена по возрастанию. 0 - функция ищет первое значение точно равное искомому. Область поиска может быть в любом порядке. -1 - функция ищет наименьшее значение, которое равно и больше искомого. Область поиска должна быть упорядочена по убыванию. Если тип_сопоставления опущен, считается, что он = 1

Функция ПОИСКПОЗ – осуществляет поиск позиции (ячейки) в заданной области со значением, точно или близко равным искомому (в зависимости от режима писка). Результатом функции ПОИСКПОЗ является не само найденное значение (оно нам известно), а его номер в области поиска. Функция ПОИСКПОЗ возвращает число – номер найденной позиции в области поиска.

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

4. 3. ФУНКЦИИ ВЕРТИКАЛЬНОГО и ГОРИЗОНТАЛЬНОГО просмотра ВПР(искомое_знач; обл_поиска; номер_столбца; реж_поиска) ГПР(искомое_знач; обл_поиска; номер_строки; реж_поиска)

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

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

Замечания: 1. Функция ВПР используется, когда в справочной таблице ключевым для поиска является первый столбец, функция ГПР – когда ключевой является первая строка. 2. Функции ВПР и ГПР можно использовать не только для работы с интервальными справочниками, но и когда в ключевом столбце (строке) справочной таблицы находятся текстовые значения Примеры использования функций ВПР и ГПР

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