Редактирование заголовков столбцов

Предварительные замечания.Особенностью данной таблицы является то, что столбцы C и D, E и F, G и H имеют общие заголовки. Такие заголовки весьма часто встречаются в экономических таблицах. Для редактирования таких заголовков используются специальные приемы форматирования ячеек.

1. Сделайте активной ячейку C 2 и введите в нее текст Наличие основных фондов.

2. Выделите при помощи мыши диапазон ячеек C 2: D 2.

3. Поместите указатель мыши, имеющий вид белого креста, на черный фон и щелкните правой кнопкой. В открывшемся контекстном меню выберите команду Формат ячеек…, перейдите на вкладку Выравнивание и щелчками мышью установите горизонтальное выравнивание по центру, вертикальное по центру, флажки переносит ь по словам и о б ъединение ячеек. Правильные установки диалога показаны на рис. 16.

Рис. 16.

Щелчком по командной кнопке ОК закройте окно. Снимите выделение, щелкнув по любой ячейке. Фрагмент таблицы с общим заголовком
столбцов C и D представлен нарис. 17.

Рис.17.

4. Самостоятельно отредактируйте общие заголовки столбцов E и F, G и H данной таблицы. Результат представлен на рис. 18.

 

Рис. 18.

5. В ячейки A 4 — H 4 необходимо ввести числа с 1 по 8 соответственно. Для этого воспользуемся автозаполнениемзначенийячеек. Выполним последовательно следующие действия:

5.1. Введите в ячейку A 4 число 1, а в ячейку B 4 —число2.

5.2. Выделите интервал ячеек A 4: B 4.

5.3. Подведите указатель мыши к «жирной» точке в правом нижнем углу выделенного интервала (маркеруавтозаполнения). Указатель мыши должен принять вид маленького черного крестика (см. рис. 19).

Рис.19.

5.4. «Протащите» мышью маркеравтозаполнения до ячейки H 4, после чего левую кнопку мыши отпустите. Интервал A 4: H 4 заполнитсярядом значений. Не снимая выделения, установите горизонтальное и вертикальное выравнивание По ц е нтру. Вид таблицы показан на рис. 20.

Рис.20.

6. В ячейку B 3 введите текст: Код стр.

7. Выделите диапазон B2:B3, откройте контекстное меню, выберите команду Формат ячеек…, перейдите на вкладку Выравнивание и щелчками мышью установите горизонтальное выравнивание по центру, вертикальное по центру, флажки переносит ь по словам и о б ъединение ячеек и щелкните ОК (см. рис. 21.)

Рис.21.

8. В ячейку C 3 введите текст: на начало года, тыс.руб., затем откройте контекстное меню этой ячейки, выберите команду формат ячеек…, перейдите на вкладку Выравнивание и щелчками мышью установите горизонтальное выравнивание по центру, вертикальное по центру, флажок переносит ь по словам и щелкните ОК.

9. В ячейку D 3 введите текст: на конец года тыс.руб. Эту ячейку необходимо отформатировать точно также, как и ячейку C 3. Для решения этой задачи воспользуемся форматированием по образцу. Для этого необходимо:

9.1. Выделите ячейку C 3, формат которой мы хотим использовать как «образец».

9.2. Щелкните мышью по кнопке Формат по образцу на Панели инструментов  . После этого к указателю мыши «приклеится» значок, напоминающий малярную кисть.

9.3. Поместите указатель мыши в ячейку D 3 и щелкните левой кнопкой мыши. Ячейка D 3 будет отформатирована по «образцу».

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

10. При необходимости измените ширину столбцов A, B, C, D. Для этого подведите указатель мыши на правую границу заголовка столбца, ширину которого Вы хотите изменить (на рис 22.это столбец D), после чего при нажатой левой кнопке мыши «перетащите» границу столбца на нужное расстояние вправо или влево.

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

Рис. 22.

11. Аналогично заполните ячейки E 3, F 3, G 3, H 3 (см. пп. 8-10). Таблица примет вид, представленный на рис. 23.

Рис. 23.

Ввод исходных данных

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

