Русские документы: Как проводить вычисления в Microsoft Excel

Как проводить вычисления в Microsoft Excel

На главную страницу

Азы
Что такое электронная таблица

Электронная таблица - это просто прямоугольная таблица, состоящая из строк и столбцов. Например:
 

 

Продажи

 

 

 

 

 

Дата

Организация

Товар

Кол-во

Ед.из.

Цена

Сумма

1.Июн

Рога и копыта

Рога

5

шт

400

2000

1.Июн

Арлекин

Масло

6

кг

12000

72000

2.Июн

Мэлла

Спирт

10

бут

5000

50000

3.Июн

Рога и копыта

Копыта

3

шт

300

900

По таблице Вы двигаете маркер. Каждая клетка обозначается, как в шахматах или в игре "морской бой"; например, D5.

В каждую клетку Вы можете занести текст, число или (самое главное!) формулу. Например, в клетку G3 (сумма) Вы записываете формулу D3*F3 (количество умножить на цену). Вся штука в том, что запишете-то Вы в клетку G3 формулу, а показывать-то она будет вычисленное значение! И более того, если Вы измените значение в какой-нибудь клетке (D3 или F3), клетка G3 тут же сама пересчитается! Это означает, что Ваше дело - указать формулы и внести числа, а все расчеты выполнятся сами! Это же голубая мечта каждого школьника - самосчитающая тетрадь по математике!
 
 

Как создать таблицу

Запустите Excel. Он находится в группе Microsoft Office. Перед Вами пустая таблица.

Как двигаться по таблице

Маркер - прямоугольник, который указывает на нужную клетку. Сейчас он стоит на клетке А1. Как Вы уже, вероятно, догадались, передвигать маркер можно клавишами-стрелками, но лучше сразу привыкнуть работать с мышкой.

Упражнение. Щелкните по клетке А4. Щелкните по клетке С6.

Да, но как двигаться вправо или вниз, если нужная клетка не видна экране? А для этого внизу и справа окна есть движки:

Эксперименты

Как ввести информацию в клетку

Да очень просто: подвести маркер к нужной клетке, набрать содержимое и нажать клавишу [Enter].

Упражнения

Что делать, если Вы ошиблись при вводе

Если клавиша [ENTER] еще не нажата, то информация пока еще не введена и все можно поправить, действуя мышкой или клавишей [ЗАБОЙ].

Упражнение. Проверьте это: наберите 1234567 (не нажимая [ENTER]!), сотрите 7 и напишите 8; замените 2 на 5.

Как исправить уже введенную клетку

А если [ENTER] уже была нажата? Тогда нужно подвести маркер к нужной клетке и нажать [F2] или сделать двойной щелчок мышкой. Теперь Вы можете действовать, как в предыдущем случае.

Упражнение. Замените в клетке А2 текст "Ведомость" на "Ведомости номер 18".

А если информации в клетке мало, или она совершенно не похожа на то, что Вы хотите туда ввести, то можно просто подвести маркер, набрать всю строку заново и нажать [ENTER]. Попробуйте.

Упражнение. Замените в клетке С1 число 1992 на 50. Кстати, как это отразилось на содержимом клетки С2? Поменяйте еще несколько раз содержимое клетки С1, посматривая при этом на С2.

Большое упражнение

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

Упражнения.

Что такое интервал

В клетке G7 нам хотелось бы видеть сумму всех сумм. Можно, конечно, ввести туда формулу G3+G4+G5+G6, но это, во-первых, долго, а во-вторых... А во-вторых, что делать, если нужно будет сложить не четыре клетки, а четыреста?

Упражнение. Но сначала запишите в клетку G7 формулу G3+G4+G5+G6.

Формулу эту можно записать и поизящнее.

Эксперимент. Давайте запишем в G7 такую формулу: =СУММ(G3.G6), то есть сумма всех клеток от G3 до G6).

Изменилось ли числовое значение клетки? Нет. Но эта запись намного удобнее. Ведь строк в таблице может быть не 3 и даже не 30; неужели перечислять их все?!

И, кстати, формулу можно записать еще проще.

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

Интервал - это прямоугольное поле в таблице. Мы задаем интервал, указав клетку в левом верхнем и правом нижнем углу, например: B3 - верхняя левая, C5 - нижняя правая. Между ними ставится двоеточие. Однако проще всего выделять интервал мышкой. Нужно просто щелкнуть по левой верхней клетке и, не убирая пальца с левой кнопки мышки, протащить указатель до правой нижней клетки.

Упражнение. Попробуйте выделить интервал B4:E12.

Ух ты, сколько кнопочек!

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

Вы можете делать с таблицами очень многое:

Столько возможностей! У Вас голова пошла кругом: разве можно все это запомнить?

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

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

Как работать с меню

Итак, с кнопками на верхней панели всё понятно. Вы нажали ее и получили результат. А как работать с меню?

Упражнение. Щелкните по слову Вставка в меню. Из этого слова должно выпасть еще одно меню:

Дальнейшее понятно: Вы просто выбираете нужное слово (блюдо) в этом меню и щелкаете по нему!

А как выйти из меню? Не поможет ли нам клавиша [Esc], нажатая дважды? Попробуйте!

Упражнение. Выйдите из меню.

И точно - помогла! А кстати, можно вместо [Esc] щелкнуть по полю таблицы, за пределами меню. Попробуйте!

Как очистить клетку

Задание. Очистите клетку А2: щелкните по ней, а затем нажмите [Delete].

И всё? И всё!

Эксперимент. А нельзя ли таким образом очистить интервал? Давайте сотрем клетки с B2 по С3, и Вы сразу все поймете. Щелкните по B2 (этим Вы задали верхний левый угол интервала), и, не отпуская клавишу мышки, протяните черную область до С3.

Теперь Вы указали нужный интервал клеток, которые хотите очистить. Нажмите [Delete], чтобы выполнить очистку.

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

А если Вы ошиблись? Как вернуться на шаг?

А если Вы очистили что-то не то, или ввели что-то не то, то Вы можете "откатиться" на шаг назад, щелкнув по кнопке  (попробуйте!). К сожалению, в отличие от Word, Excel помнит только одну последнюю операцию и может вернуть только последнее исправление.

Упражнения

Как напечатать таблицу

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

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

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

А потом нужно щелкнуть по кнопке . И всё!

Упражнение. Напечатайте Вашу таблицу.

Как запомнить таблицу на диске

Это совершенно необходимая операция.

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

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

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

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

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

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

Чтобы запомнить таблицу, щелкните по кнопке . Появляется диалоговое окно.

Теперь нужно просто затереть то имя файла, которое предлагает Excel (book1) и записать свое, а потом щелкнуть по кнопке ОК.

Упражнение. Проделайте эти шаги и запомните таблицу по именем Prodano.

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

Упражнение. Запомните таблицу Prodano еще раз.

Как закончить работу с Excel

Для этого нужно выполнить Файл | Выход, то есть щелкнуть по слову Файл в меню, а затем в появившемся подменю щелкнуть по слову Выход.

Упражнение. Проделайте это.

А теперь войдите в Excel снова. Экран пуст.

Научимся еще одной важной операции.

Как загрузить таблицу с диска

Загрузим, конечно, все ту же таблицу Prodano.

Задание. Щёлкните по . На экране появился список файлов-таблиц:

Нужно щелкнуть по нужному имени (Prodano) и щелкнуть по ОК.
 
 

Большое упражнение

