Почему-то часто в нашей литературе используется термин «электронная таблица». Это чрезвычайно удобный программный продукт, предназначенный в первую оче-редь для обработки большого массива количественных данных, что нашло свое применение сначала в статистических расчетах.
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 в стандартном виде. В ячейку А2 вводим текст «Анализ ссуды на покупку недвижимости», в ячейку С4 - «Процентная ставка», С5 - «Срок в месяцах», С6 - «Размер ссуды». Заполняем ячейки Д4-Д6 исходными данными. Да, не забудьте отформатировать ячейку Д4 под %, для чего наводим курсор мыши на ячейку, правая кнопка дает меню, выбираете формат ячейки, числовые форматы - процентный. Также для ячейки Д6 выбираем денежный формат. Все видно на screen 2.
Excel, таблицы данных
В ячейку С9 вводим текст «Платежи», а в СЮ формулу. Формулу выбираем из сервиса Excel в меню «Формулы», входим в подменю вставить функцию, находим функцию ПЛТ, пояснение к ней видно внизу - screen 3.
Excel, таблицы данных
Заполняем аргументы функции, как на рисунке 4. Не забудьте перед формулой поставить «-«, чтобы вывести положительное значение, аргумент ставки деленное на 12, поскольку в ячейке Д4 - процентная ставка годовая, заполняйте, как на screen 4.
Нажав ОК, вы получаете в ячейке СЮ сумму ежемесячных платежей. Теперь мы хотим сделать анализ, как меняются суммы ежемесячных платежей в зависимости от процентной ставки от 9 до 11 % с шагом 0,25%. Для этого заполняем столбец В, начиная с ячейки В11, рядом числовых значений. Для этого удобно использовать маркер заполнения, то есть в В11 вводим 9% в В12-9,25%, выделяем мышью обе ячейки. Наводим курсор на нижний правый угол блока, курсор преобразуется в крестик, и протягиваем по длине столбца..
Excel, таблицы данных
Все, осталось совсем немного, пользуемся теперь сервисом Excel, таблицы данных. Для этого выделяем мышью блок ячеек от В10 до С19, входим в меню «Данные», выбираем «Анализ, что если» и «Таблица данных». Вставляем, как показано на иллюстрации 5 адрес вхождение, ОК. И таблица данных заполнена.
Excel, таблицы данных
И это еще не все. Предположим Вы хотите посчитать сумму процентов по кредиту, вводим в ячейку Д10 формулу = (СЮ х Д5) - Д6, результат я выделил рамкой. Опять пользуемся инструментом таблицы данных, выделяем мышью блок ячеек от В10 до Д19, проводим те же операции, что описаны выше, вставляем адрес вхождения Д4, ОК. И снова наша таблица заполнена (screen 6).
Рассмотрим еще более интересный, но усложненный вариант работы с Excel, таблицы данных с двумя вхождениями, то есть в формуле будет меняться уже не один, а целых два ключевых параметра. Результатом у нас будет являться массив данных, который мы уже можем анализировать. Чтобы не усложнять задачу возьмем те же исходные данные, что и в предыдущей статье «Excel, таблицы данных» (ссылка http://avs632a.ru/soft/excel-tablicy-dannyx): процент годовых - 10% (первый ключевой параметр), срок возврата кредита -10 лет (второй ключевой параметр), величина займа 1 миллион рублей. Основной долг и проценты по кредиту выплачиваются равномерно и одинаковыми суммами в течение всего периода. В результате расчетов мы должны получить массив ячеек с расчетными данными. Все манипуляции с табличным процессором я буду снабжать пошаговыми иллюстрациями, чтобы была понятна технология работы с электронной таблицей.
Excel, таблицы данных
Итак, начнем, запускаем программу Excel в ее обычном виде. В ячейке А1 печатаем текст «Анализ ссуды на покупку недвижимости 2-й вариант», С4 - «Процентная ставка», С5 - «Срок в месяцах», С6 - «Величина займа». Я специально выделил жирным и более крупным шрифтом ключевые параметры: процентная годовая ставка по займу и срок возврата кредита (screen 7).
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
