Что нового в Excel 2007 и 2010?

В.Г.Казанцев

Методы использования электронных таблиц Excel 2007-2010

Практикум для студентов очной формы обучения

Гатчина

======================================================================

Содержание практических занятий по Excel 2

Что нового в Excel 2007 и 2010?. 2

Окно Excel 2010 (2007) – структура, содержание вкладок ленты. 4

Добавление, скрытие, отображение и защита элементов таблицы. 6

Работа с ячейками в Excel 2010 (2007) 6

ПЗ-1. Данные. Прогрессии. Списки. Форматы. Мастер функций. 7

Создание книги. Состав таблиц. Ячейки. 7

Ввод и проверка вводимых данных. 7

Построение и использование прогрессий. 8

Формулы, операторы, ссылки, сообщения об ошибках, функции. 9

Работа с формулами. 9

Типы операторов: 10

Ссылки в формулах. 10

Сообщения об ошибках. 10

Работа с функциями. 11

ПЗ-2. Списки. Виды функций. График функции. Таблица умножения. 12

Создание списков. 12

Виды функций. 12

Пользовательский формат. Мастер функций. 12

Построение графика значений синуса угла. 13

Простая таблица умножения. 14

ПЗ-3. Области ввода. Вклады. Условное форматирование. Номера недель. 15

Таблица умножения с областью ввода. 15

Таблица расчета процентов по вкладу. 15

Платежи по займу. 17

Условное форматирование. 17

Номера недель. 18

Дополнительные возможности условного форматирования. 18

ПЗ-4. Определение последнего дня недели каждого месяца. Макросы. 21

Определение последнего четверга каждого месяца. Даты текущего месяца. 21

Использование макросов для создания интерфейса. 22

Содержание книги. 23

ПЗ-5. Вычисление данных различными способами. 24

Вычисление значений синуса с помощью таблицы. 24

Вычисление с помощью полосы прокрутки. 24

Использование строки состояния. 25

Вычисления с использованием имен. 25

Ввод и редактирование табличных формул. 26

ПЗ-6. Текстовые функции. Дата прописью. Функции даты и времени. 26

Текстовые функции. 26

Дата прописью.. 28

Функции и форматы даты и времени. 28

ПЗ-7,8. Учет и анализ хозяйственных операций. 30

Постановка задачи. 30

Обработка данных табличными формулами. 30

ПЗ-9. Использование VBA для разработки модулей программ (массивы). 33

ПЗ-10. Использование макросов и модулей VBA для других задач. 38

Сочетания клавиш для работы в Excel и Windows-7. 40

Литература. 42

 

 

Содержание практических занятий по Excel

Технология работы в электронных таблицах имеет такой богатый набор приемов и способов, освоить которые в полном объеме практически невозможно. Да этого и не требуется. Каждый пользователь выбирает тот набор возможностей электронных таблиц, который соответствует предметной области и характеру его деятельности. Чтобы освоить необходимые приемы работы, надо в полной мере представлять возможности табличного процессора, владеть элементарными навыками использования вычислительной среды данной прикладной программы, понимать язык описания действий в рамках Excel, использовать основные сер­висные программы (Мастера). Этому и посвящаются предлагаемые практические занятия, реализация которых очень проста – все действия описаны достаточно подробно.

Практические занятия разработаны, в основном, для MS Excel редакции 2007 и 2010гг. Применяются следующие региональные стандарты (уста­навливаются или изменяются с помощью панели управления, пункт Язык и региональные стандарты): язык русский, разделитель дробной части – "," (запятая), разделитель элементов списка –";" (точка с запя­той), разделитель компонентов даты – "." (точка).

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

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

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

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

Проверка вводимых данных

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

Для этого: 1) Выделяем ячейку А10, выбираем команду меню Данные-Проверка данных. Появится диалоговое окно с тремя вкладками 2) На первой вкладке Параметры надо задать тип данных и их значения в окнах Условие проверки (рис.слева). 3) На второй и третьей вкладке нужно задать два сообщения, ко­торые будут появляться при выделении ячейки (для ввода данных) и если нарушится ука­занное условие.

 

Вид сообщения об ошибке должен иметь значение Останов (рис.справа).

Построение и использование прогрессий.

В ячейку В1 введем единицу, в ячейку В2 тройку. Выделим эти две ячейки и размножим (скопируем) их до 10 строки. Получим арифметическую прогрессию, первый член которой равен 1, шаг прогрессии 2, предельное значение 19. Все эти параметры мы ввели вручную.

Построить в столбце С прогрессию четных чисел, начиная с 0 до 10 строки.