Стандартный цикл работы с Excel таков: Вы запускаете Excel; создаете новую таблицу или загружаете старую; работаете с этой таблицей и, если она еще понадобится, запоминаете ее на диске. Если хотите работать с другой таблицей, создаете (или загружаете) ее и, наконец, заканчиваете работу с Excel.

Создайте одну-две небольшие таблицы из области Вашей профессиональной деятельности. Постарайтесь, чтобы в них было максимум по 20 строк и 6 столбцов (работа с большими таблицами имеет свои особенности, о которых мы поговорим позже; не забегайте вперед!). Распечатайте их на бумаге. Запомните их на диске. Сымитируйте цикл работы, описанный в предыдущем абзаце.

Как редактировать таблицу

Редактировать - значит:

Форматировать - значит:

Запустите Excel, если он еще не запущен, и загрузите таблицу Prodano. Сейчас мы ее существенно исправим.
 

 

A

B

C

D

E

F

G

1

 

Продажи

 

 

 

 

 

2

Дата

Организация

Товар

Кол-во

Ед.из.

Цена

Сумма

3

1.Июн

Рога и копыта

Рога

5

шт

400

2000

4

1.Июн

Арлекин

Масло

6

кг

12000

72000

5

2.Июн

Мэлла

Спирт

10

бут

5000

50000

6

3.Июн

Рога и копыта

Копыта

3

шт

300

900

Как вставлять строки и столбцы

Вставляем одну строку или один столбец

Конечно, в реальной ведомости будет не три продажи. А как в этом случае расширить таблицу?

Задание. Щелкните правой(!) кнопкой мышки по номеру строки 6 (то есть не по клетке, а по вертикальной полоске с номерами строк). Строка 6 должна выделиться черным цветом, и к тому же появится меню

Теперь щелкните по Вставка. Заметьте, что формула с суммой отодвинулась вниз.

Готово!

Упражнение. А теперь сами вставьте столбец F. Процедура точно такая же, но Вы должны щелкнуть не по номеру строки, а по имени столбца. Проделайте это. Заметьте, что то, что было в столбце F, переместилось в столбец G.

Закрепите успех. Вставьте еще раз строку 6. Вставьте столбец D.
 
 

Вставляем несколько строк или столбцов
 
 

А если Вам нужно вставить, например, пять строк? Неужели пять раз выполнять вставку?

Конечно, нет. Давайте вставим пять строк с 6-й по 10-ю.

Задание. Щелкните левой клавишей по номеру строки 6 и, не отпуская клавиши, протяните указатель до строки 10. Все эти строки должны выделиться черным цветом. А теперь щелкните по выделению уже правой клавишей мышки. Дальнейшее понятно. Готово!

Упражнения.

Проблемы с настройкой формул

Собственно говоря, особых проблем нет. Например, когда Вы вставили столбцы С-Е, формула Сумма перенастроилась и вместо СУММ(G3:G6) стала такой: СУММ(G3:G8)

Задание. Проверьте это!

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

Непонятно? Давайте поэкспериментируем.

Эксперимент. Вставьте строку 9. Изменился ли интервал в формуле суммы? Нет, потому что Вы вставляли строку за пределами интервала.

Эксперимент. Вставьте строку 5. Изменился ли интервал? Значит, если мы вставляем строки в середину интервала, формулы перенастраиваются.

Еще эксперимент. Вставьте строку 8. Интервал опять изменился, и вставленная строка попала в формулу.

И последний эксперимент. Вставьте строку 3. Интервал изменился, но как!

Все это интересно, но зачем Вам это, спросите Вы. А вот зачем: ведь таблицы, которые Вы создаете, не вечны. Все время Вы будете их корректировать. Так зачем создавать себе лишнюю работу и сначала вставлять строки и столбцы, а потом исправлять формулы? Уж лучше вставить их так, чтобы формулы перенастроились сами! Настоящий пользователь компьютера должен быть ленивым!

Как удалять строки и столбцы

Да точно так же, как и вставляли, только вместо команды Вставка нужно использовать команду Удалить. Вы, может быть, даже обидитесь, если я начну Вам описывать это подробно. Вы уже достаточно сильны в Excel, чтобы попробовать эту команду самостоятельно.

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

Проблема с настройкой формул существует и при удалении. Что будет, если Вы удалите столбец, значения которого используются в других клетках? Например, столбец D (Количество). Давайте попробуем. Удалите столбец D. В столбце "Сумма" немедленно появилось слово ССЫЛКА! Это не означает, что Вас за это нужно сослать на три года с поражением в правах. Просто теперь Excel не знает, что ему использовать в формуле - количество-то Вы убрали!

Мораль. Прежде, чем удалять строки или столбцы, посмотрите, а не ссылаются ли на них какие-нибудь клетки.

Как копировать клетки

А теперь вставьте несколько строк перед "итого". Заполните строки 5-8 в столбцах А - F любыми товарами.

Встает, однако, такая проблема. Неужели Вам теперь нужно вручную внести в клетку G5 формулу C5*F5, в клетку G6 формулу С6*F6 и так далее? Как-то это скучновато! Если Вы настоящий пользователь (то есть ленивый), эта перспектива не может Вас не беспокоить!

Конечно же, создатели Excel все предусмотрели. Давайте возьмем клетку G4 да и размножим ее одним махом на весь интервал G5:G8!

Задание. Щелкните по клетке G4. А теперь постарайтесь подвести указатель мышки к точке в правом нижнем углу клетки, чтобы указатель из толстого крестика превратился в тонкий. А теперь прижмите левую кнопку мышки и тащите указатель вниз. Когда отпустите - клетка G4 скопируется.

Как копировать интервал

Все это просто замечательно, но хотелось бы еще проще. Взять этак интервал от С4 до F4 и одним махом размножить его на интервал от C5 до F8. Почему бы и нет? Например, у Вас несколько записей об одном и том же товаре, только меняется количество и организация.

Задание. Выделите интервал C4:F4. В правом нижнем углу его - такая же точка (или, точнее, маленький квадратик), как и у клетки. Ухватитесь за него и протащите указатель вниз.

Вот это другое дело!

Упражнение. Закрепите успех: сотрите интервал C5:F8 и заполните его снова. И так несколько раз, пока руки не станут делать эту операцию сами.

Запомните получившуюся таблицу на диск.

Эксперимент. Можно ли копировать на заполненные клетки? До сих пор мы копировали только на пустые клетки... Может быть, новые значения просто заменят старые?

Измените формулу в G3 на D3*F3*1,4 (как известно, в нашей стране налоги изменяются только в большую сторону!) и размножьте G3 на G4:G8.

Получилось!

Что делать, если не всю формулу надо настраивать

Автоматическая настройка формул - это, конечно, хорошо.

И, кстати, она работает не только по вертикали, но и по горизонтали. И, кстати, она работает и по диагонали (Упражнение: убедитесь в этом, скопировав клетку G5 в клетку H13. Результат неожиданный, но объяснимый).

Но автоматическая настройка нужна не всегда. Иногда она даже вредна.

Давайте, например: НДС вынесем в отдельную ячейку, например, в G1. Это удобно - изменив один раз НДС в ячейке G1, Вы измените его сразу во всех формулах.

Формула в клетке G3 будет выглядеть так:

G1*F3*D3

Задание. Введите в клетку G1 число 1,2 и исправьте формулу в клетке G3. Размножьте эту формулу на G4:G8. Что-то не так...

Вот что получилось:

G3 G1*F3*D3

G4 G2*F4*D4

G5 G3*F5*D5

и так далее. Это явно не то, что бы мы хотели. Мы бы хотели вот чего:

G3 G1*F3*D3

G4 G1*F4*D4

G5 G1*F5*D5

...но как этого достичь?

Щёлкните дважды по клетке G3 и поведите курсор к тексту "G1" . А теперь нажмите клавишу [F4] на клавиатуре. Вместо "G1" должно появиться "$G$1". Опять-таки, знак доллара не означает чего-то денежного. Он просто поясняет, что в этой формуле ссылка на клетку G1 не относительная, а абсолютная, то есть при копировании клетки она так и останется ссылкой на клетку G1.

Упражнение. Сделайте в G3 ссылку на G1 относительной и размножьте эту клетку вниз.

Вы проделали все это? Ну, как? Это другое дело, не так ли?!

Как перемещать участки таблиц

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

Упражнение. Перенесите заголовок из B1 в D1 так: щелкните по клетке B1, подведите указатель мышки к правой границе клетки так, чтобы он превратился в стрелку. А теперь хватайте клетку левой клавишей мышки и тащите туда, куда нужно!

Эксперимент. Можно ли таким образом переносить не только отдельные клетки, но и участки таблицы? Запомните таблицу на диске (на всякий случай), и перенесите весь столбец с ценами из начала таблицы в конец.

Кстати, можно не выделять интервал, а просто ухватить за название столбца (A,B, C...) и тащить... Попробуйте!

Кстати, проверьте, произошла ли перенастройка формул? Это очень важно!

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

Все, что необходимо для работы, Вы уже знаете).

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

Как форматировать таблицу

Как изменить ширину столбцов

Это очень просто. Подведите указатель мышки к названиям столбцов, в аккурат между столбцами C и D. Указатель из крестика должен превратиться в две стрелки. Хватайте границу столбца и тащите вправо!

Эксперименты.

Упражнение. Вы воодушевлены успехом? Уменьшите ширину столбца В.

Как изменить высоту строки

Вы, уже, наверное, догадались. Ну да, ухватить границу строки и утащить ее куда надо.

Упражнение. Увеличьте высоту строки 2: ухватите ее за нижнюю границу и утащите вниз.

Как выровнять текст

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

Нам помогут кнопки  на верхней панели. Щёлкните по клетке F2 (Цена), а затем по кнопке , и текст "Цена" прижмется к правому краю клетки.

Задание. Проделайте то же самое с клеткой "Сумма".

Эксперимент. А что делают остальные две кнопки?

И еще один эксперимент. Действует ли выравнивание на выделенные блоки клеток (интервалы)?

Есть еще одна славная возможность - выполнить Формат | Формат ячеек и в открывшемся диалоговом меню выбрать выравнивание. Заметьте, что в этом меню возможностей куда больше. Например, можно повернуть текст ВЕРТИКАЛЬНО, да еще и несколькими способами. Это иногда ох как помогает, особенно, если значения в ячейках маленькие, а заголовки столбцов большие ("Ориентация"). Можно даже задать вертикальное выравнивание, то есть прижать заголовки вверх или вниз.

Упражнение. Приведите нашу таблицу к такому виду:
 
 


 
 

Примечание. Обратите внимание на то, что текст заголовка здесь не только повернут вертикально, но и выровнен по центру.

Как задать формат ячеек

Следующая задача - привести ячейки в вид, приятный глазу. Может, Вы хотите изменить цвет ячеек, а может, чтобы после каждого числа стояло "руб". Делается это просто: нужно щелкнуть по ячейке или интервалу ПРАВОЙ клавишей мышки и выбрать в появившемся меню пункт "Формат ячеек". (Конечно, то же самое можно было сделать, выполнив Формат | Формат ячеек.) Появляется диалоговое окно, в котором Вы видите множество закладок, в том числе и знакомые (например, "выравнивание").

Вот тут, щелкнув по слову "Число", Вы получите все возможные виды представления чисел:

Поясню:

Упражнение. Опробуйте различные форматы представления чисел.
 
 

Как нарисовать рамочки

Займемся делом увлекательным, хотя и несколько нудноватым: нарисуем красивые рамочки. Ведь наша таблица была бы куда привлекательнее, будь она оформлена так:

Вот этим Вы сейчас и займетесь.

Сначала надо выделить интервал ячеек, в котором Вы намерены провести линии.

Упражнение. Выделите интервал от A1 до G7.

А теперь выполните Формат | Формат ячеек | Рамка. Вот что должно появиться:

Теперь осталось выбрать толщину линии в блоке "стиль" и указать, где линия должна проходит, в блоке "рамка". Щелкните по "контур", "слева" и т.д. Когда Вы щелкните по "ОК", уже все линии будут проведены.

Как защитить клетки от нечаянных изменений

Зачем это нужно

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

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

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

Посмотрим еще раз на нашу таблицу.

В столбце G у нас формулы вычисления суммы, их менять не надо. Рамочки, заголовок тоже не должны меняться. Если бы мы могли как-нибудь пометить их, чтобы Excel не позволял их изменять...

Как это сделать

Давайте для начала защитим весь лист.

Задание. Выполните Сервис | Защита | Защитить лист . Появится диалоговое окно:

Ну, пароли лучше не задавать - сколько было случаев, когда люди забывали свои собственные пароли... Так что щелкайте сразу по кнопке ОК.

А теперь попробуйте изменить содержимое любой ячейки. Ну, как? То-то...

Да, но теперь с таблицей вообще ничего нельзя сделать. Надо ведь разблокировать хотя бы некоторые ячейки. Для этого выполните Сервис | Защита | Снять защиту листа. Так. Теперь нужно определить ячейки, которые можно изменять.

Задание. Щёлкните по клетке F3 правой клавишей и выполните Формат ячеек | Защита. Появится диалоговое окно:


 
 

Надо щелкнуть по окошку рядом со словом "Заблокировать", чтобы убрать крестик. Теперь ячейка разблокирована, и, если Вы защитите лист, то эту ячейку можно будет изменять.

Упражнение. Проверьте это. Защитите лист и попробуйте изменить ячейку F3. А ячейку G3 можно изменить? Попробуйте записать что-нибудь в клетку G3. Как бы не так: Excel сигнализирует, что этого делать нельзя.

Эксперимент Попробуйте отредактировать клетку G3 при помощи клавиши F2. То же самое!

Эксперимент. А может быть, удастся очистить ее клавишей [Delete]? Попробуйте!

Нет, и это не берет.

Это просто замечательно. Но... но не означает ли это, что содержимое этой клетки вообще никак не меняется? И не изменится даже в том случае, если мы изменим клетку, от которой зависит защищенная клетка? Нам такая защита не нужна!

Эксперимент. Измените содержимое клетки F3. Изменилось ли число в клетке G3?

Итак, защита - это то, что нужно.

Эксперимент. Можно ли разблокировать ячейки, когда лист защищен? Попробуйте разблокировать ячейку F4.

Не вышло? Да, пункт меню "Формат" высвечен серым цветом и не выполняется вообще. И это правильно - раз лист защищен, с ним нельзя сделать НИКАКИХ ИЗМЕНЕНИЙ, кроме ввода в разблокированные ячейки.

Итак:

Чтобы защитить все ячейки от нежелательных изменений, Вы должны установить защиту листа.

