Электронные таблицы

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

Наметившийся личный финансовый кризис вновь заставил меня устроится на работу в оффлайне. На сей раз техническим консультантом в местный райсовет. Задачи случается решать различные и вот на днях пришлось мне сотрудничать с нашей бухгалтерией...

Началось всё с того, что нужно было настроить работу одной из бухгалтерских систем отчётности. Это я сделал, а затем меня спросили, знаю ли я Excel. Экселя я особо не знал, но подумал, что по ходу дела разберусь, поэтому согласился помочь. А сделать нужно было таблицу для распечатки корешков по зарплате :)

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

Поэтому, задумавшись над таким положением вещей, я и решил написать статью, прочитав которую, можно было бы вникнуть в основы работы с электронными таблицами. В статье все примеры будут показаны на базе бесплатного компонента OpenOffice Calc, но они применимы и для Microsoft Office Excel.

Рабочие книги и их структура

Рабочими книгами в сфере электронных таблиц называют файлы, в которых эти таблицы хранятся. Для пакета Microsoft Office стандартными форматами файлов Excel будут XLS или XLSX, а для OpenOffice Calc – ODS.

По умолчанию рабочая книга содержит три листа, на каждом из которых имеется таблица с ячейками. Изначально всего существовало по 256 строк и столбцов на каждом листе (всего 65536 ячеек), однако, в современных табличных процессорах размеры листа гораздо большие (хотя и конечные):

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

На одном рабочем листе Вы можете создать фактически неограниченное количество таблиц с различными расчётами. Однако, на практике чаще всего один лист содержит всего одну таблицу.

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

1. Панель меню. Может быть выполнена в виде классической панели с раскрывающимися списками функций, а может реализовываться в виде вкладок (например, ленточный интерфейс Microsoft Office Excel 2007). Содержит доступ ко всем возможностям и настройкам программы.
2. Панель форматирования. Обычно отдельная панель или вкладка, на которой находятся инструменты форматирования текста и внешнего вида ячеек.
3. Навигационный список. Обычно находится в левом верхнем углу над рабочим листом и отображает адреса текущих выделенных ячеек. Также может быть использован для быстрого перехода к ячейке с заданным адресом (вводите адрес и жмёте Enter).
4. Поле ввода формул. Специальное поле, в котором можно задать как простое содержимое выделенной ячейки, так и специальную формулу для вычисления этого содержимого.
5. Строка состояния. Отображает дополнительную полезную информацию о типе выбранной ячейки, её текущем значении и другие служебные данные.

Ячейки электронных таблиц

Основа всех электронных таблиц – это их ячейки. Каждая ячейка имеет собственный уникальный адрес, который формируется из буквы, обозначающей столбец, и цифры, указывающей на номер строки. Таким образом, например, адрес третьей ячейки в третьем столбце будет C3 (C - название третьего столбца).

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

Ещё один случай – выделение всего столбца или строки. Для этого достаточно нажать на их название. А, чтобы выделить всю таблицу целиком (например, для массового изменения параметров внешнего вида) нужно нажать на пустой квадратик в углу координатной сетки):

Каждая ячейка может содержать произвольные данные, которые вводятся вручную или вычисляются на основе заданной формулы (о формулах речь пойдёт отдельно). Кроме того, у ячеек есть ряд параметров отображения и оформления. Доступ к этим параметрам проще всего получить из контекстного меню (пункт "Формат ячеек"):

Самая главная закавыка с ячейками кроется в первой вкладке окна "Формат ячеек" (в OpenOffice Calc она называется "Числа", а в Microsoft Office Excel – "Число"). Дело в том, что здесь задаётся тип данных ячейки и во многих готовых таблицах он не всегда стандартный. Если у Вас, например, введённое число превращается в дату или нормально не отображается текст, то проблема как раз в этих параметрах.

