Лекция 11. Расчеты на листе Excel

Вид материала

Лекция

Содержание Главное меню (словами; остальное – панели инструментов) Время – набор цифр, разделенных двоеточием. Если в позиции часов указано больше 24, а в позиции минут – больше 60, образуется ТЕ Ввод констант Ввод ссылок Ввод формул Ввод функций Редактирование функций и формул

Подобный материал:

• Рабочая программа предпрофильного курса «Экономические расчеты в ms excel», 89.35kb.
• Построение графиков в excel, 17.52kb.
• Лекция №6 от 4 марта 2000 года, 195.1kb.
• Программа дисциплины Статистические расчеты в ms excel для специальности 080500., 71.81kb.
• Лекция №1 Тема: Создание тестов с использованием Microsoft Excel, 22.51kb.
• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Реферат по дисциплине: Информатика На тему: «Тенденции развития Интернет», 244.7kb.
• Предмет: макроэкономика, 9.74kb.
• Введение в Excel Цели, 81.13kb.
• Курс: I семестр: 1 Кол-во часов: лекций, 21.65kb.

Информатика 11

Лекция 11. Расчеты на листе Excel


Ключевые слова настоящей лекции

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

Структура меню Excel

Пуск \ Настройка \ Панель задач и меню «Пуск» \ Использовать сокращенные меню)

Главное меню (словами; остальное – панели инструментов)

Файл \ Сохранить как …

Правка \ Специальная вставка

Правка \ Заменить

Вид \ Панели инструментов

Вставка \ Лист

Формат \ Ячейки

Сервис \ Автозамена

Сервис \ Надстройки \ Пакет анализа

\ Поиск решения

\ Настройка \ Панели инструментов \ Создать…

\ Настройка \ Панели инструментов \ Команды…

\ Параметры \ Общие \ Список ранее открывавшихся файлов

\ Листов в новой книге

\ Стандартный шрифт

\ Размер

\ Рабочий каталог

Данные \ Сортировка

Фильтр

Данные \ Текст по столбцам,

\ Сводная таблица

Окно \ Новое

\ Закрепить области


Электронный лист Excel

Ячейки Excel могут содержать

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

Типы данных (констант)

В ячейки Excel могут быть помещены данные разных типов, которые требуют для себя разные объемы памяти и обрабатываются различным образом. Так что попытка обработать определенный тип данных не «своими» операторами приводит к ошибке или неопределенному результату. Например, ДАТА 10.02.1996, приведенная в формату ЧИСЛО, получает вид 35105.00, который не имеет никакого смысла. Все типы данных представлены в окне Формат \ Ячейки. С помощью этой команд можно менять типы данных. Это бывает необходимо, если произошло неправильное определение типа данных (довольно часто). Для биологов чаще приходится иметь дело с 5 типами данных (данные – это значения, но не ссылки, функции, операторы).

Общий – формат, который Excel определяет самостоятельно, ориентируясь на внешние свойства вводимых с клавиатуры (или экспортируемых) значений. При вводе числа, точки, числа с одной точкой Excel решает, что это ЧИСЛО. При вводе числа с двумя точками, Excel решает, что это ДАТА. При вводе числа, разделенного двоеточием определяется тип ВРЕМЯ. После ввода слов истина или ложь они преобразуются с тип ЛОГИЧЕСИКИЙ. При вводе первого символа в виде букв, пробела, кавычек, апострофа Excel решает, что это ТЕКСТ; числа с более чем двумя точками или с необычными значками также распознается как ТЕКСТ. Введенные значения Excel располагает в ячейке определенным образом: текст – с выравниванием влево, число, дату, время – с выравниванием вправо, логическое – по центру.

Числовой – набор цифр, разделенных или не разделенных десятичной точкой (число цифр после запятой задается в окне Формат \ Ячейки \ Число).

Дата – набор цифр, разделенных двумя точками. Если в позиции дней стоит число больше 31 или в позиции месяцев указано число больше 12, вводимые символы распознаются как ТЕКСТ.

Время – набор цифр, разделенных двоеточием. Если в позиции часов указано больше 24, а в позиции минут – больше 60, образуется ТЕКСТ.

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

Логический – этот тип данных имеет только два значения ИСТИНА и ЛОЖЬ. Значения записывается в ячейки или функции без кавычек (строчные буквы автоматически преобразуются в прописные).


Ввод констант

Выполняется вручную с клавиатуры, в том числе с использование буфера обмна.


Ссылки

Ссылки (адрес) – координаты столбца и строки ячейки, первой и последней ячеек для блока.

Ссылки – это адреса ячеек листа Excel, имеющие формат:

Индекс_столбцаНомер_строки, например, F5, ER567.

Число строк на листе Excel – 65536, число столбцов – 256. Поскольку букв в английском алфавите всего ничего, 28, индексы столбцов образуются из двух букв и доходят до IV (9*28). Есть два типа ссылок:

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