Чтобы разблокировать ячейки, которые можно изменять, Вы должны:

  1. Снять защиту листа, если она есть.
  1. Выделить интервал ячеек, который нужно разблокировать и выполнить Формат ячеек| Защита и убрать крестик в окне "Заблокировать".
  1. Опять установить защиту листа.

Поэкспериментируйте: снимите защиту со всех ячеек, которые можно изменять.

Как работать с большими таблицами

Сейчас Вам предстоит существенно увеличить размеры таблицы. Помимо всего прочего, это хорошее упражнение на повторение темы "Редактирование таблицы"; сделайте его обязательно. Что же именно Вам нужно сделать?

Добавьте столбцы:

Кроме того увеличьте ширину столбцов "Организация" и "Товар".

В результате всех этих действий таблица перестанет помещаться на экране (по горизонтали).

Не поленитесь также добавить хотя-бы 15-20 строк и повторите тему "размножение формул", чтобы таблица не влезала в экран еще и по вертикали. Постарайтесь, чтобы даты были разбросаны хотя бы по двум кварталам года, желательно, чтобы на каждый месяц квартала приходилась хотя бы одна строчка. Это будет полезно к тому времени, когда мы начнем обрабатывать эти данные.

Задание. Проделайте все эти изменения, запомните таблицу на диск - и только тогда читайте дальше.

Как закрепить заголовки

Вот первая проблема, встающая с появлением больших таблиц. Таблицы большие, а экран маленький. Когда Вы видите названия фирм, то не видите НДС, и наоборот. А, передвинув маркер на одну из последних строк, Вы теряете из виду верхний заголовок.

Задание. Установите маркер вот сюда и выполните Окно | Фиксировать подокна:

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

Эксперимент. Догадайтесь сами, как убрать фиксацию подокон.

Как разбить экран на окна

Вторая проблема связана с тем, что Вы, возможно, хотели бы увидеть одновременно весьма далекие друг от друга части таблицы. Например, Вы хотели бы наблюдать изменение суммы по столбцу НДС, изменяя количество в верхней левой части таблицы. Фиксация заголовков не всегда удобна для этого. А если разделить экран на две половины (например, по горизонтали), и в верхней половине показывать одну часть таблицы, а в нижней - другую? Именно это и делает команда Окно | Разбить:

Задание. Установите маркер посреди экрана. Выполните Окно | Разбить).

Подвигайте маркер. Убедитесь в том, что в "половинном" окне все работает точно так же, как раньше работало в большом. Перейдите во второе окно, щелкнув в нём мышкой. Подвигайте в нем маркер. Отмените разделение на окна, выполнив Окно | Отменить разбиение.

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

Как печатать таблицы

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

Нет, не придется. Excel сам "нарезает" таблицу по вертикали и печатает по частям. Затем Вам нужно просто склеить получившиеся листы.

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

Все эти режимы можно установить, выполнив Файл | Параметры страницы

Задание. Выполните это.

Громадное диалоговое окно появилось перед Вами. Конечно, оно нужно не только для печати больших таблиц. Но только при печати больших таблиц оно используется "на полную катушку".

Сейчас нас интересуют только некоторые пункты.

Как установить размеры листа

Размеры листа зависит, во-первых, от размеров бумаги, а во-вторых, от полей, которые Вы установите.

Есть масса вариантов:

Посмотрите, какой у Вас принтер (узкий или широкий) и выполните задания:

Задание. Выберите в диалоговом окне "Размер листа" и щёлкните по "язычку". Выберите формат A4 и щелкните по кнопке "Просмотр" .Выйдите из просмотра, щелкнув по кнопке "Закрыть". Выберите формат A5 и щелкните по кнопке "Просмотр" .Выйдите из просмотра, щелкнув по кнопке "Закрыть".

Как изменить ориентацию при печати

Упражнение. Щёлкните по окошку "Портрет" и щелкните по кнопке "Просмотр". Выйдите из режима просмотра, щелкнув по кнопке "Закрыть".

Упражнение. Щёлкните по окошку "Ландшафт" и щелкните по кнопке "Просмотр". Переходите к следующему листу, щелкая по кнопке "След", а к предыдущему - щелкая по кнопке "Пред". Выйдите из режима просмотра, щелкнув по кнопке "Закрыть".

Итак, Вы можете печатать таблицу вдоль листа, и поперек его, как Вам удобнее.

Как печатать на каждой странице шапку (титул)

Речь идет вот о чем. Когда Вы печатали большую (по вертикали) таблицу, то на первой странице у Вас была шапка, а на второй нет.

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

Чтобы сделать это, нужно в диалоговом окне "Параметры страницы" щелкнуть по закладке "Лист".

Вот теперь нужно щелкнуть по окошку "строки сверху" и указать интервал ячеек, в которых находится шапка (лучше всего выделить целиком строки 1-2, и в окошке появится $1:$2).

Задание. Проделайте это, а затем выполните просмотр. Шапка должна повториться на каждой странице.

Эксперимент. А нельзя ли и вертикальную шапку печатать тоже (например, столбцы A и B)? Это было бы очень удобно... Попробуйте!

Как нарисовать диаграммы

Лучше один раз увидеть, чем сто раз услышать. И лучше увидеть рисунок, чем столбцы цифр.

Сейчас Вы научитесь рисовать такие диаграммы. И не только такие.

В этом Вам поможет Мастер диаграмм, специальная программа, которая проведет Вас "за ручку" .
 
 
 
 

Как построить диаграмму с помощью Мастера

Давайте для начала построим диаграмму без названий.

Упражнение. Запустите Мастер диаграмм: щелкните по . Появился небольшой такой крестик: это Мастер диаграмм задает первый вопрос. Где разместить диаграмму? Щёлкните в любом месте таблицы и протяните вправо и вниз. Именно здесь потом появится диаграмма. Ничего, если место будет неудачным: в любой момент Вы можете переместить диаграмму, увеличить ее или уменьшить.

Теперь Excel интересуется, по каким именно интервалам ячеек мы будем строить диаграмму. До тех пор, пока Вы не указали, какие числа выводить в виде диаграммы, Excel никаких диаграмм не выведет. Удовлетворите его любопытство. Интервалы Вы уже умеете задавать, вот и задайте - от G2 до ... ну, положим, до G6. (Диаграммы из более чем пятнадцати столбиков получаются не очень красивыми, поэтому для начала нарисуем диаграмму на четыре персоны). В результате картинка станет такой:

Задали? Ну, приступаем к самому интересному. Щёлкните по кнопке Шаг>

Чтобы выбрать тип диаграммы, достаточно... конечно же, щелкнуть по нему и затем потом щелкнуть по кнопке Шаг>.

Сделайте это. Выберите Круговую диаграмму.

Ну, это пока слишком большие тонкости. Щелкайте по Шаг>.

Это диалоговое окно было бы нам полезно, если бы мы выводили диаграммы с названиями. Но пока у нас очень простая диаграмма, поэтому - Шаг> .

А вот тут можно ввести заголовок диаграммы. Например, "Отчет по фирмам". Осталось щелкнуть по "Закончить", и диаграмма готова!

А теперь - самое интересное! Попробуйте изменить ячейку D6 и вместо шести кило масла поставить два. Диаграмма тут же отреагировала!

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

Как задать легенду
 
 

