Редактор электронных таблиц «Microsoft Excel» (стр. 2 )

В большинстве случаев не приходится напрямую иметь дело с системой последовательной нумерации дат. Дата вводится в привычном формате. Предположим, вам надо ввести дату 1 июня 1999 года. Набираете 1.06.1999 (или ту же дату в другом формате) и Excel сразу воспринимает это число как дату, а его числовое значение сразу переводит в 36312.

Как можно вводить дату: 1-6-99, 1-6-1999, 1/6/99, 1-6/99, 1 июнь 1999 (будет 1 июня 1999 года). 1.06, 1-6, 1/6, 1 июнь – будет 1 июня текущего года.

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

При вводе времени Excel расширяет свою систему представления дат так, чтобы она содержала дробную часть. Другими словами, воспринимает время как дробную часть даты. Например, 1 июня 1999 года – 36312. Тогда 12 часов дня 1 июня 1999 года будет 36312,5. Время обычно вводится так: «11:30» -- 11 часов 30 минут утра. Чтобы ввести 23 часа 30 минут, надо ввести «23:30» или «11:30 pm».

Комбинированно дату-время можно ввести так: 1-6-99 11:30.

Автоматическое заполнение. Иногда требуется заполнять большой диапазон однотипными данными. Иногда эту задачу можно переложить на Excel, воспользовавшись функцией автоматическое заполнение. Например, пронумеровать строки, поставить идущие подряд даты и т. д.

________________________________________________________________________________

Формулы.

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

Чтобы начать вводить формулу, надо начать вводить со знака «=», либо сразу нажать кнопку «изменить формулу» в строке формул.  Например, если вы введете в ячейку «1+2», то увидите в ячейке просто «1+2». Если же вы введете «=1+2», то увидите в ячейке «3». В строке ввода формул вы по-прежнему будете видеть «=1+2», чтобы знать, по какой формуле происходило вычисление.

Итак, мы увидели, что формулы могут содержать некоторые данные (числа 1 и 2) и знаки арифметических действий: +, -, *, /, ^(возведение в степень).

Что еще могут содержать формулы?

Ссылки.

Очень часто в таблице содержимое одной ячейки зависит от содержимого других ячеек. Для этого используют ссылки. Например, если содержимое ячейки b1 должно быть равно содержимому ячейки a1, просто напишите в ячейке b1 “=a1”. Если вы захотите изменить содержимое ячейки a1, содержимое ячейки b1 заменится автоматически. Чтобы вставить ссылку в вашу формулу, можно просто в процессе ввода формулы кликнуть мышкой по нужной вам ячейке.

Ссылки бывают абсолютные и относительные. Поймем на примере, что это такое. Когда вы пишите в ячейке b2 формулу “=a1”, на самом деле Excel понимает это так: взять значение из клетки, которая находится на одну строчку выше и на один столбец левее. Если мы скопируем информацию из ячейки b2 и вставим в ячейку с8, то в ячейке c8 значение будет равно числу в ячейке b7 (Excel подумает, что вам нужна не клетка a1, а клетка на одну выше и на одну левее, чем c8, т. е. b7). Такие ссылки называются относительными (Excel помнит не название ячейки, а ее относительное расположение). На самом деле,  это бывает очень удобно. Например, в столбике L вы хотите посчитать сумму чисел из столбиков B:K. Вы должны только в ячейке L1 посчитать сумму в ячейках b1:k1, а потом просто скопировать значение на все нижние ячейки столбика L (заполнить вниз).

Но иногда все-таки нужно ссылаться именно на конкретную ячейку. (Нужна абсолютная ссылка). Тогда вместо ”a1” (что создало бы относительную ссылку), напишите “$a$1”. Теперь Excel будет помнить именно название ячейки, а не ее относительное расположение. И при копировании формулы «=$a$1» в другую ячейку, там появится тоже «=$a$1».

Итак, при абсолютной адресации Excel запоминает конкретное расположение ячейки, на которую мы ссылаемся. При отоносительной – отоносительное расположение ячейки (на которую происходит ссылка) относительно данной (из которой идет ссылка).

Примечание: Если надо зафиксировать только строку, а для столбца оставить относительное расположение, поставьте знак $ только возле названия строки (т. е. напишите “a$1”). Это называется смешанная адресация.

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

Функции.

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

Примеры упрощения работы в Excel с помощью функций.