В столбце D построить арифметическую прогрессию от 0 до 100 с шагом 5.

Кроме ручного способа построения арифметической прогрессии есть способ использова­ния встроенного мастера. Но – всегда надо в ячейку, откуда начнется построение прогрессии, вручную вве­сти значениепервого ее члена и щелкнуть по кнопке с галочкой (или нажать Enter и вернуться в ячейку).

Вызов мастера (окна Прогрессия): вкладка Главная, группа Редактирование, кнопка Заполнить, пункт Прогрессия (рис.справа).

Итак, в ячейку Е1 вводим 0, возвращаемся в нее и вызываем окно Прогрессия (см.рис. на след.стр.). В нем выбираем параметры – такие же, как в столбце D и нажимаем кнопку ОК. В столбце Е будет построена ариф­метическая прогрессия – точно та­кая же, как в столбце D.

Перейти на новый лист, назвать его именем График и в столбце А построить арифметическую прогрессию от 0 до 360 с шагом 10 любым из рассмотренных способом.

Вернуться на лист Данные.

 

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

 

 

1. Введем в ячейку А11 единицу, а затем размножим ее вниз при нажатой клавише Ctrl до 20 строки. Получим ряд чисел от 1 до 10.

2. Введем в ячейку В11 число 101, а затем размножим ячейку вниз, нажав правую кла­вишу мыши, до 20 строки и там отпустим клавишу. В появившемся контекстном меню надо выбрать Прогрессия и сразу щелкнуть кнопку ОК. Получим порядковые номера от 101 до 110.

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

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

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

 

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

 

 

Убедимся, что число столбцов на листе – 256 (или более 16 тысяч à в Excel - 2010). Выделим первую строку и в контекстном меню щелкнем пункт Добавить ячейки (Вставить). В ячейку А1 введем единицу, щелкнем по кнопке с галочкой, вызовем окно Прогрессия, введем пре­дельное значение 3000000 и нажмем кнопку ОК. Сочетанием Ctrl+à переместимся в самый правый столбец, а номер этого столбца, указанный в 1 строке, будет равен 256 или 16384 (для Excel - 2010).

Построение геометрической прогрессии с помощью меню: введем в ячейку F1 двойку, а в окне Прогрессия зададим для этой ячейки параметры: по столбцам, геометрическая, шаг 2, предельное значение 3000000, нажмем ОК. Будет построена геометрическая прогрессия, которая представляет собой последовательность двоек в степени, соответствующей номеру строки. Например, в строке 8 будет число 2⁸=256, в строке 10: 2¹⁰=1024, в 16 строке: 2¹⁶=65536, в 20-ой строке: 2²⁰=1048576.

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

Для справки: в ходе ПЗ №1 в книге созданы листы: Данные, График

Ссылки в формулах.

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

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

Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символами $ перед координатами. Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.

Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).

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

Изменение вида ссылки возможно вручную вставкой или удалением знака $, или путем нажатия клавиши F4 (поочередное нажатие циклически изменяет вид ссылки: В1 => $В$1 => В$1 => $B1 => В).

Сообщения об ошибках

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

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

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

#ЗНАЧ! Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel не может преобразовать данный текст в ячейке в правильный тип данных. Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат правильные значения.

#ДЕЛ/0! Эти сообщение о том, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

#ИМЯ? Имя с ошибкой или неправильный оператор, или в формуле используется функция с ошибкой в написании, например, СУМ(A1:А10) вместо СУММ(A1:А10).

#Н/Д Эти символы сообщают, что нужное значение не доступно для функции или формулы.

#ССЫЛКА! Эти символы сообщают о неверной ссылке на ячейку.

#ЧИСЛО! Это сообщение об использовании неправильных числовых значений в формуле или функции. Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.

Работа с функциями

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

Функция включает следующие элементы:

– знак равенства (=);

– собственное имя, например СУММ;

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

– аргументы, которые непосредственно зависят от выбранной функции и могут состоять из чисел, текста, логических величин (например, «ИСТИНА» или «ЛОЖЬ»), массивов, ссылок, формул и т. д.

– закрывающая скобка.

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

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

В Excel существуют математические, финансовые, статистические и другие функции. Имя функции можно вводить вручную с клавиатуры, а можно выбирать в окне «Мастер функций».

Функция может выступать в качестве аргумента для другой функции. В этом случае она будет называться вложенной функцией. При этом в формулах можно использовать до семи уровней (Excel 2007 и более ранние версии) или до 64 уровней (Excel 2010) вложения функций.

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

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