Заголовок диаграммы мы уже задали. Теперь нужно задать так называемую легенду. Тогда диаграмма примет вид:
Делается это следующим образом. Сначала выделите интервал C3:C6 и скопируйте его в буфер (не забыли? Это делается Правка|Копировать) . А теперь сделайте двойной щелчок по диаграмме. Именно двойной! Так Вы войдете в режим редактирования диаграммы. Если это Вам удалось, то Вы увидите, что меню вверху немного изменилось, и выполните Вставка | Легенда.

Осталось всего ничего: щелкните по появившейся в диаграмме легенде и выполните Правка| Вставка.
 
 

Как изменить вид диаграммы

Круговая диаграмма - не единственная. Существуют еще... впрочем, чем рассказывать, лучше посмотреть.

Упражнение.

Выполните Формат|Тип диаграммы. Выберите тип "График" и нажмите ОК. Должно получиться вот что:

Задание. Опробуйте другие типы диаграмм.

Как задать значения над точками

Последняя диаграмма всем хороша, но у нее есть недостаток: Вы не видите, какие именно числовые значения находятся в точках. Исправим эту погрешность, задав Вставка|Метки данных.


 
 

Щёлкните по "показывать значение". Теперь диаграмма выглядит так:

Это куда как лучше!

Кстати. Если у Вас названия (Рога, масло и так далее) не влезли, нужно просто растянуть диаграмму вниз. Ухватите за любой из нижних "габаритных огней" и тащите! Вот так все просто...

Эксперимент. Посмотрите, как отображаются значения данных в круговых диаграммах (да и других тоже). Потом снова установите тип диаграммы "График".

Как задать несколько линий на одном графике

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

Упражнение.

  1. Выделите ячейки НДС (вместе с заголовком). Например, если НДС у Вас в столбце Н, то выделите интервал Н2-Н6.
  1. Скопируйте его в буфер.
  1. Дважды щелкните по диаграмме, чтобы войти в нее.
  1. А теперь выполните Правка | Вставить.

Результат должен быть таким.

Объёмные и прочие диаграммы

Упражнение. Любопытно, как выглядит теперь наша диаграмма в круговом виде?

Так, показывается только первый ряд, и это не удивительно: как на одной круговой диаграмме показать несколько слоев? Никак: они будут загораживать друг друга.

Упражнение. А как диаграмма выглядит в режиме Столбиковая?

Так, столбики расположились рядом. Их стало больше, а сами они стали потоньше. Хорошо.

А если Вы хотите посмотреть столбики один на другом, то есть увидеть и сумму, и налог, и общую их сумму?

Упражнение. Выполните Формат | Тип диаграммы | Параметры | Подтип.

Щёлкните по среднему подтипу и потом - по ОК.

А может быть, Excel умеет рисовать и трехмерные графики? Запросто! Выполните Формат | Тип диаграммы и щелкните по слову "Объемная".

Самый приятный вид объемной диаграммы (на мой взгляд) - это "График". Вот как будет выглядеть наша диаграмма:

О чем я еще не рассказал

Графики - тема большая и сложная. Вы познакомились с основами, а более экзотические возможности я обозначу штрихпунктиром:

можно менять формат надписей на осях;

можно вырезать кусок из пирога на круговой диаграмме;

можно шкалировать оси не автоматически, а вручную, задавая начало, конец и шаг;

можно манипулировать цветами графика, надписей и т.д.;

можно манипулировать шрифтами в надписях;

можно задавать или отменять сетку, рамку , оси;

можно закрашивать диаграммы как сплошным цветом, так и штриховкой;

можно вращать объемные диаграммы, поворачивая их нужным боком к зрителю.

Упражнение. Какие-то из этих возможностей Вам понравились? Или Вы чувствуете, что они пригодятся? Тогда опробуйте их.

Как пользоваться рабочей книгой

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

