ИНФОРМАТИКА

Лекция 8        Работа в электронных таблицах

Введение (ЭТ как таковые)

Любая статистическая работа заканчивается отчетом, который не обходится без длинных колонок цифр и десятка графиков. Каждый, кому приходится составлять итоговые таблицы и вклеивать гра-фики, знает, как хочется перепоручить это нудное дело кому-нибудь другому. Отвращение к рутинной работе и движет создателями многочисленных электронных таблиц (ЭТ), или spreadsheets. При по-мощи ЭТ можно решать практически все бухгалтерские задачи, задачи учета кадров, планирования и множество других задач, где требуются проводить "вычислительные операции". Во всем мире с ЭТ работают банковские служащие и страховые агенты, менеджеры и секретари. ЭТ полезны при про-стой статической работе и в инженерных расчетах. Наибольший эффект получается при совместном использовании ЭТ и Систем Управления Баз Данных (СУБД), так как ЭТ мало приспособлены для хранения большого объема информации но хорошо анализируют ее. Поэтому чаще всего эти две программы работают в паре.

История

Первые попытки создания ЭТ относятся к эре больших машин, однако наибольшее распростране-ние они получили на персональных машинах в силу доступности и удобства работы. В 1978 году, вы-пускники Массачусетского технологического института Ден Бриклин и Боб Френкстон решили создать “числовой процессор”, так как делали кучу ошибок при работе на калькуляторах. В 1979 родилась первая ЭТ для машин Apple II. Чтобы не отпугнуть покупателей, создатели решили не называть ее “электронной таблицей” а дали название “визуальный калькулятор” (по английски “visible calculator”). Так появился пакет VisiCalc. Успех превзошел все ожидания. Ежемесячно продавалась по несколько тысяч копий программы. Два наиболее преуспевающих продавца стали впоследствии ос-нователями фирмы Lotus.

К моменту появления персональных компьютеров идеология и концепция ЭТ была уже разработа-на и в январе 1983, появился пакет LOTUS 1-2-3, фирмы Lotus Development Corporation, который ста-новится самым популярным пакетом для работы с ЭТ на следующие несколько лет.

Из наиболее распространенных электронных таблиц нужно упомянуть Lotus 1-2-3 и Excel. По-скольку сейчас на большинстве компьютеров стоит помимо операционной системы Microsoft Windows также и пакет офисных программ Microsoft Office, то данное пособие описывает основные возможно-сти Excel, хотя необходимо отметить, что научившись работать в одной ЭТ, Вы сможете продуктивно использовать ЭТ, разработанные другими фирмами, так как основные идеи во всех подобных про-граммах — одни и те же.

Основные термины

Электронная таблица — программа обработки данных которые можно представить в виде прямо-угольной таблицы, образованной строками и столбцами.

Существует два способа обозначения строк и столбцов ЭТ:

• Столбцы обозначаются буквами английского алфавита - A,B,C,D... а строки числами — 1,2,3... (рис. 1 а). При этом способе после столбца с именем Z будут следовать столбцы с именами AA, AB, AC ... AZ, BA, BB, BC ... BZ, ...

• Как столбцы так и строки обозначаются числами (рис. 1 б).

Ячейка — базовый элемент ЭТ. Каждая ячейка имеет свое уникальное имя, которое строится по следующим правилам:

Если используется первый способ обозначения, то сначала пишется буква столбца, а потом номер строки. Например, A4, BZ1056, B99.

Если используется второй способ, то пишут: букву R (сокращение от слова Row — строка), номер строки, затем букву С (сокращение от слова Column — столбец) и номер столбца. Например, те же ячейки, что и в предыдущем примере, будут иметь при такой записи имена R4C1, R1056C78, R99C2.

В Excel переключиться между способами обозначения ячеек можно следующим способом: Сервис-Параметры-Общие-Стиль ссылок (Tools-Options-General-Reference Sryle).

Очень часто возникает задача группирования ячеек. При этом используют следующие два понятия:

Смежной группой (блоком) ячеек или просто группой (блоком) ячеек называется связанная прямоугольная часть ЭТ. Для обозначения группы берут имя ячейки находящейся в левой верхней части выделения, затем пишут символ “:" (двоеточие) и приписывают имя ячейки, находящейся в правой нижней части выделения.

Несмежной группой (блоком) ячеек называется совокупность ячеек и связанных групп ячеек. Обозначают такой блок, перечисляя имена, разделенные символом “;” (точка с запятой).

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

Формулы в ячейках

Понятие формулы в ЭТ

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

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

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

С2=А1+А3+7,3

говорит нам, что значение ячейки С2 будет равно сумме ячеек А1, А3 и числа 7,3.

В Excel в формулах не указывается куда заносить результат вычисления (в данном примере С2), т.е. формула начинается со знака =.

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

В формулах используются следующие знаки алгебраических операций: +,–, знак * обозначает ум-ножение, косая черта (/) — деление, знак ^ — возведение в степень (для его ввода мы в задании2 создали кнопку), также можно использовать скобки.

Ссылки

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

Копирование формул.

Абсолютные и относительные ссылки

Выше рассказано, как копировать данные из одной ячейки в другую. Что же произойдет, если мы копируем ячейку, например Е18, в которой находится не числовая или текстовая информация, а формула, например =С21? На Ошибка! Источник ссылки не найден. приведен фрагмент окна Excel, в котором показано, что в этом случае будет в соседних ячейках.

То есть, Excel берет на себя труд изменить все ссылки так, чтобы они указывали на соответ-ствующие ячейки для нового местоположения формулы.

Это происходит потому, что когда в ячейке E18 была записана формула =C21, там было указано: "Взять содержимое ячейки, находящейся на два столбца правее и на три строки ниже". После копи-рования формулы, например, в D17, ситуация осталась прежней. Формула опять говорит "Взять со-держимое ячейки, находящейся на два столбца правее и на три строки ниже". Поэтому там находится формула =В20. То же происходит и при копировании в другие ячейки.

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

Если мы не желаем этого, то надо использовать "абсолютные" ссылки. Они отличаются наличием знака $ перед названием строки или столбца (например, =$В$20 или =С$112) и тем, что при копиро-вании таких ссылок то, перед чем стоит $, не изменяется.

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

Элементарные формулы могут состоять лишь из арифметических операндов (+-*/) и адресов яче-ек. Но в реальных задачах необходимо вводить формулы, содержащие различные функции для под-счета более сложных выражений таких как синус, косинус или произведение матриц. Имена функций в формулах можно вводить вручную (если вы точно уверены, что именно такое ее название, и имен-но такие операнды у этой функции) или воспользоваться мастером функций в Excel. Если при вводе формулы необходимо вставить некую функцию, тот можно просто нажать на соответсвующую кнопку в строке формул (для Excel 95) или кнопку на панели инструментов “Стандартная”. В открывшемся диало-говом окне необходимо выбрать функцию двойным щелчком (они сгруппированы в несколько катего-рий и доступна подсказка для выделенной функции), а затем ответить на дополнительные вопросы об операндах (если оно будут).

Логические функции.

Некоторые функции в результате своих расчетов возвращают не числовые значения, а логические — то есть они могут принять или значение ИСТИНА (TRUE) или ЛОЖЬ(FALSE). Простейшим приме-ром такой функции является функция сравнения двух чисел. Введем в ячейки D3 листа “Счет” фор-мулу =С3>10000. Результатом будет значение ИСТИНА так как действительно 50 тысяч больше чем 10. Введя в ячейку Е3 формулу =С3<50000 получим результатом ЛОЖЬ.