1.1. При помощи мыши выделите интервал ячеек B 5: B 10 (эту операцию мы неоднократно выполняли ранее).

1.2. Поместите указатель мыши на черный фон и щелкните правой кнопкой мыши. В открывшемся контекстном меню выберите команду Формат ячеек… и перейдите на вкладку Число. В поле Ч исловые форматы: щелчком мыши выберите Текстовый. (рис.24)

Рис.24.

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

1.4. В ячейку B 5 введите:010

1.5. В ячейку B 6 введите:011

1.6. В ячейку B 7 введите:012

1.7. В ячейку B 8 введите:020

1.8. В ячейку B 9 введите:030

1.9. В ячейку B 10 введите:040

2. Заполните таблицу числовыми значениями согласно табл 2. Числа вводите без пробелов между цифрами!

 

Таблица 2

Ячейка…	Значение…
C5	8835018
C6	8270111
C7	7217195
C8	514005
C9	2285943
D5	9111297
D6	8713278
D7	7258065
D8	549203
D9	2375888

3. Выделите интервал ячеек C 5: D 10, вызовите контекстное меню, выберите команду Формат ячеек… и перейдите на вкладку Число и выберите следующие установки (рис.25).

Рис.25.

4. Щелчком мыши перейдите на вкладку Выравнивание и установите горизонтальное выравнивание по ц е нтру,  вертикальное по ц е нтру, затем щелчком по командной кнопке ОК закройте окно. Снимите выделение, щелкнув по любой ячейке.

Вид таблицы представлен на рис. 26.

Рис.26.

3.8.Вычисление расчетных значений: вставка и
редактирование формул

1. Рассчитаем сумму основных фондов на начало года:

1.1 Щелчком мыши перейдите в ячейку C 10.

1.2. Перейдите на английскую раскладку клавиатуры (левый Alt+ Shift).

1.3. Введите с клавиатуры следующую формулу: = C 5+ C 8+ C 9 Редактируемая формула отображается в строке формул (рис.27). Ввод формулы всегда завершается нажатием. В результате в ячейку C10 будет занесено значение, равное сумме значений ячеек C5, C8 и C9.

Другой способ ввода формулы: введите с клавиатуры =, щелкните по ячейке C5, далее введите с клавиатуры +, щелкните по ячейке C8, введите с клавиатуры +, щелкните по ячейке C9 и нажмитеклавишу Enter.

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

 

Рис. 27.

2. Рассчитаем сумму основных фондов на конец года. Для этого в ячейку D10 необходимо ввести формулу =D5+D8+D9. Сравним эту формулу с ранее введенной в ячейку C10. Сравнение показывает, что обозначения строк в этих двух формулах одинаковы, различны лишь обозначения столбцов. Этот факт позволяет применить операцию автозаполнения ячеек формулами (автоматического копирования формул). Для этого щелкните мышью по ячейке C10, подведите указатель мыши к маркеру автозаполнения и при нажатой левой кнопке мыши «протащите» его до ячейки D10, после чего кнопку отпустите (рис. 28).

Рис. 28.

3. Рассчитаем абсолютные изменения за год для всех строк (столбец E). Для этого необходимо ввести следующие формулы согласно табл. 3

Таблица 3

Ячейка…	ФОРМУЛА…
E5	=D5-C5
E6	=D6-C6
E7	=D7-C7
E8	=D8-C8
E9	=D9-C9
E10	=D10-C10

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

3.1. Введите в ячейку E 5 формулу = D 5- C 5

3.2. Щелкните мышью по ячейке, подведите указатель мыши к маркеру автозаполнения и при нажатой левой кнопке мыши "протащите" его до ячейки Е10, после чего кнопку отпустите (рис. 29).

Рис.29.

3.3. Не снимая выделения, выберите команду формат ячеек… из контексного меню интервала ячеек E5:E10 и установите финансовый формат на вкладке Число, а затем на вкладке Выравнивание вертикальное и горизонтальное выравнивание по центру, после чего снимите выделение. Вид таблицы показан на рис.30.

Рис.30.

4. Рассчитайте относительные изменения за год (столбец F). Для этого:

4.1. Используя автоматическое копирование формул, заполните ячейки формулами согласно табл.4:

Таблица 4

Ячейка…	ФОРМУЛА…
F5	=D5/C5
F6	=D6/C6
F7	=D7/C7
F8	=D8/C8
F9	=D9/C9
F10	=D10/C10

4.2. Не снимая выделения, выберите команду Формат ячеек… из контексного меню интервала ячеек F 5:F10 и установите процентный формат с двумя десятичными знаками после запятой на вкладке Число (рис.31), а затем на вкладке Выравнивание — вертикальное и горизонтальное выравнивание по центру, после чего снимите выделение.

Рис.31.

5. Рассчитаем структуру основных фондов на начало года.

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

Таблица 5.

Ячейка…	ФОРМУЛА…
G5	=C5/C10
G6	=C6/C10
G7	=C7/C10
G8	=C8/C10
G9	=C9/C10
G10	=C10/C10

Анализ этой таблицы показывает, что использовать автоматическое копирование формул в том виде, как это делалось ранее, нельзя, так как адрес ячейки С10 должен оставаться неизменным!

 Однако эту неприятность можно легко обойти двумя способами:

¾ используя абсолютную адресациюячеек

¾ используя имена ячеек.

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

5.1. В ячейку G 5 введите формулу = C 5/$ C $10 Знак доллара, помещенный перед обозначениями столбца и строки, делает адрес ячейки абсолютным.

  Абсолютные адреса, в отличие от относительных (например C5), не меняются при копировании формул! По умолчанию Excel использует относительную адресацию.

5.2. При помощи операции автоматическое копирование формул, подробно описанной ранее, заполните формулами остальные ячейки этого столбца, после чего, не снимая выделения, присвойте интервалу ячеек G 5: G 10 процентный формат с двумя десятичными знаками после запятой (вкладка Число), вертикальное и горизонтальное выравнивание по центру (вкладка Выравнивание), после чего снимите выделение. На экране Вы увидите примерно следующее:

Рис. 32.

6. Рассчитаем структуру основных фондов на конец года. Для этого необходимо ввести в ячейки следующие формулы:

Таблица 6

Ячейка…	ФОРМУЛА…
H5	=D5/D10
H6	=D6/D10
H7	=D7/D10
H8	=D8/D10
H9	=D9/D10
H10	=D10/D10

Чтобы не вводить все эти формулы по отдельности, присвоим ячейке D 10 имя Всего, а затемвоспользуемся автоматическим копированием формул.  Для этого выполните следующие операции:

6.1. Выделите ячейку D 10 и выполните команду меню Вст а вка— И мя— П рисвоить…

В открывшемся диалоговом окне Присвоение имени (рис. 33) щелкните мышью в поле И мя (там появится мигающий курсор), введите с клавиатуры Всего и щелкните кнопку ОК.

Рис. 33.

Обратите внимание, что имя ячейки отображается в левой части строки формул.

6.2. В ячейку H5 введите формулу =D5/Всего, причем имя Всего набирать на клавиатуре не обязательно, достаточно в процессе ввода формулы щелкнуть мышью по ячейке D 10.

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

Таблица 7

Ячейка…	ФОРМУЛА…
H5	=D5/ Всего
H6	=D6/Всего
H7	=D7/Всего
H8	=D8/Всего
H9	=D9/Всего
H10	=D10/Всего

6.4. Используя рассмотренную ранее операцию форматирования по образцу, присвойте интервалу ячеек H5:H10  процентный формат. В качестве образца выберите любую ячейку диапазона G5:G10. Окончательный вид таблицы представлен на рис. 34.

Рис.34.

3.9. Отслеживание взаимосвязи (трассировка) ячеек.
Проверка формул

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

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

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

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

Выполним трассировку ячейки C 10.

1. Выделите ячейку C 10.

2. С ервис—З а висимости— П анель зависимостей. (см. рис. 35.)

Рис. 35.

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

Рис. 36.

3. Щелкните мышью по кнопке Влияющие ячейки  , затем по кнопке Зависимые ячейки . Результат представлен на рис. 37 и в особых комментариях не нуждается.

Рис.37.

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