Также советую обратить внимание на кнопки на панели инструментов, которые позволяют увеличивать/уменьшать разрядность чисел в ячейках или включать денежный формат. Эти кнопки автоматически меняют тип данных (не нужно лезть в меню) для выделенных ячеек, выводя знаки после запятой или название валюты по умолчанию (задаётся в языковых параметрах Панели управления компьютера):

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

Формулы

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

Для примера заполним ячейки первой строки произвольными цифрами. Для этого просто выделяем ячейку и в ней или в строке ввода формулы пишем числа. Наиболее часто нам нужно получить сумму чисел в определённых ячейках, поэтому в одной из свободных ячеек (пускай это будет A2) нам необходимо ввести формулу:

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

Поскольку у нас всего три ячейки с цифрами, сумму которых нам нужно получить, то мы можем воспользоваться элементарными арифметическими действиями. Просто перечисляем по порядку адреса всех ячеек через знак "плюс". Для завершения ввода нажимаем Enter или кнопку с зелёной галочкой слева от поля ввода формулы.

Отдельно стоит сказать об адресации ячеек. Каждая ячейка имеет адрес вида: "буква столбца""цифра строки". Однако, это только один из видов ссылок – относительный.

Относительные ссылки могут автоматически меняться при изменении количества строк или столбцов. Например, в ячейке A2 у нас сейчас имеется формула: "=A1+B1+C1". Теперь, если мы вставим новую строку над первой, все наши ячейки опустятся вниз, но сохранят свои значения, а в формуле (которая теперь будет в ячейке A3) номер строки автоматически сменится на второй: "=A2+B2+C2":

Если же Вам нужно точно привязать формулу к конкретной ячейке, чтобы её значение не менялось, то Вам следует использовать абсолютные ссылки. Абсолютный адрес ячейки отличается только тем, что перед каждой её координатой Вы добавляете значок "доллара", например: $A$1. Если же значок "$" добавлять только к одной из координат, то мы получим смешанную ссылку, "привязанную" к номеру строки или столбца.

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

1. Ссылка на ячейку другого листа той же рабочей книги:

• Calc: =ИмяЛиста.АдресЯчейки (например: =Лист2.A1);
• Excel: =ИмяЛиста!АдресЯчейки (например: =Лист2!A1).

2. Ссылка на ячейку на листе другой открытой рабочей книги:

• Calc: -;
• Excel: =[ИмяКниги]ИмяЛиста!АдресЯчейки (например: =[Книга2]Лист1!A1).

3. Ссылка на ячейку на листе другой закрытой в данный момент рабочей книги:

• Calc: ='file:///ПутьКФайлу/ИмяФайла'#$ИмяЛиста.АдресЯчейки (например: ='file:///H:/source.ods'#$Лист1.A1);
• Excel: ='ПолныйПутьКФайлу\[ИмяКниги(файла)]ИмяЛиста'!АдресЯчейки (например: ='D:\Отчёты\[Книга1.xls]Лист1'!A1).

Calc не имеет отдельного вида формул для получения ссылок на ячейки в другом, открытом в данный момент, файле. Вместо этого Вам нужно использовать третий вариант с полным видом ссылки. При этом, обратите внимание на слеши в путях. В Excel, как в Проводнике Windows, используются обратные слеши, тогда как в Calc используются абсолютные ссылки в стиле UNIX-подобных систем!

И теперь снова вернёмся к математическим формулам и нашему примеру. Если нам нужно получить сумму небольшого количества ячеек, то для этого достаточно простых арифметических действий. Однако, на практике объёмы вычислений бывают гораздо больше. В этом случае неудобно перечислять все ячейки, поэтому существуют альтернативные виды формул со ссылками на диапазоны:

Принцип диапазона в том, что мы указываем адреса первой и последней смежных ячеек через двоеточие. Табличный же процессор автоматически получает значения всех ячеек, которые находятся между заданными точками и производит над ними указанные нами действия (в нашем случае суммирование).

