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

Excel

Excel

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

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

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

Немного истории. Сама идея табличного процессора появилась более 50 лет назад. Я же работал с различным программным обеспечением: SuperCalc, Lotus 1-2-3, Quattro Pro и, наконец, Excel различных версий. Все это программное обеспечение от различных фирм построено в принципе одинаково, отличаясь правилами написания формул, сервисом, определенными «фишками».

Самой распространенной «электронной таблицей» по праву считается Excel корпорации Microsoft. Поэтому в своих заметках я буду рассматривать некоторые приемы работы с этой программой. Кто хотя бы раз пользовался этим табличным процессором, помнит, что при загрузке программы перед вами появится основное окно программы.

Основное в табличном процессоре массив ячеек, который может заполняться цифрами, формулами, функциями, текстом и прочее. Ячейки объединены в строки и колонки, каждая из которых имеет свой адрес, состоящий из названия колонки А или В, или С и номера строки. В интернете бесплатно распространяется масса книг, учеб-ников, самоучителей, поэтому при желании можно на начальном уровне все освоить и без моих заметок. Мой наставник при первом знакомстве спросил: « С Лотусом работал, тогда запомни, что формула в ячейки начинается не со знака +, а со знака =». На этом я и закончил бы свою первую заметку, она была посвящена истории и необходимости изучения табличных процессоров для людей различных профессий.

Создание шаблона

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

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

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

Как создать шаблон для табличного процессора. Я делаю это в три этапа.

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

Второе. Заходите в основное меню через кнопку Office, как показано на screen 1.

Создание шаблона

Создание шаблона

Далее «сохранить как» и выбираете «Книга Excel», называете шаблон должным образом и сохраняете его на Рабочем столе. Все шаблон или новая форма готова.

Третье. Теперь при работе с шаблоном табличного процессора Excel, вам необходимо будет открыть форму и сразу сохранить ее под оригинальным именем книги в необходимом месте, например в Папке N под именем «Статистика форма ЗП за ноябрь». Далее вводите свои цифровые данные и обрабатывайте их необходимым образом.

Excel, таблицы данных

А сейчас речь пойдет об интересном приеме работы с табличным процессором. А именно об анализе «что - если» для того, чтобы посмотреть, как влияет изменение одного из цифровых значений, входящего в формулу, на целый массив ячеек. «Эка невидаль» - скажет кто-то. Что тут такого удивительного? Мне кажется есть. Прием называется таблицы данных, который обеспечивает быстрое вычисление по заданной ранее формуле. Технология работы описана в «хелпере», но туманно, на мой взгляд.

Я рассмотрю в этой статье создание такой таблицы с одним вхождением, то есть меняться в формуле будет один ключевой параметр. Допустим, нам надо провести анализ ссуды на покупку недвижимости под определенный процент годовых, короче, ипотека. Исходные данные следующие: процент годовых -10% (ключевой) параметр, срок возврата кредита - 10 лет, величина займа 1 миллион рублей. Проценты по займу выплачиваются равномерно и одинаковыми суммами.

Excel, таблицы данных

Excel, таблицы данных

Запускаем электронную таблицу Excel в стандартном виде. В ячейку А2 вводим текст «Анализ ссуды на покупку недвижимости», в ячейку С4 - «Процентная ставка», С5 - «Срок в месяцах», С6 - «Размер ссуды». Заполняем ячейки Д4-Д6 исходными данными. Да, не забудьте отформатировать ячейку Д4 под %, для чего наводим курсор мыши на ячейку, правая кнопка дает меню, выбираете формат ячейки, числовые форматы - процентный. Также для ячейки Д6 выбираем денежный формат. Все видно на screen 2.

Excel, таблицы данных

Excel, таблицы данных

В ячейку С9 вводим текст «Платежи», а в СЮ формулу. Формулу выбираем из сервиса Excel в меню «Формулы», входим в подменю вставить функцию, находим функцию ПЛТ, пояснение к ней видно внизу - screen 3.

Excel, таблицы данных

Excel, таблицы данных

Заполняем аргументы функции, как на рисунке 4. Не забудьте перед формулой поставить «-«, чтобы вывести положительное значение, аргумент ставки деленное на 12, поскольку в ячейке Д4 - процентная ставка годовая, заполняйте, как на screen 4.