абсолютные ссылки – при копировании в другие ячейки не изменяют индекс столбца и номер строки; эти ссылки указывают на одну и ту же ячейку (строку или столбец). Чтобы зафиксировать индекс столбца или номер строки, перед ними нужно поставить знак доллара $.

Варианты при копировании:

B5 – меняется и номер и индекс,

$B5 – номер меняется, индекс сохраняется,

B$5 – номер сохраняется, индекс меняется,

$B$5 – и номер и индекс сохраняются.

В ячейке, содержащей ссылку высвечивается то значение, которое присутствует по адресу ссылки (в той ячейке, на которую ссылаются).


Ввод ссылок

Относительные ссылки вводятся вручную (в латинском регистре) или с помощью мыши. Для этого необходимо, чтобы в ячейке, куда хочется вставить ссылку на другую ячейку, на первой позиции стоял знак равно =, который означает режим ввода формул и функций. Для придания ссылке статуса абсолютной, приходится вводить знак $ перед индексами столбцов и строк. Упростить ввод можно, если ввести на панель инструментов значок $ из меню Сервис \ Настройка \ Команды \ Вставка \ “$” знак доллара.

Очень эффективен способ автозаполнения (копирование серии ячеек при протаскивании мышкой, помещая курсор над правым нижним углом).


Формулы

Выражения, записанные в ячейке и возвращающие результаты некоторых расчетов, действий. Начинаются со знака =, содержат константы, переменные (ссылки на другие ячейки) и функции, связанные операторами математических (* + / - = ( ) ), текстовых (& ) и логических (>, =>, or, and…) выражений.

Если в ячейке записана формула или функция, говорят, что она (функция, формула) возвращает результат такого-то действия, т. е. в ней выполняются какие то расчеты, а результат высвечивается в ячейке. Если мы видим в ячейке число 3 это может быть и константа 3, и ссылка на другую ячейку с числом 3, и результат расчета по формуле =18/6 и пр.

Если в формулу введено какое-либо конкретное значение (число 3, 4.56..., текст «привет», логическое выражение ИСТИНА...), то говорят, что это –константа, поскольку они остаются неизменными до тех пор, пока мы сами их не поменяем.

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


Ввод формул

Ввод выполняется вручную; для этого в выбранную мышкой ячейку вводится знак = (равно) и далее без пробелов набираются значения констант, операторы, функции, ссылки. Ссылки удобно водить, кликая мышью на ячейки, содержащие нужные величины. Функции допустимо вводить, используя кнопку fx, расположенную в командной строке, заполняя предлагаемые поля. Однако лучше всего функции вводить вручную, в том числе используя возможности автозамены.

Очень эффективен способ автозаполнения формулами серии ячеек (протаскивание мышкой, помещая курсор над правым нижним углом). Следует помнить, что при этом относительные ссылки меняют адрес ячейки, а абсолютные – нет.


Функции

Стандартные процедуры обработки информации, в среде Excel играют роль элементов программирования (наряду с константами и выражениями). Функции располагаются в одной ячейке и записываются в формате Visual Basic for Application – специального языка программирования в среде Excel.

Запись функции в ячейке имеет три компонента

• знак равно =,
  
• имя функции (без пробелов)
  
• список параметров, заключенных в скобки.
  

Большинство имен функций имеет кириллическое написание, хотя есть и на латинице.

В списке параметров могут быть

• константы (числа, «текст», обязательно заключенный в кавычках, логические значения ИСТИНА и ЛОЖЬ, даты и др.),
  
• ссылки на соседние ячейки или диапазоны ячеек, в которых содержатся анализируемые данные, или – переменные,
  
• арифметические, текстовые, логические и пр. операторы (включающие знаки операций +, -, , &, >, =>, (, ),…), которые обеспечивают выполнение специфического действия со значениями формулы,
  
• другие функции со своими параметрами, в том числе одноименные (рекуррентный вызов).
  

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

Результатом выполнения функции является появление в той ячейке, куда была записана функция, некоего рассчитанного значения. Внешне процесс выглядит так, что автор что-то вложил в ячейку электронного листа (памяти Excel) и получил обратно некоторое значение (число, текст и пр.) – компьютер ему вернул результат запроса. По этой причине принято говорить, что функция возвращает то или иное значение.

Если в результате выполнения функции произошла ошибка, функция возвратит одно из нескольких сообщений, позволяющих определить тип ошибки. Для определения типа служит функция =ТИП.ОШИБКИ(значение_ошибки)