Например, вам надо вычислить среднее количество продаваемого за день. Данные о продажах у вас находятся в ячейках А1:А10. Можно это сделать по формуле: «=(а1+а2+а3+а4+а5+а6+а7+а8+а9+а10)/10». Это не очень удобная формула. Например, если появятся новые данные, то формулу будет исправить нелегко. А если бы у нас были сразу данные не за 10 дней, а за 100, 1000? Для того, чтобы упростить весь процесс, вместо неудобной формулы будем вводить формулу «=СРЗНАЧ(а1:а10)», которая тоже вычислит среднее значение в диапазоне а1:а10, но с которой гораздо удобнее работать.

Иногда с помощью функций можно избежать долгого ручного редактирования.  Предположим в вашей рабочей таблице содержится список из 2 тысяч фамилий, но они все введены как попало. Какие-то – прописными буквами (ИВАНОВ), какие-то строчными (иванов), а в каких-то вообще строчные буквы чередуются с заглавными (ИвАнОв). Вы сами понимаете, что в таком виде хранить список нельзя. Надо бы ввести все фамилии в нормальном виде (первая буква – заглавная, остальные – строчные: Иванов). На редактирование списка вручную ушло бы много часов!  Мы же воспользуемся функцией. Чтобы изменить написанное в ячейке а1, введем в ячейку b1 формулу «=ПРОПНАЧ(а1)». Достаточно ввести эту формулу 1 раз и растянуть на 1999 оставшихся строк. В столбце b у вас уже получатся фамилии в нужном виде. Теперь достаточно скрыть столбец а, чтобы его не было видно на печати.

Рассмотрим еще один пример. У вас – контора, в которой работает 1000 продавцов. Если продавец реализовал продукции более, чем на 10 000 рублей, то ему положена комиссия 7%, а если меньше, чем на эту сумму, то всего 5%. Чтобы просто так вычислить это значение, вы должны просматривать весь список продавцов. Одним подставлять значение 5%, другим – 7%. Но с помощью функций это сделать намного легче. Пусть стоимость реализованного написана в ячейке c1. В ячейке d1 надо ввести комиссию. В ячейку d1 вводим формулу: «=ЕСЛИ(с1>10000;0,07;0,05)». И ставим формат ячейки d1 – процентный.  Осталось только растянуть эту формулу вниз по оставшимся 999 продавцам и Excel сам вычислит комиссию.

Аргументы функций.

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

1. Без аргументов. Например, функция ПИ().

2. Функции с одним аргументом. Например, функция «КОРЕНЬ(x)».

3. С фиксированным числом аргументов. Например, у функции ЕСЛИ 3 аргумента. На первом месте стоит условие, потом  значение, которое появляется, если условие истинно, потом значение, которое появляется, если условие ложно.

4. С неопределенным числом аргументов. Например, функция СУММ. Можно вставить в качестве аргумента диапазон любой длинны.

5. Функции с необязательными аргументами.7

Если в функции используется несколько аргументов, они отделяются друг от друга точкой с запятой (;). В качестве аргументов можно вводить данные, ссылки, и более простые формулы. Например, «=сумм(корень(а1)+корень(а2)-3)»

Способы ввода функций.

Есть 2 основных способа ввода функций. Во-первых, можно вводить функции вручную. Это бывает удобно, если часто используешь некоторые функции. Но с другой стороны, надо помнить все функции, с которыми работаешь и т. д. Второй способ – вводить функции с помощью Мастера функций.

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

Матричные вычисления.

У Excel’я есть богатые возможности работы с матрицами.
Матрица – это просто диапазон данных.
Для того, чтобы матричные функции действовали корректно, надо:

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

2. Ввести нужную формулу

3. Нажать клавиши Ctrl+Shift+Enter.

Например, в диапазоне а1:с8 введена одна матрица (8Ч3), в диапазоне e1:k3 введена другая матрица (3Ч7). Нам надо вычислить их произведение. Понятно, что результатом будет матрица размерами 8Ч7. Выделяем диапазон такого размера. Например, m1:t8. Теперь вводим в стороку формул «=МУМНОЖ(а1:с8; e1:k3)» и нажимаем Ctrl+Shift+Enter. При этом формула в строке формул возьмется в фигурные скобочки (это признак того, что сработала матричная формула) и вы не сможете изменить какую-нибудь одну ячейку диапазона m1:t8.

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

Список – табличная база данных.

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

Рассмотрим пример списка:

	Годовой фонд зарплаты	Месячный оклад	Город проживания	Дата приема на работу
	140 000	10 000	Москва	01.05.2002
	210 000	15 000	Омск	17.06.1979
	21 000	1 500	Москва	02.12.2007

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