Нажав ОК, вы получаете в ячейке СЮ сумму ежемесячных платежей. Теперь мы хотим сделать анализ, как меняются суммы ежемесячных платежей в зависимости от процентной ставки от 9 до 11 % с шагом 0,25%. Для этого заполняем столбец В, начиная с ячейки В11, рядом числовых значений. Для этого удобно использовать маркер заполнения, то есть в В11 вводим 9% в В12-9,25%, выделяем мышью обе ячейки. Наводим курсор на нижний правый угол блока, курсор преобразуется в крестик, и протягиваем по длине столбца..

Excel, таблицы данных

Excel, таблицы данных

Все, осталось совсем немного, пользуемся теперь сервисом Excel, таблицы данных. Для этого выделяем мышью блок ячеек от В10 до С19, входим в меню «Данные», выбираем «Анализ, что если» и «Таблица данных». Вставляем, как показано на иллюстрации 5 адрес вхождение, ОК. И таблица данных заполнена.

Excel, таблицы данных

Excel, таблицы данных

И это еще не все. Предположим Вы хотите посчитать сумму процентов по кредиту, вводим в ячейку Д10 формулу = (СЮ х Д5) - Д6, результат я выделил рамкой. Опять пользуемся инструментом таблицы данных, выделяем мышью блок ячеек от В10 до Д19, проводим те же операции, что описаны выше, вставляем адрес вхождения Д4, ОК. И снова наша таблица заполнена (screen 6).

Рассмотрим еще более интересный, но усложненный вариант работы с Excel, таблицы данных с двумя вхождениями, то есть в формуле будет меняться уже не один, а целых два ключевых параметра. Результатом у нас будет являться массив данных, который мы уже можем анализировать. Чтобы не усложнять задачу возьмем те же исходные данные, что и в предыдущей статье «Excel, таблицы данных» (ссылка http://avs632a.ru/soft/excel-tablicy-dannyx): процент годовых - 10% (первый ключевой параметр), срок возврата кредита -10 лет (второй ключевой параметр), величина займа 1 миллион рублей. Основной долг и проценты по кредиту выплачиваются равномерно и одинаковыми суммами в течение всего периода. В результате расчетов мы должны получить массив ячеек с расчетными данными. Все манипуляции с табличным процессором я буду снабжать пошаговыми иллюстрациями, чтобы была понятна технология работы с электронной таблицей.

Excel, таблицы данных

Excel, таблицы данных

Итак, начнем, запускаем программу Excel в ее обычном виде. В ячейке А1 печатаем текст «Анализ ссуды на покупку недвижимости 2-й вариант», С4 - «Процентная ставка», С5 - «Срок в месяцах», С6 - «Величина займа». Я специально выделил жирным и более крупным шрифтом ключевые параметры: процентная годовая ставка по займу и срок возврата кредита (screen 7).

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Excel, таблицы данных

Далее, используя стандартные формулы электронной таблицы, вычисляем в ячейке А12 сумму равномерных платежей для указанных выше заданных условий, как показано на иллюстрациях 8-10. Для этого входим в меню формулы и, используя функции ABS (рассчитывает модуль числа) и ПЛТ (определяет сумму платежей в месяц исходя из принятых исходных данных), получаем искомое значение. В командной строке и на выноске видна расчетная формула, которую вы должны ввести в ячейку А12, используя мастер формул. Затем все просто. Заполняем столбец А (А13:А21) линейкой процентных ставок от 9,00 до 11,00%, а 12-ю строку - линейкой второго ключевого параметра. Срок возврата кредита определяем от 100 месяцев до 150 месяцев. Теперь, имея в ячейке А12 расчетную формулу, а в столбцах и строках изменения ключевых параметров можно получить целевой массив ячеек Excel, таблицы данных. Для этого выделяем интервал ячеек, в нашем примере это A12:G21. Используя меню данные команды таблица, проводим заполнение таблицы, как показано на screen 11-13. При этом важно понимать, что значения, полученные в результате обработки таблицы данных, представляют собой цифровой единый массив, и их нельзя редактировать поодиночке. При попытке это сделать табличный процессор выдаст предупреждение об этом, и не будет проводить изменения данных. Проверку проведенных нами действий легко сделать, численное значение в ячейке А12 равно значению в ячейке D17.
Андрей САВИНОВ

http://avs632a.bu