1 способ: 1. В окне открытого листа выделить ячейку, где будет располагаться функция, перейти к вкладке «Формулы» и в группе «Библиотека функций» щелкнуть по кнопке «f_x Вставить функцию».

2. В окне «Мастер функций – шаг 1 из 2» в графе «Категории» раскрыть список существующих типов функций и выбрать нужную категорию. Например, «Математические».

3. В группе «Выберите функцию» из списка категории выбрать нужную функцию.

4. Перейти к следующему шагу щелчком по кнопке «ОК».

5. В окне «Аргументы функции» ввести в поля аргументов адреса ячеек для вычисления.

Примечание. Для каждой функции окно «Аргументы функции» имеет собственный вид.

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

2 способ: 1. В окне открытого листа выделить ячейку, где будет располагаться функция.

2. Нажать сочетание клавиш Shift+F3.

3. Далее выполнять шаги, как в первом способе.

3 способ. Нужную функцию можно выбрать на вкладке «Формулы» и в группе «Библиотека функций» из списка соответствующих категорий (напр., Финансовые – рис.выше).

ПЗ-2. Списки. Виды функций. График функции. Таблица умножения.

Создание списков.

Если ввести в ячейку строку, которая содержит число (например, "глава 1"), то при раз­множении содержимого ячейки числовое значение будет увеличиваться на единицу ("глава 2", "глава 3", и т.д.). Полезное свойство для заполнения заголовков таблиц, на­пример: "1 квартал", "2 квартал" и т.д.

Введем в ячейку G1 буквы Пн и размножим ячейку вниз. Дни недели будут обновляться до Вс, а затем список повторится. То же самое произойдет, если будет введено полное название дня недели.

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

Эти эффекты происходят потому, что в Excel имеются те самые перечни значений, которые повторяются при размножении ячеек. Окно Списки можно найти по следующему пути: Файлà Параметры à Дополнительно à Создавать списки для сортировки и заполнения (в самом конце раздела: внизу).

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

Можно создать свой список: для этого надо щелкнуть мышью в правом окне Элементы списка и ввести эти элементы по одному в строке (нажимая после каждого Enter), или все элементы в одну строку, разделяя запятой, после чего нажать кнопку Добавить. Новый список переместится в левый столбец и его можно будет использовать для автоматического создания списка: ввести значение любого элемента и размножить его.

 

Виды функций.

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

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

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

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

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

Для удобства работы функции в Excel разбиты по категориям: статистические, текстовые и математические и др.

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

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

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

Для работы с функциями используется встроенная программа Мастер функ­ций.

Пользовательский формат. Мастер функций.

Выделим диапазон А1:К20 и сделаем его заливку любым цветом. В ячейке А1 напишем Титульный лист размером шрифта 20, объединим ячейки А1:Е1. В ячейку J1 вызовем Мастер функ­ций сочетанием клавиш Shift+F3. Или: Формулы-Вставить функцию.

На первом шаге работы Мастера выберем категорию Дата и время и щелкнем по функции СЕГОДНЯ(), нажмем кнопку ОК. Появится окно второго шага с именем заданной функции (на втором шаге надо вводить значения аргументов) и сообщением, что у этой функции нет аргументов. Нажмем ОК, окно Мастера функций закроется, а в ячейке J1 появится дата.

То же самое проделаем и для ячейки К1. Здесь также появится сегодняшняя дата. Но для этой ячейки изменимФОРМАТ представления данных. Для этого нажмем сочетание Ctrl+1, ко­торое вызывает окно Формат ячеек (рис.слева). Щелкнув вкладку Число, выделим в столбце Числовые форматы строку (все форматы). В поле Тип: введем четыре буквы "Д". В процессе ввода под надписью Образец будут появляться изображения даты, соответствующие введенному формату.

Так, при значении "ДД" под словом Образец будет число (день), обозначенное двумя цифрами, при значении "ДДД" – день недели двумя буквами, а при значении "ДДДД" – день недели полностью.

Этот пример – самая яр­кая иллюстрация того, что же собой представляет ФОРМАТ данных: введенные одной и той же функцией значения отобража­ются абсолютно по-разному – в одном случае в формате даты (день, месяц, год), а в другом – в виде названия дня недели.

Небольшая задача.

Расчет дней недели, приходящихся на 13 число каждого месяца, произведем на новом листе, назвав его 13пт. В ячейку А1 введем год. В ячейку А2 введем 1 и размножим ее до 13 строки при нажатой клавише Ctrl: появится последовательность чисел от 1 до 12. В ячейки В2, С2, D2 с помощью Мастера функций или вручную (с клавиатуры) введем фор­мулы, как показано ниже, а затем размножим их до 13 строки.

	=ДАТА($A$1;A2;13)	=ДЕНЬНЕД(B2;2)	=ЕСЛИ(C2=5;"Пятница";"")

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