В русскоязычных версиях Excel все доступные действия формул тоже русифицированные, тогда как в Calc сохранены оригинальные английские названия (правда, снабжённые русским описанием). Исходя из описаний Вы, в принципе, можете найти любые действия в любом табличном процессоре, но ниже я приведу соответствия наиболее используемых формул:

Формула	Excel	Calc
Сумма	СУММ	SUM
Умножение	ПРОИЗВЕД	PRODUCT
Округление	ОКРУГЛ	ROUND
Модуль числа	ABS	ABS
Корень квадратный	КОРЕНЬ	SQRT
Возведение в степень	СТЕПЕНЬ	POWER
Условие	ЕСЛИ	IF
Слияние значений	СЦЕПИТЬ	CONCATENATE
Длинна строки	ДЛСТР	LEN

Наиболее полный же список соответствий Вы можете найти на официальном WIKI-ресурсе OpenOffice.

Стилизация и распечатка таблиц

Думаю, с принципами счёта и взаимоссылок в ячейках мы разобрались, поэтому предлагаю "на закуску" разобраться с принципами форматирования готовых таблиц и вывода их на печать. Для этого предлагаю соорудить корешок по выдаче зарплаты, о котором я упомянул в начале статьи. Он должен иметь следующий вид:

Имя Получателя				___	месяц	___	год
Оклад	Ранг	Стаж	Надбавка	Премия	Отпуск	Оздоров.	Всего насчитано
							(сумма по строке)
Аванс	Подоход. налог	Профсоюз.	Больнич.	Военсбор	Алименты	Всего удержано	К выдаче
						(сумма по строке)	(разница начисления и удержаний)

Во всех ячейках числа будут вводиться вручную (или браться из других файлов с ведомостями), а в трёх будут автоматически рассчитываться при помощи элементарных формул и арифметических действий: СУММ или SUM для ячеек "Всего насчитано" и "Всего удержано", а также "Всего насчитано"-"Всего удержано".

После ввода всех полей наша табличка будет иметь примерно следующий вид:

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

Также желательно отцентрировать текст в ячейках. Выделите их, а затем вызовите из контекстного меню пункт "Формат ячеек". В открывшемся окошке перейдите на вкладку "Выравнивание" и задайте нужные параметры центровки и переноса слов:

Таблица приобретёт более красивый внешний вид, однако, нам нужно как-то узнать, насколько широкой её делать, чтобы она уместилась на печатный лист. Для этого нужно активировать разметку страницы. Это можно сделать несколькими способами, однако, самый универсальный – из меню "Файл" вызвать функцию "Предварительный просмотр", а затем закрыть окно предпросмотра:

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

Последний штрих – добавление рамок для нашей таблицы. Снова выделяем все используемые ячейки и в контекстном меню выбираем пункт "Формат ячеек". Переходим на вкладку "Обрамление" (Calc) или "Граница" (Excel) и настраиваем внешний вид рамок (для каждого элемента границы можно задать свой стиль):

В итоге мы получим красивую, готовую к распечатке табличку! При желании можно изменить ещё и фон строк с текстом, а также скопировать и вставить ниже несколько копий нашего зарплатного "корешка", чтобы на одном печатном листе их было несколько.

Выводы

В нашей статье мы рассмотрели только самые базовые действия с электронными таблицами. На практике у Вас может возникнуть множество вопросов. В Excel уже встроена хорошая справочная система, в которой можно найти большинство ответов. Для Calc же этим целям соответствует русскоязычный WIKI-портал.

Как видите, работать с табличными процессорами ненамного сложнее, чем с обычным калькулятором! Зато пользы намного больше. Поэтому, если Ваша деятельность так или иначе связана с обработкой математических или статистических данных, то электронные таблицы станут для Вас незаменимым помощником, делающим львиную долю расчётов за Вас!

P.S. Разрешается свободно копировать и цитировать данную статью при условии указания открытой активной ссылки на источник и сохранения авторства Руслана Тертышного.