Это типичная рабочая книга, информация в которой в которой связана темой "Учебный центр". Сейчас эта книга раскрыта на листе "Смета" (заметьте, внизу название этого листа выделено. Кроме этого листа, есть листы "Доход" , "Расход" и так далее. Собственно говоря, это та рабочая книга, которой я пользуюсь постоянно и в которой находится вся ключевая информация по учебному центру - от списка дел до прикидочных расчетов по закупке компьютеров.

Собственно говоря, любой файл в Excel выглядит так же, то есть это рабочая книга, составленная из множества листов. Листы эти можно перелистывать, вырывать, вставлять, давать им названия, а информацию, хранящуюся на одном листе, можно использовать на другом листе (например, можно связать листы "Доходы" и "Расходы" и на отдельном листе вывести разницу). Все это мы сейчас научимся делать. Итак, сейчас у Вас листы таблицы имеют имена "Лист1", "Лист2" и так далее.

Как перелистывать рабочую книгу

Листов в рабочей книге может быть очень много, так что Вы видите только пять-шесть закладок одновременно. Остальные скрыты за панелью.

Если закладка нужного Вам листа видна, то просто щелкните по ней, и нужный лист откроется.

Упражнение. Щёлкните по закладке "Лист2". Щёлкните по закладке "Лист3". Щёлкните по закладке "Лист1".

Если же нужная закладка не видна, воспользуйтесь кнопками, расположенными в левом нижнем углу экрана: . Крайние кнопки устанавливают указатель сразу на первый или последний лист. Средние - двигают указатель на лист влево-вправо.

Упражнение. Перейдите на последний лист. Перейдите на первый лист. Перейдите на лист 11. Перейдите на лист 2. Не забывайте, что для того, чтобы сделать лист активным, нужно не только сделать видимым его закладку, но и щелкнуть по этой закладке.

Как вставлять, переставлять, удалять, копировать и переименовывать листы

Делается это очень просто. Нужно для начала щелкнуть по нужной закладке ПРАВОЙ клавишей мышки. Появится вот такое меню:

Осталось выбрать нужный пункт, и - вперед!

Упражнения.

Как ссылаться на содержимое других листов

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

Упражнение.

  1. Запишите в ячейку А1 листа "Смета" какое-нибудь число. Например, 56.
  1. Перейдите на лист "Организации", и в ячейку, например, С1, начните набор формулы:

  2. =100*
  1. Теперь, не нажимая Enter, перейдите на лист "Смета" и щелкните по ячейке А1. Заметьте, что формула стала выглядеть так: =100*Смета!А1
  1. Вот теперь можно и Enter нажать. Посмотрите, что теперь в С1.

Как консолидировать данные

Консолидация - слово звучное и не совсем понятное. Обозначает же оно совсем простые вещи. Например, на трех листах у Вас расположены одинаковые по структуре таблицы за январь, февраль и март. А Вам нужно получить суммарные данные за первый квартал. Это и значить консолидировать три таблицы в одну. Давайте попробуем сделать это.

Упражнение.

Заведите лист "Январь" (левый верхний угол - ячейка А1):
 

НДС

6000000

Соцстрах

400000

Прибыль

1200000

Заведите лист "Февраль" (левый верхний угол - ячейка А1):
 

НДС

8000000

Соцстрах

500000

Прибыль

1400000

Заведите лист "Март" (левый верхний угол - ячейка А1):
 

НДС

9000000

Соцстрах

600000

Прибыль

1800000

Если Вы ленивый пользователь, Вы, конечно, воспользовались копированием данных. Если нет - то писали всё это три раза. И напрасно! Настоящий пользователь должен быть ленивым! Так что теперь заведите еще лист "1 квартал":
 

НДС

 

Соцстрах

 

Прибыль

 

Теперь выделите интервал В1:В3 на листе "1 квартал" и выполните Данные | Консолидация. Вот что Вы увидите:

Щёлкните по окошку "Создавать связи с исходными данными". Это очень полезное окошко. Тогда, если у Вас будут меняться исходные таблицы, автоматически будет пересчитываться и суммирующая таблица.

Щёлкните по окошку "Ссылка". Теперь нужно указать, что именно Вы собираетесь консолидировать. Щёлкните по закладке "Январь" и выделите интервал В1:В3. Щёлкните по кнопке "Добавить". Перейдите на лист "Февраль" и повторите эту процедуру. Понятно, что и с мартом нужно проделать то же. А вот теперь можно и щелкнуть по кнопке ОК. Проверьте: на листе "1 квартал" должны быть суммирующие данные.

Эксперимент. Что будет, если изменить данные, например, за март. Изменятся ли суммирующие данные? Напомню, Вы включили режим "Создать связи с исходными данными". Кстати, как Вы думаете, всегда ли нужно его включать?

Эксперимент. Что будет, если использовать не сумму, а, например, максимум или среднее (для этого при консолидации нужно щелкнуть по окошку "Функция" и выбрать нужную функцию).

Эксперимент. Что будет, если консолидировать данные, находящиеся не в одинаковых интервалах (у нас все было в интервалах В1:В3, а могло бы быть и так: в январе С4:С6, в марте - А8:А10, а в феврале - В1:В3).

Что такое обработка данных

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

Таблица имеет вид так называемого списка, или базы данных. Есть столбцы, и каждый столбец имеет имя (Организация, Дата и т.д.). Эти столбцы называются полями. Есть строки, и вычисления организованы по горизонтали (например, поле Д-76 вычисляется как Цена*Количество). Вы вдруг захотели узнать, на какую именно сумму наторговали Вы с К/х "Луч", или решили посмотреть только строки, относящиеся к 12 марта... Вот тут Вам помогут мощные и в то же время простые средства Excel - фильтрация, сортировка, итоги и сводные таблицы.

Как фильтровать данные

Фильтровать - значит отбирать только строки, удовлетворяющие некоему условию.

Как установить фильтр

Установите маркер внутри списка и выполните Данные | Фильтр| Автофильтр. Около каждого имени поля появится язычок:

Щёлкните по язычку "Организация".

Вот это да! Excel сам отобрал неповторяющиеся названия организаций. Щёлкните по строчке АОЗТ "Белокуриха". В таблице остались только строки, в которых название организации - АОЗТ "Белокуриха"!

Как показать все строки (сбросить условия)

Да, но где же остальные? Куда они делись? Не беспокойтесь, щелкните еще раз по язычку "Организации" и выберите "Все". Все строки вернулись обратно.

Это очень интересно...

Как установить два условия по одному столбцу

А если Вам захотелось посмотреть сразу и АОЗТ "Белокуриха", и к/х "Заря"? Всё учтено могучим ураганом, как говаривал О. Бендер. Щёлкните по язычку "Организации", и в появившемся списке выберите "Настройка". Вот что Вы увидите:

Щёлкните по верхнему правому язычку и выберите "АОЗТ Белокуриха". Щёлкните по нижнему язычку и выберите "к/х Заря". Но это еще не всё. Левые язычки означают логические условия (равно, больше и т.д.) Нужно установить и вверху, и внизу знак равенства.

Но и это еще не всё. Есть еще окошки "И" и "ИЛИ". Это тоже важные окошки. "И" означает, что оба условия должны выполняться одновременно. Но в нашем случае это невозможно: не может быть организация одновременно и АОЗТ Белокуриха, и к/х Заря! Значит, надо щелкнуть по "ИЛИ". Итак, картинка в конце концов должна быть такая:

Щёлкните теперь по ОК и посмотрите, что получилось, а затем сделайте снова, чтобы показывались все строки.

На самом деле условие "И" тоже весьма полезно. Например, Вы решили отфильтровать все сделки, в которых Д-76 находится в пределах от 400000 до 5000000. Тогда фильтр по полю "дебет" (Д-76) выглядит так:

Посмотрите, что получилось и восстановите опять все строки (сбросьте условия).

Как установить условия для нескольких полей

Эксперимент. А что будет, если, отфильтровав список по Организации, мы щёлкнем еще по одному язычку, например, по "Форма оплаты"? Попробуйте отобрать все операции, проведенные безналичным путем с АОЗТ Белокурихой!

Таких условий мы может указать сколько угодно, но, к сожалению, все они будут объединяться только по "И". Более сложные фильтры можно строить, если выполнить Данные | Фильтр| Усиленный фильтр, но в этой книге мы это рассматривать не будем.

Сбросьте оба условия.

Упражнения

Как отказаться от фильтра

А очень просто - выбрать еще раз Данные | Фильтр| Автофильтр. Откажитесь от фильтра.
 
 

Как упорядочить (отсортировать) таблицу

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

Только учтите, что сортировка - операция опасная, и перед ней желательно сохранить таблицу на диск.

Задание. Сохраните таблицу.

Выполните Данные | Сортировка. (Внимание! перед этим убедитесь, что курсор стоит внутри вашей таблицы, а не где-нибудь за ее пределами!).

Excel спрашивает, по какому именно столбцу Вы собираетесь сортировать (упорядочивать) таблицу. Щёлкните по язычку и увидите список:

.

Осталось щелкнуть по слову "Организация" - и дело в шляпе!

Теперь посмотрите на таблицу. Она переупорядочена.

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

Упражнение. А теперь упорядочьте таблицу по убыванию суммы.

Эксперимент. Попробуйте упорядочить таблицу так: по организациям, а если организации одинаковые, то по сумме. (Подсказка: используйте "Затем" в диалоговом окне сортировки).

Перед следующей темой упорядочьте таблицу по организациям.

Как подбить промежуточные итоги по группам

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

Выполните Данные | Итоги. Установите в появившемся диалоговом окне язычки так:

И, естественно, щелкните по ОК. То, что при этом получится, Вам наверняка понравится:

Вы видите суммы дебета и кредита по каждой организации, да еще и общую сумму... Неплохо! А что это за загадочные минусы появились слева, и какие-то цифры (1 2 3)? щелкните-ка по цифре 2!

Здорово! Теперь мы видим только суммы по организациям, без подробностей. А если все-таки захотелось подробностей? Например, что скрывается за суммой по Бийскому маслосырзаводу? Ну так щёлкните по плюсику напротив этого маслосырзавода.

Теперь, кажется, становится ясно, что там были за минусики: если щелкнуть по нему, данные сворачиваются до одной строки с суммой! Попробуйте!

Эксперимент. А что будет, если щелкнуть по единичке?

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

Как отменить итоги

Выполните Данные | Итоги и щелкните по "Убрать всё".

Сочетаются ли итоги с автофильтром?

Вопрос, вообще-то, интересный... Если мы вдруг захотим получить итоги только в промежутке от 1 января до 31 марта? Попробуйте - включите сначала автофильтр, потом итоги. Или наоборот. Результат будет интересным.
 
 

Эти умопомрачительные сводные таблицы

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

Итак, если Вы что-то сейчас жуете, выньте это изо рта, не то подавитесь. Сядьте покрепче. Пристегните ремни. Начинаем.

Что такое сводная таблица

Электронные таблицы - самое гениальное изобретение в компьютерной жизни. Сравниться с ним может разве что изобретение самого компьютера и, может быть, изобретение первого языка программирования. Но были задачки, электронным таблицам доселе недоступные. Вот, положим, в таблице типа PRODANO Вам нужно узнать, на какую сумму сторговали Вы с К/х "Луч" в первом квартале, или процентное соотношение безналичных и наличных продаж... Короче, нужно получить некоторые обобщающие данные. Можно, конечно, городить сложные формулы, отбирая нужные ячейки. Можно использовать отбор по критериям. Можно попробовать консолидацию. Но есть средство куда проще и мощнее.

Задание. Если Вы находитесь в нашем учебном центре, загрузите учебную таблицу Sklad. Иначе - дополните таблицу PRODANO такими же данными. Найдите в меню "Данные" подменю "Сводная таблица" (маркер в это время должен стоять внутри Вашей таблицы). Дважды нажав "Шаг>", Вы увидите вот что.Справа - ПОЛЯ Вашей таблицы (названия столбцов). В центре - макет сводной таблицы. Там Вы укажете, по каким полям Вам данные сгруппировать (строки, столбцы), и что именно группировать (данные).

Вы просто хватаете поле "организация" мышкой и тащите на область "Строка", а поле "Д-76" тащите в "Данные".

Упражнение. Сделайте это!

Внимание! Если Excel у Вас настроен неверно, то вместо "Сумма по полю Д-76" появится "Счет по полю Д-76". Тогда нужно дважды щелкнуть по данным, сделать вот так:







и, естественно, щелкнуть по ОК. Щелкнув потом по "Закончить", вы получите на отдельном листе вот что:

Вот так всё просто. Раз, два - и суммы по организациям готовы. Здорово, правда? Но это еще не всё. Что будет, если Вы захотите получить суммы по товарам, а не по организациям? Или суммы и по товарам, и по организациям?

Упражнение. Выполните опять Данные|Сводная таблица и утащите поле "Организация" на область "Столбец", а поле "Товар" на область "Строка". Вот это да!

Вот это уже сводная таблица почти во всем своем великолепии. Вы оценили? Пять щелчков мышкой, и Вы получаете ЛЮБОЙ срез Ваших данных. Например, если бы в исходной таблице были поля "счет дебет" и "счет кредит", а в каждой строке были бы записаны проводки, Вы тут же получили бы "шахматку".

Как менять группировку

Но ведь и это еще не все. Если Вы просто ухватите поле "Организация" и утащите его влево (ОДНО движение мышкой!), то получите вот что:

И так далее, а в конце таблицы - общая сумма.

Но Вам, положим, нужна группировка не по товарам, а по организациям. Хватайте поле "Товар" и тащите его вправо! Легким движением руки таблица превращается в такую:

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

Входим в макет сводной таблицы, тащим "Форму оплаты" на область строк.

Ап!

Как фильтровать сведенные данные

Всего одно движение мышкой, и такие потрясающие результаты! Но Вы будете потрясены еще больше, когда узнаете, что это была еще только присказка. Сказка только начинается! Сказка начинается с того, что мы передвигаем поле "организация" в левый верхний угол. (Упражнение. Вы не забыли сделать это?) И что же мы видим?
 
 

Щелкните по язычку около слова "Все".
 
 

Теперь, выбрав, например, АО "Альянс", Вы получите суммы только по АО "Альянс"! Вот так все просто!

 
 

Как группировать данные помесячно и поквартально

Но самые восхитительные штуки можно получить, пустив по столбцам поле "Дата". Поле "Товары" уберем вовсе, а поле "Организация" пустим по строкам

Это еще не все. Теперь, щелкнув по полю "Дата" левой клавишей и выбрав "Группировка и структура" "Группировать", получим вот что:

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

И даже еще круче, вот так (если утащить кварталы в левый верхний угол):

Вот это, скажу я Вам, вещь. Теперь Вам понятно, почему ушла в монастырь та программистка? А если непонятно, поясню: в так называемых АСУ-шных (или, как теперь говорят, офисных) задачах программисты испокон веку занимались тремя вещами: ведением базы данных, фильтрацией данных и составлением сводных таблиц. Теперь у них остается только ведение БД...

Но не спешите отстегивать ремни и запихивать в рот бутерброды. Демонстрация сводных таблиц еще не закончилась! Взгляните на диалоговое окно "Поле сводной таблицы"(туда, где мы устанавливали сумму вместо счета). Что будет, если мы все-таки поставим там счет? А вот что: в клетках сводной таблицы появится КОЛИЧЕСТВО строк, удовлетворяющих условию. Например, не сумма денег, полученных от АО "Альянс" за хлеб, а число покупок им хлеба. Для этой задачи количество бессмысленно, но так можно, например, обрабатывать социологические анкеты! А ведь кроме суммы и счета, есть еще и максимум, минимум, среднее и так далее!

Что, кроме суммы?

Но и это еще не все. Щелкнув в этом же диалоговом окне по кнопке "Параметры", Вы откроете еще целую кучу возможностей:

Например, Вы сможете показать вместо сумм (или вместе с суммами) проценты по строке. Выглядеть это будет, например, так:


 
 

Ну и, пожалуй, хватит. Не то, чтобы я рассказал все о сводных таблицах. Нет. Еще осталось... ну например, такая возможность: сводные таблицы можно строить не по таблице Excel, а по некоей выборке из базы данных, написанной хотя бы на FoxPro. Причем можно построить весьма сложный запрос и отобрать данные из нескольких связанных таблиц.

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

Функции

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

Как работать с Мастером функций

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

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

Как округлить число и взять абсолютное значение

Перейдите на любой пустой лист рабочей книги.

Введите в клетку А1 число 1234,567, а в клетку В1 число -123.

Введите в клетку В2 формулу abs(B1) (или, что то же самое, вызовите Мастер функций, выберите категорию "Математические" и функцию ABS).

В клетке В2 появилось 123, то есть абсолютное значение числа -123. Какой знак ни имело бы число, abs делает его положительным.

Введите в клетку А2 формулу ЦЕЛОЕ(B1) (или, что то же самое, вызовите Мастер функций, выберите категорию "Математические" и функцию ЦЕЛОЕ).

В клетке А2 появилось число 1234, или целая часть числа 1234.567. Функция ЦЕЛОЕ просто отбрасывает дробную часть, как будто ее нет вовсе.

А округление?! А округление делает следующая функция.

Введите в клетку А3 формулу ОКРУГЛ(B1,0).

В клетке А3 появилось число 1235. Оно округлено до целого. А если вместо нуля Вы установите другое число, то ОКРУГЛ округлит до указанного количества десятичных знаков.

Любопытно, что количество разрядов может быть отрицательным. Например, ОКРУГЛ(B1,-2) даст 1200, то есть округлять можно до десятков, сотен и т.д.

Упражнение. Проверьте это.

Как использовать математические функции

Даю только формулы, без комментариев:

КОРЕНЬ - квадратный корень

exp - экспонента

ln - натуральный логарифм

pi - число Пи

СЛЧИСЛ - случайное число

ОСТАТ(a,b) - остаток от деления a на b

sin - естественно, синус

cos - конечно, косинус

tan - тангенс

asin - арксинус

acos - арккосинус

atan - арктангенс

Упражнение. Введите в клетку А4 формулу КОРЕНЬ(A1). Хорошо. А как Excel отреагирует, если заставить его взять корень из отрицательного числа? Исправьте формулу на КОРЕНЬ(B1). Так, вместо значения выдается #ЧИСЛО! Это сообщение выдается и в других случаях (например, если Вы пытаетесь взять логарифм от отрицательного числа).

Эксперимент. Проверьте, способен ли Excel обрабатывать вложенные функции? Например, вычислит ли он такое выражение:

sin(ln(A1)+cos(B1))

Как подсчитать сумму, среднее, максимум и минимум по интервалу

Одну функцию Вы уже знаете: СУММА. Правда, у Вас могло возникнуть ложное впечатление, что интервал - это всегда часть строки или столбца. Ничего подобного. Интервал может быть прямоугольным, и тогда Вы задаете координаты левого верхнего и правого нижнего угла.

Давайте убедимся в этом. Перейдите снова на первый лист нашей таблицы Prodano. В клетку А1 занесите формулу СУММА(F3:G6). Действительно, Excel сложил все числа в заданном прямоугольнике (проверьте!).

Но это еще не все. Исправьте формулу, заменив СУММА на МАКС так, чтобы получилось МАКС(F3:G6). Теперь в А1 у Вас максимальное значение в интервале.

А если Вы поставите не МАКС, а МИН, то... ну, конечно, минимальное значение.

A если не МИН, а СРЗНАЧ(F3:G6), то это будет среднее значение.

И, наконец, СЧЁТ(F3:G6) - количество непустых числовых клеток в интервале.

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

Как включить в выражение условие

Выражения - штука тонкая. Бывает так, что, в зависимости от разных условий, выражение в клетке должно быть то одним, то другим. Типичный пример - подоходный налог. Для каких-то сумм процент подоходного налога один, для других - другой, а формула-то должна быть одна! Для этих целей существует функция ЕСЛИ. Она записывается так:

ЕСЛИ(условие;выражение1;выражение2)

Работает так: если условие верно, вычисляется выражение 1, иначе - выражение 2.

Например, пусть в клетке d13 находится выражение

ЕСЛИ(D6<1000;D6*0,12;D6*0,2)

То есть, если число в клетке D6 меньше тысячи, то в клетку D13 занесется 12%D6, а если больше тысячи или равно, то 20%D6.

Упражнение. Добавьте в таблицу Prodano новый налог, который составляет 12% от НДС, если НДС меньше 1 000 000, и 20% от премии, если НДС превышает миллион. Согласен, что это весьма глупый налог, но где и когда в нашей стране Вы видели умные налоги?

А можно ли строить более сложные конструкции? Например, если НДС до 1 000 000, то 12%, если от 1 000 000 до 10 000 000, то 20%, а если свыше 10 000 000, то 30%. А почему бы и нет? Просто на месте любого из двух выражений в ЕСЛИ опять-таки может стоять ЕСЛИ. Например:

ЕСЛИ(D6<1000;D6*0,12;ЕСЛИ(D6<10000;D6*0,2;D6*0,3))

Главное - правильно расставить скобки! А пределов в погружении ЕСЛИ в ЕСЛИ нет.

А можно ли указывать более сложные условия? Например, если НДС меньше миллиона, но цена меньше тысячи рублей, то налог берется в размере 6%. Формула будет выглядеть так:

ЕСЛИ(И(D6<1000,F6>0),D6*0,6,D6*0,12)

И - функция "И". Она верна, если верны оба условия. А если хотя бы одно неверно, то неверно и обобщающее условие "И".

Есть еще функция ИЛИ. Она верна, если верно хотя бы одно условие из двух. Например,

ЕСЛИ(ИЛИ(D6<1000,F6>0);D6*0,6;D6*0,12)

означает вот что: если D6 меньше тысячи, то, независимо от того, больше f6 нуля или нет, условие все равно верно. И наоборот, если f6 больше нуля, независимо от того, больше нуля D6 или нет, условие все равно верно.

И, наконец, функция НЕ. Это просто отрицание. Если выражение верно, то НЕ(выражение) ложно, и наоборот.

Все эти функции можно вкладывать друг в друга и конструировать весьма сложные условия. Однако без нужды этим не увлекайтесь: в сложных условиях легко сделать ошибку и трудно ее заметить. Старайтесь не делать больше 2-3 вложений условий и ЕСЛИ.

Как работать с датами

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

Начнем с простой, но полезной функции, просто выдающей текущую дату. Если в вашем компьютере установлены и правильно подведены часы, он знает текущую дату и текущее время.

Воспользуйтесь этим и в клетку А1 занесите текст =СЕГОДНЯ(). Что Вы там видите? Сегодняшнее число. Прелесть в том, что завтра там автоматически проставится завтрашнее число.

Упражнения.

Ну и несколько более экзотичных функций:

Упражнение. Покажите сегодняшний день недели.

Большое упражнение. Добавьте в таблицу Prodano колонки "Дата по договору" и "Просрочка". Добейтесь, чтобы для всех платежей просрочка рассчитывалась автоматически, и введите пеню за просрочку в размере 10% за каждые 10 дней.
 
 

Прочие функции

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

О чем я еще не рассказал

Конечно, это еще не весь Excel. Но я и не ставил задачу описать весь Excel. Более того, я не ставил задачу описать именно Excel. Я хотел на примере Excel показать Вам возможности электронных таблиц.

Но все-таки, что еще может Excel? Покажем штрихпунктиром:

Очень краткий справочник по EXCEL 5.0
 
 

Клавиши указываются в квадратных скобках, например [Home].

Позиции меню указываются через вертикальную линию. Например, Файл | Сохранить как означает: выбрать в меню Файл, в появившемся окне выбрать Сохранить как

Как работать с файлами

Создать новый пустой блокнот  или Файл | Создать

Открыть существующий блокнот  или Файл | Открыть

Сохранить блокнот в виде файла на диске  или Файл | Сохранить

Сохранить блокнот впервые или под другим именем Файл | Сохранить как

Найти блокнот по контексту (если не помните имя файла) Файл | Поиск файла

Напечатать рабочий лист  или Файл | Печать

Перемещаться между несколькими открытыми блокнотами Окно| (и выбрать блокнот)

Как двигаться по блокноту

По рабочему листу лифты справа и внизу 

Между рабочими листами 

На один экран вверх / вниз [Page Up]/ [Page Down]

Зафиксировать верхние и боковые титулы 

Как корректировать блокнот

Корректировать содержимое ячейки двойной щелчок по ячейке или [F2]

Удалить текущий / предыдущий символ [Delete] /[BackSpace]

Отменить последнее действие 

Вставить строку или столбец

щёлкнуть по номеру строки или имени столбца и Правка |Вставить

Удалить строку или столбец

щёлкнуть по номеру строки или имени столбца и Правка |Удалить

Начать набор формулы =

Вставить функцию 

Вставить диаграмму 

Как работать с блоками ячеек

Выделить блок ....................щёлкнуть по первой ячейке и протащить до последней

Переместить выделенный блок...... подвести к границе блока, нажать левую клавишу

мышки и тащить

Размножить выделенный блок ...... подвести к левому нижнему углу, нажать левую клавишу и тащить

Очистить выделенный блок [Delete]

Скопировать выделенный блок в буфер .....................................Правка | Копировать

Вырезать выделенный блок в буфер...............................................Правка | Вырезать

Вставить выделенный блок из буфера.............................................Правка | Вставить

Снять выделение.........................щёлкнуть мышкой за пределами выделенного блока

Как форматировать текст

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

Выбрать шрифт, размер, начертание 

Как обобщать данные

Сформировать сводную таблицу Данные| Сводная таблица

Сформировать итоги по отсортированной таблице Данные | Итоги

Отфильтровать данные Данные | Фильтр | Автофильтр