Таблица умножения с областью ввода.

Для области ввода надо на листе ТабУмн добавить но­вые строки. Выделим строки с 1 по 6, в выделенной области щелкнем правой кнопкой мыши и выберем пункт контек­стного меню Добавить ячейки (Вставить - для Excel -2010). Появится 6 пустых строк в верхней части листа.

Самое главное – формулы в ячейках таблицы умножения, которая теперь начнется с 7 строки, автоматически будут пересчитаны (!). В бывшей ячейке В2 (теперь она имеет адрес В8), где была формула =$А2*В$1, появится формула =$A8*B$7. Соответственно изменятся другие формулы листа.

Заполним область ввода надписями, как на рисунке слева, для этого надо вво­дить значения в первом столбце (столбце А), объединять ячейки и задавать формат надписи.

Введем в ячейки D2:D5 единицы и свяжем область ввода с расчетной таблицей: формулы приведены на этом же рисунке. Размножим формулы из ячеек А9 (вниз) и С7 (вправо) до необходимого предела (17-я строка и столбец К). В итоге получим школьную таблицу умножения.

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

Или для нечетных значений аргументов. Или для троек. Или для множителей, кратных 60.

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

Таблица расчета процентов по вкладу.

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

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

Ø первоначальную сумму вклада;

Ø начальное значение процентной ставки по депозиту и шаг ее изменения;

Ø начальное значение периода времени и шаг его изменения.

Процентные ставки будут располагаться в столбце Процент, а периоды времени – в строке Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и от процентной ставки. Предполагаем, что процент по вкладу сложный, начисляется в конце года. В каждом следующем году сумма вклада увеличивается на сумму процента и из нового значения вклада вычисляется процент. При такой схеме сумма вклада на конец периода (P1) рассчитывается по формуле: P1=P0*(1+ r) ⁿ, где P0 – сумма, размещенная на депозите, r – ставка по депо­зиту, n – число периодов (лет). Фрагмент таблицы с расчетными формулами приведен ниже.

 

Изменение внешнего вида новой таблицы по отношению к исходной таблице умножения можно выявить из приведенных выше рисунков. Ниже указана последовательность созда­ния основной расчетной формулы в ячейке В10 вместо исходной формулы =$A8*B$7 и размножение ее на всю область значений. Это еще один способ заполнения формулами диа­пазона ячеек.

1. Выделить диапазон В10:К19. Ячейка В10 останется активной.

2. Ввести знак равенства, щелкнуть по ячейке D2 и нажатием функциональной клавиши F4 задать абсолютную ссылку на эту ячейку ($D$2).

3. Ввести знак умножения (*), открыть круглую скобку, набрать 1 и знак "+".

4. Щелкнуть по ячейке А10 и три раза нажать функциональную клавишу F4 – будет соз­дана смешанная ссылка на эту ячейку ($А10), закрыть круглую скобку. Такая смешанная ссылка означает абсолютную ссылку на столбец А.

5. Ввести знак возведения в степень (^), перейдя в английскую раскладку и нажав Shift+6.

6. Щелкнуть по ячейке В9 и дважды нажать на функциональную клавишу F4 – будет соз­дана смешанная ссылка на эту ячейку (В$9). Такая смешанная ссылка означает абсолют­ную ссылку на строку 9.

7. Завершить ввод формулы нажатием сочетания клавиш Ctrl+Enter. При этом формулами будет заполнен весь выделенный диапазон.

На заполнение формулами таблицы указанным способом уходит около 1 минуты.

 

Данная таблица позволяет изменять сумму депозита, размер процентной ставки, полу­чать результат на определенный временной период. Ниже приведена таб­лица расчетов в ре­жиме отра­жения результатов. Из нее видно, что, разместив вклад под 11% годовых, по­лучите удвое­ние вклада не ранее, чем через 7 лет (строка выделена курси­вом и ячейка обведена овалом).

 

 

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

 

Платежи по займу.

В состав MS Office входит большое число шаблонов, макетов и образцов документов. Их можно найти в меню кнопки Пуск-Создать. В открывающемся окне име­ется несколько вкладок, каждая из которых открывает набор шаблонов определенной тема­тики (например, Базы данных, Презентации, Шаблоны оформления и др.).