Значение_ошибки	Номер	Причина
########	0	Значение ошибки ##### выводится в том случае, когда ячейка содержит число, дату или время, число знаков которого больше ширины ячейки, или в том случае, если ячейка содержит дату и/или формулу, которая выводит отрицательный результат.
#ПУСТО!	1	Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек.
#ДЕЛ/0!	2	Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на ноль (0).
#ЗНАЧ!	3	Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда,
#ССЫЛКА!	4	Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку.
#ИМЯ?	5	Ошибка #ИМЯ? появляется, когда Microsoft Excel не может распознать имя, используемое в формуле.
#ЧИСЛО!	6	Ошибка #ЧИСЛО! появляется, когда возникают проблемы при использовании чисел в формуле или функции.
#Н/Д	7	Значение ошибки #Н/Д является сокращением термина “Неопределенные Данные”.
Любое другое	#Н/Д

Ввод функций

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

Распространены четыре варианта ввода функций в ячейки.

• вставка стандартной функции нажатием на кнопку f_x в строке формул (или командой Вставка \ Функция); в появившемся окне выбирается нужная функция из нужной категории и заполняется окно функции;
  
• ввод функции вручную; для этого в выбранную мышкой ячейку вводится знак = (равно) и далее без пробелов набирается имя функции и вводятся значения параметров, в том числе с помощью мыши;
  
• копирование функции из одной ячейки в другую, в том числе с помощью операции «автозаполнение»; при этом следует помнить, что ссылки без префикса $ будут пересчитаны;
  
• ввод с помощью «автозамены»; если приходится часто пользоваться одними и теми же функциями, например, =срзнач(, то имеет смысл заменить их побуквенный ввод какой-либо аббревиатурой, состоящей из пары прописных букв (СР), прописав ее в Сервис \ Параметры автозамены; вводить функцию в окно автозамены следует с одной открытой скобкой, чтобы иметь возможность быстро начать ее редактирование; набрав нужную аббревиатуру (СР) следует нажать пробел, тогда после замены аббревиатуры на нужный текст функции курсор попадает внутрь скобки, и появляется возможность сразу вводить параметры, включая использование мыши.
  

Редактирование функций и формул

В Excel эффективно работают функции, обрабатывающие результаты работы вложенных функций, по этой причине для организации одной функции приходится ее многократно редактировать. Оптимально начинать редактирование с нажатия клавиши F2. В этом случае все ячейки, ссылки на которые содержаться в параметрах функции, будут оконтурены разноцветными рамками. Это позволяет быстро менять ссылки, просто перемещая мышкой такую рамку (взяв за грань) на другие нужные ячейки.

Для закрепления сделанных изменений в ячейке следует нажать Enter.

Часто требуется в текст одной функции внести имя и список параметров (текст) другой функции. Процедура выполняется в два приема: входим в тело второй функции (F2), выделяем нужный текст, копируем в буфер обмена (Ctrl+C), выходим из ячейки (Esc), входим в тело второй функции (F2), вставляем текст (Ctrl+V).

Для выхода из тела функции без изменений следует нажать Esc.


Список важных функций (с точки зрения биолога)

Категория	Имя и формат функции	Возвращает
Дата, Время	год(значение) месяц(значение) день(значение) сегодня() час(значение) дней360(нач, кон, метод)	из даты – число год из даты – число месяца из даты – день недели число сегодня из времени – число час из даты – число дней метод 1
Математические	abs(значение) sin(число) exp(число) ln(число) log10(число) log(число,основание) знак(число) корень(число) округл(x,n_знаков) целое(число) остат(число,делитель) пи(число) слчис() степень(число) сумм(диапазон) суммесли(индекс, критерий, диапазон сумм)	ликвидирует знак синус угла ex натуральный логарифм десятичный логарифм любой логарифм -1,0,1 корень, то же что и x0.5 до n знаков после запятой убирает знаки после запятой остаток от частного, знак от делителя 3.14… случайное равномерное от 0 до 1 xa сумма чисел сумма чисел в столбце, но только тех, для которых индекс в другом столбце совпадает с критерием
Статистические	макс(диапазон) мин(диапазон) медиана(диапазон) срзнач(диапазон) ранг(число, ссылка, порядок) счет(диапазон) счетесли(диапазон, критерий) считатьпустоты(диапазон)	наибольшее из диапазона наименьшее из диапазона медианное из диапазона среднее из диапазона ранг в диапазоне всего чисел в диапазоне (не текст) подсчет чисел в столбце, но только тех, для которых индекс в другом столбце совпадает с критерием
Текстовые	значен(текст) кодсимв(текст) найти(искомый текст, просматриваемый, первая позиция) пстр(тескт, нач. поз, число симв)	текст в число код первого номер позиции вхождения искомого текста в просматриваемый фрагмент текста
Логические	если(лог_выражение, если Истина действие 1, если Ложь действие 2)	проверка логического выражения и выполнение действия 1 или действия 2
Проверка свойств	еош(ссылка) епусто(ссылка) етекст(ссылка) ечисло(ссылка) тип.ошибки(ссылка)	наличие ошибки вообще (ИСТИНА) пустота с ячейке (ИСТИНА) текст с ячейке (ИСТИНА) число с ячейке (ИСТИНА) номер ошибки (см. выше)