Создание линейных последовательностей

Активная ячейка в правом нижнем углу имеет маленький черный квадратик. Он называется маркер заполнения. С его помощью можно автоматически создавать последовательности с заданным шагом. Например,

в ячейку А1 ввести число 1, в А2 - 2

- выделить эти ячейки;

- маркер заполнения переместить вниз до ячейки А20;

- отпустить кнопку мыши.

В результате в ячейке А3 появится число 3, в А4 – 4 и т.д. в А20 - 20.

Если нужно заполнить диапазон одинаковыми значениями, перемещая маркер заполнения, нажать Ctrl.

Создавая последовательности можно менять шаг, например, в А1 - 1; в А2 - 3, тогда в А3 будет 5.

Можно создавать последовательности и текстовой информации, например месяцы года, дни недели…

Работа с формулами

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

В формулах можно использовать скобки, арифметические действия: + сложение, - вычитание, * умножение, / деление, ^ возведение в степень, %- деление содержимого ячейки на 100; операторы сравнения: равно =, не равно<>, больше >, меньше <, не больше <=, не меньше >=, специальные логические операторы: логическое отрицание NOT, логическое “и”AND, логическое “или”OR. Логические операции возвращают результат истина или ложь.

Например: формула = А12<15 даст результат истина, если в А12 значение<15, иначе - ложь.

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на одну строку ниже и на один столбец левее данной. Если формула будет скопирована в другую ячейку, то относительное указание ссылки сохранится. Например, при копировании формулы в ячейку D5 ссылка будет продолжать указывать на ячейку, располагающуюся ниже и левее, в данном случае на ячейку С6.

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Элементы номера ячейки, использующие абсолютную адресацию начинают со знака $. Например, $А$1 – полная абсолютная адресация, $А1, А$1 – частичная абсолютная адресация, то есть здесь один из компонентов рассматривается как абсолютный, другой как относительный.

Пример: = ( А1+В$1)/ $С1

Для того чтобы ввести формулу:

1. курсор расположить в ячейке, куда необходимо поместить результат;
2. ввести формулу, начав со знака =;
3. нажав “Enter”, в ячейке получим результат вычисления;

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

Примеры формул:

= А1+ В1

= А1 * В1

= А1% (если в А1-5, то результат = 0,05 )

= А1- В1

= А1 / В1

= А1 ^ 2 (если в А1-5, то результат = 25 ).

Для редактирования формулы используют клавишу F2.

Чтобы вывести на экран все использованные в таблице формулы, то есть получить формульный вид таблицы: меню Сервис=>Параметры => Вид => Формулы.

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

В формулах можно ссылаться на ячейки других листов, например: Лист 6! А1, можно использовать трехмерные ссылки, чтобы ссылаться на диапазон ячеек из нескольких листов: (Лист1:Лист 6 ! А1 :А10)

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

Например, мы рассчитываем курс валют:

в ячейке А1 - находится цена в ПМР (60)

в ячейке А2- курс доллара к ПМР (6,0)

в ячейке А3 - необходимо рассчитать цену в долларах, и добавить текст, что это усл. ед., тогда в ячейку А3 вводится формула:

= А1 / А2 & “у.е.” и в итоге в А3 получим результат 10 у.е.

При изменении курса, будет меняться значение в А3.

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

Пример: в ячейке А1 введен текст ИТ01, в А2 – ИТ02.

В ячейке А4: = “Лучшие студенты в группах” & A1 & “и” & A2

& -используется для объединения текста, ячеек, диапазонов, причем текст обязательно заключать в кавычки.

Если формула введена неверно, или Excel не может вычислить, он выводит сообщение об ошибке, сообщение начинается со знака #.

Вычисления в массивах

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

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

Пример: Необходимо значение в ячейках с А1 по А300 умножить на соответствующие значения с В1 по В300, результат поместить в С1 по С300.

Можно ввести в С1 формулу = А1 * B1, а затем скопировать ее в диапазон с С2 : C300.

Но, чтобы сэкономить память, введем в С1 формулу = А1:А300 B1:B300 и нажмем комбинацию Shift +Ctrl +Enter.

Произойдет 300 перемножений и результаты занесутся в C1:C300. В каждой ячейке этого диапазона появится формула = {A1:A300*B1:B300}

Отдельные ячейки в массиве не подлежат редактированию. Редактируется весь массив с помощью клавиши F2, изменяется формула и затем: Shift+Ctrl+Enter.

Использование функций

Помимо формул в Excel при расчетах можно использовать встроенные функции. В Excel встроено более 200 функций.

Формат записи: = Имя функции (список аргументов).

Каждая функция имеет свой набор аргументов. Аргументами могут быть числа, ссылки, текст, логические выражения. Некоторые функции могут иметь до 30 аргументов.

Чтобы вызвать список функций, на панели инструментов выбирается Мастер функций Fx либо меню Вставка => Функция

Открывается диалоговое окно, содержащее:

- категорию функций (классификация);

- имя функций (по каждой категории);

- подсказка о текущей функции (на которой находится курсор);

В Excel имеются следующие категории функций:

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

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

ü Текстовые выполняют операции над текстовыми строками.

ü Логические используются для построения логических выражений, результат которых зависит от истинности проверяемого условия. Например, = Если С1>D1*B1; «УРА!»; «УВЫ».

ü Финансовые используются в сложных финансовых расчетах, например, определение нормы дисконта, размера ежемесячных выплат для погашения кредита и др.

ü Последние – это 10 последних использованных функций.

ü Все - все функции в алфавитном порядке.

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

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

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

Необходимо сначала выделить необходимый диапазон. Выбрать меню Данные=> Сортировка. Откроется диалоговое окно, в котором задаются ключи сортировки, порядок сортировки и различные Параметры.

Вне зависимости от порядка сортировки, пустые ячейки помещаются в конец данных.

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

Фильтрация данных

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

Для фильтрации данных таблицы необходимо: выделить всю таблицу, выбрать меню Данные => Фильтр => Автофильтр.

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

Отменить фильтрацию можно через меню Данные =>Фильтр => Автофильтр.

Можно производить фильтрацию по нескольким критериям. Для этого в раскрывшемся списке критериев столбца выбрать параметр Условие. Откроется диалоговое окно Пользовательский автофильтр. Для критерия отбора можно использовать условия больше, больше или равно, меньше, меньше или равно, равно, не равно, а также операторы И и ИЛИ.

Для фильтрации по сложному комплексному критерию используется меню Данные=> Фильтр => Усиленный фильтр. Чтобы использовать Усиленный фильтр, сначала создается таблица критериев на свободном месте таблицы (копируются имена нужных столбцов).

Пример:

№ группы	Информатика
	>88

Затем, выбрав команду Усиленный фильтр, в открывшемся окне задается Исходный диапазон, который нужно отфильтровать, и Диапазон условий (включая имена столбцов). В строке “Обработка” указывается выполнять фильтрацию на месте или перенести выходные данные в другое место рабочего листа.

При фильтрации используются также вычисляемые критерии.