Выберем шаблон Рассрочка (Файл à Создать à Образцы шаблонов à Рассрочка). Откроется книга Excel, в ко­торой будет один лист: Таблица рассрочки. Введем значения в область ввода и после щелчка по клавише Enter получим данные по займу. Параметр Всего в счет процентов пока­зывает сумму переплаты.

Если ввести 4 платежа в год (по кварталам) вместо 12, как в приведенной таблице, пере­плата составит уже 48361,48р. (на 2 с лишним тысячи рублей больше). Введя несколько соче­таний исходных данных можно выбрать оптимальный по каким-то критериям вариант займа.

 

Условное форматирование

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

На листе Данные ввести в блок А16:А40 арифметическую прогрессию: ряд чисел от 1 до 25.

Требуется наложить на диапазон А16:А40 формат: если число лежит в диапазоне от 1 до 10, то выводится курсивом; если от 11 до 20, то выводится полужирным курсивом в рамке; если больше или равно 21, то выводится красными цифрами на голубом фоне.

 

Выделить блок А16:А40. Выбрать меню: Главная à Условное форматирование à Создать правило à Форматировать только ячейки, которые содержат…

В диало­говом окне указать для 1 условия: значение между 1 и 10 (заполняется четыре поля ввода – рисунок выше), щелкнуть кнопку "Формат". Появится новое диа­логовое окно с тремя вкладками. На вкладке "Шрифт" вы­брать "курсив" и щелкнуть "ОК". По­сле этого снова вызвать окно условного форматирования и выбрать пункт «Управление правилами». Появится окно Диспетчера правил. Надо добавлять правила для других (указанных выше) диапазонов данных.

Условное форматирование можно задавать формулой. Перейдем на лист 13пт. Введем в Е15:Е45 прогрессию дат: в Е15 запишем 01.01.10, щелкнем по галочке (закончится ввод, поя­вится четыре цифры обозначения года), размножим ячейку вниз до 45 строки – появятся даты от 1 до 31 января 2010 года. Нужно выделить красным цветом ячейку, в которой будет отображаться число, соответствующее дню недели Воскресенье, и голубым цветом – суббот­ние дни.

Выделим диапазон Е15:Е45, выберем команду меню: Главная à Условное форматирование à Создать правило à Использовать формулу для определения форматируемых ячеек.

В диалоговом окне надо указать формулу, как показано на рисунке.

Щелкнуть кнопку " Ф ормат" (показана на рисунке выше), вкладку Заливка и выбрать голубой цвет, щелкнуть ОК. Снова вызвать окно условного форматирования и щелкнуть пункт Управление правилами, затем за­дать параметры (формулу и цвета) для второго условия (воскресенье), щелкнуть ОК и еще раз ОК. В диапазоне дат столбца Е красным цветом будут выделены воскресные дни, а голубым – субботние.

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

 

Номера недель.

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

В ячейку F15 с помощью мастера функций введем формулу: =ЕСЛИ(ДЕНЬНЕД(E15;2)=1;"ПН";"") и размножим ее вниз.

В ячейку G15 введем фор­мулу:

=ЕСЛИ(F15="ПН";НОМНЕДЕЛИ(E15;2);"")

размножим ее вниз.

Эти формулы можно свести в одну и сразу вычислять номер недели:

=ЕСЛИ(ДЕНЬНЕД(E15;2)=1;НОМНЕДЕЛИ(E15;2);"")

В итоге получим значения в столбцах E, F, G – как на рисунке слева. Можно продолжить прогрессию дат в столбце Е до нужного нам предела, например, до 30 июня. Формулы в столб­цах F и G для увеличенного диапазона дат можно размножить двойным щелчком левой кнопки мыши по маркеру размножения из любой ячейки, содержащей формулу.

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

 

Для справки: после ПЗ-3 в книге должны быть листы:

Данные, График, 13пт, Титул, ТабУмн, Вклады.

ПРЕДУСТАНОВЛЕННЫЕ ПРАВИЛА.

1. Правила выделения ячеек. .

В Excel 2007-2010 эти правила содержат условия: Больше, Меньше, Между, Равно, Текст содержит, Дата, Повторяющиеся значения. Названия пунктов говорят за себя, и не нуждаются в подробных описаниях их функционала. Чуть более подробно можно рассмотреть лишь Дата и Повторяющиеся значения.

Дата:

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

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

 

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

Правила отбора первых и последних значений.

Содержит условия: Первые 10 элементов, Первые 10%, Последние 10 элементов, Последние 10%, Выше среднего, Ниже среднего.

 

Гистограммы.

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