Практикум по информатике
 Скачать 7.84 Mb.
|
|
Глава 3. Работа в среде табличного процессора Excel 2007 §1. Вид окна Excel 2007 Одним из наиболее широко распространенных приложений пакета Microsoft Office является табличный процессор Excel 2007. Файл, сформированный в этом приложении, может иметь любое имя, соответствующее ограничениям, накладываемым операци- онной системой. Его расширение «xls» устанавливается автома- тически. После запуска программы в верхней части экрана появ- ляется окно интегрированного пакета Microsoft Office, включаю- щее строку заголовка, меню и области активной вкладки. Напри- мер, на рис. 3.0 при активной вкладке Главная видим области Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки, Редактирование и соответствующие им кнопки панелей инстру- ментов. Рис. 3.0 Под областями располагается информационная строка. В её левой части находится поле имени, где записывается адрес или 98 имя выделенной ячейки, а в правой части — поле формул, со- держащее информацию, внесенную в выделенную ячейку. В ра- бочей части окна расположена сетка, каждая ячейка которой ха- рактеризуется номерами строки и столбца, отсчитанными от верхнего левого угла. При этом по умолчанию строки маркиру- ются цифрами, а столбцы — буквами или комбинациями букв ла- тинского алфавита. Активное окно табличного процессора Excel 2007 называется рабочим листом, а создаваемый программой файл или документ — рабочей книгой. Если требуется сослаться на ячейку таблицы, расположенной на другом листе, то её адрес записывают в виде Имя_листа!Адрес_ячейки. Можно сослаться на информацию, содержащуюся в другой рабочей книге. Такая ссылка называется внешней и имеет вид [Книга5]Имя_листа! Адрес_ячейки. Имя книги заключается в квадратные скобки. При использовании внешней ссылки должны быть открыты окна обе- их используемых книг. Пользователь имеет возможность удалить ячейку, выбрав Главная—› Ячейки—› Удалить или вставить ячейку при помо- щи команды Вставить. Те же команды дают возможность вставлять и удалять столбцы и строки. При желании сетку можно сделать невидимой. Для этого надо выбрать Вид—› Показать или скрыть—›Сетка (убрать фла- жок). Рабочая книга может содержать несколько рабочих листов. Пользователь имеет возможность добавлять листы с помощью кнопки Вставить лист в нижней части окна за последним лис- том. §2. Выделение ячеек, редактирование информации в ячейках Ввод и обработка информации возможны только в выделен- ных ячейках. Для того чтобы выделить ячейку, необходимо ус- тановить на неё курсор и щёлкнуть левой клавишей мыши. Адрес выделенной ячейки отображается в поле имени, расположенном в верхней части экрана непосредственно над таблицей слева. После ввода информации в ячейку необходимо либо нажать клавишу 99 «Enter», либо перевести курсор в соседнюю ячейку клавишами управления, что свидетельствует о завершении ввода информа- ции. Если после этого требуется произвести редактирование дан- ных в ячейке, не уничтожая их, ячейку выделяют и изменяют ин- формацию в ней в соответствии с обычными правилами редакти- рования текстов. Допускается осуществление редактирования пу- тём ввода информации в поле формул. Не следует забывать, что клавиши со стрелками используются для перемещения курсора из одной ячейки в другую. Поэтому для его перемещения внутри ячейки в процессе редактирования информации следует исполь- зовать мышь. В некоторых случаях приходится выделять не одну, а целый набор ячеек. Если этот набор представляет собой столбец или строку, то для выделения достаточно щелкнуть мышью по соот- ветствующему заголовку. Для выделения листа достаточно щелкнуть мышью по кнопке в левом верхнем углу экрана на пе- ресечении осей координат. Если необходимо выделить произ- вольную прямоугольную область, то достаточно при нажатой кнопке мыши перевести курсор по диагонали из одного угла об- ласти в противоположный. В случае необходимости выделить ячейки, расположенные в различных частях экрана (выделение несвязанной области), надо щелкнуть мышью по каждой из них при нажатой клавише «Ctrl». Выделенная ячейка (область) имеет жирный черный контур с маленьким квадратом в нижнем правом углу. Этот квадрат называется маркером заполнения и играет существенную роль при управлении данными, помещёнными в ячейку. §3. Копирование и перемещение информации, содер- жащейся в ячейках Копирование и перемещение числовых и строковых данных. Функции управляющих клавиш, вид курсора мыши на листе ра- бочей книги Excel 2007 и изменение информации при перемеще- нии курсора зависят от положения курсора относительно контура выделенной ячейки (см. рис. 3.1). К операциям, выполняемым с использованием левой клавиши мыши, относится генерация про- 100 стых и прогрессирующих последовательностей данных. Генера- ция простой последовательности — копирование информации, содержащейся в исходной ячейке или в выделенном блоке, во все последовательно выделяемые ячейки. 1 1 1 1 1 Прогрессирующая последовательность, генерируемая при одновременном нажатии левой клавиши мыши и клавиши «Ctrl», имеет следующий вид: 1 2 3 4 5 причём исходный блок не должен включать более одной ячейки. Операции копирования и перемещения ячеек могут быть выпол- нены также с использованием контекстного меню, которое появ- ляется в случае нажатия правой клавиши мыши. Закономерно мо- гут изменяться при перемещении курсора не только числа, но и, например, даты, дни недели, названия месяцев и т.д. Нечисловые последовательности, которые генерирует про- грамма, образуют так называемые списки, хранящиеся в памяти машины (например, названия месяцев). В справочной системе способ генерации простых и прогрес- сирующих последовательностей в ячейках таблицы с использова- нием левой клавиши мыши назван автозаполнением с использо- ванием смежных ячеек. Действие Вид курсо- ра Результат 1. Указать любое ме- сто в ячейке Выделить ячейку или область 2. Указать на границу или область выделен- ной ячейки Перенести ячейку или область из одного мес- та в другое 3. Указать на границу выделенной ячейки или области при на- жатой клавише «Ctrl» + Скопировать ячейку или область с помо- щью перетаскивания 4. Указать на маркер заполнения + Автоматически гене- рировать последова- тельности данных 101 5. Указать на маркер заполнения выделен- ной ячейки при нажа- той клавише «Ctrl» + + Автоматически гене- рировать прогресси- рующие последовательности данных Рис. 3.1 Аналогично осуществляются операции выбора, копирования и перемещения листов рабочей книги. Как и при работе с отдель- ными ячейками, перемещение листа осуществляется с использо- ванием левой клавиши мыши, а копирование — при нажатой клавише «Ctrl». Если щелкнуть по двум ярлычкам листов при нажатой клавише «Shift», то выделенными окажутся все листы, расположенные между двумя отмеченными. При выполнении каждой из перечисленных процедур изменяется внешний вид курсора. §4. Выполнение арифметических операций Excel 2007 имеет мощный аппарат для проведения математи- ческих и прочих расчётов. Простейшие расчёты — арифметиче- ские действия над числами, помещёнными в соответствующие ячейки электронной таблицы. Для выполнения таких действий в ячейку, где должен находиться результат расчёта, записывают знак «=», свидетельствующий о характере выражения как мате- матической формуле, и адреса ячеек, содержащих исходные дан- ные, соединённые знаками арифметических действий: +, -,*, / , ^ (возведение в степень). Знаки арифметических действий называ- ются арифметическими операторами. Наряду с арифметиче- скими операторами программа даёт пользователю возможность применять логические операторы: «=» равенство, «<» меньше, «>» больше, «<=» меньше или равно, «>=» больше или равно, «<>» не равно, текстовый оператор «&» объединение последова- тельностей символов (амперсанд). Действия программы после записи формул зависят от устано- вок. Обычно вычисления производятся автоматически, и их ре- зультат вставляется в соответствующую ячейку. При желании 102 пользователь может заменить результаты формулами. Для этого надо выбрать Формулы—› Зависимости формул—› Показать формулы (рис. 3.2). Перемещение и копирование формул. Абсолютная и относи- тельная адресации ячеек. Одним из параметров, который может автоматически изменяться, является адрес ячейки, т.е. номер столбца и номер строки, на пересечении которых расположена ячейка. Изменяемые адреса ячеек называются относительными. При обычной записи адреса считаются относительными. Рис. 3.2 Рис. 3.3 Пример задачи с использованием относительных адресов ячеек и копированием формулы показан на рис. 3.3 и 3.4. Здесь формула =С3-D3 вносится в ячейку Е3 и затем копируется в диа- пазон E4:E5. 103 Рис. 3.4 Аналогичные манипуляции можно производить и со строка- ми, столбцами и блоками. Однако бывают случаи, когда адрес ячейки, например С1, при всех манипуляциях должен оставаться неизменным. Такой абсо- лютный адрес должен иметь знак $ перед названием столбца и перед номером строки. Например, =A1*$C$1. Пример использования абсолютных адресов ячеек показан на рис. 3.5 и 3.6. Здесь выражение =С3*$D$19 вводится в ячейку F3 и затем ко- пируется в диапазон F3:G16. В ячейку D19 вводится значение курса доллара, которое ис- пользуется при расчете розничной и оптовой цены в рублях. Что- бы при копировании формулы значение курса доллара не меня- лось, в формуле используется абсолютный адрес ячейки $D$19. 104 Рис. 3.5 Рис. 3.6 105 Задание Самостоятельно скопируйте таблицу на Лист2 и Лист3. Пере- именуйте листы: Лист1 – на «Январь»; Лист2 – на «Февраль»; Лист3 — на «Март»; Лист4 — на «Квартал». Переименовать лис- ты можно с помощью контекстно–зависимого меню командой Переименовать, наведя указатель мыши на вкладку Лист и на- жав правую кнопку. В феврале и марте измените, курс доллара, цены мониторов в рублях изменятся автоматически. Назовите таблицы: «Цены мониторов за январь», «Цены мониторов за фев- раль», «Цены мониторов за март». На листе «Квартал» создайте новую таблицу, в которой укажите розничную цену мониторов в рублях за январь, февраль и март (цены возьмите с соответст- вующих листов, например =Январь! С3). В последнем столбце рассчитайте среднюю цену каждого монитора за квартал. В случае необходимости одна из координат ячейки может быть абсолютной, а другая — относительной. Например, запись =$А2*В$1 свидетельствует об абсолютном характере номера столбца первой ячейки и номера строки второй ячейки. Осталь- ные координаты обеих ячеек относительны. Такая форма записи адреса часто используется при создании таблиц для расчёта зна- чений функций двух аргументов с использованием автозаполне- ния ячеек. Пример нахождения значений таблицы умножения z=x*y с использованием абсолютных адресов ячеек показан на рис. 3–7. Здесь формула =$A3*B$2 записывается в ячейку В3 и затем ко- пируется в диапазон В3:J11. Таким образом, абсолютными дела- ют адреса аргументов, которые в данном примере находятся в столбце А и в строке 2. 106 Рис. 3.7 Задание Создайте таблицу значений функции z=sin 2 (x)+ cos 2 (y). Аргу- менты задайте в интервале от 0˚ до90˚ с интервалом 10˚. Использование осмысленных имён как одна из форм абсо- лютной адресации Excel дает возможность присваивать выделенным ячейкам или областям осмысленные имена и заменять ими координаты. Результат, может быть, достигнут с использованием вкладки Формулы —› Определенные имена —› ввести имя —› Оk. Если осмысленные имена присвоены многим элементам таблицы, це- лесообразно вывести все названия и координаты соответствую- щих им ячеек на какой–либо свободный лист рабочей книги. Вы- вод перечня имён и вставка нужного имени может осуществлять- ся при помощи команды Формулы—› Определенные имена— ›Присвоить имя—› Применить имена. После этого на экране появится диалоговое окно с перечнем имён. По имени можно об- ращаться к ячейкам, расположенным на любом из листов рабочей книги. Можно также присваивать осмысленные имена листам рабо- чей книги вместо порядковых номеров. Для этого надо дважды щёлкнуть мышью по ярлычку листа в нижней части экрана, после чего в диалоговое окно вписать новое название листа и нажать «Enter». Другой способ переименования листа — щёлкнуть по ярлычку правой кнопкой мыши. Появится контекстное меню, со- держащее перечень операций работы с листом (вставка, удале- 107 ние, переименование, выделение). В нём необходимо выбрать со- ответствующий пункт и ответить на последующие вопросы, ко- торые будет автоматически задавать программа. Вторая процеду- ра несколько сложнее, однако более универсальна, чем первая, ибо применима для выполнения каждой операции в контекстном меню. При выполнении расчётов осмысленные имена могут быть использованы наравне с координатами. Например, если ячейкам заранее присвоены имена, то возможна такая запись =Цена*Количество. §5. Создание прогрессий Пользователь имеет возможность производить автозаполне- ние смежных ячеек значениями, образующими арифметическую или геометрическую прогрессию. Для создания прогрессии сле- дует: 1. Ввести значение первого члена прогрессии в первую ячейку области. 2. Выбрать на вкладке Главная команду Прогрессия (рис. 3.8). Рис. 3.8 3. В появившемся диалоговом окне (рис. 3.9) выбрать тип про- грессии (арифметическая, геометрическая, даты, автозаполне- ние). Указать, где располагать прогрессию в строках или в столб- цах. Указать величину шага (знаменателя) прогрессии, устано- вить предельное значение элемента прогрессии. Для последова- тельности дат выбрать единицу измерения (день, рабочий день, 108 месяц, год). В случае использования дат следует установить в ячейках таблицы соответствующий формат (см. ниже). Формирование прогрессии прекратится либо после полного заполнения выделенной области, либо после достижения пре- дельного значения. Рис. 3.9 Прогрессия может быть создана с использованием правой клавиши мыши. Для этого следует: 1. Ввести значение первого члена прогрессии в ячейку. 2. Установить курсор мыши на маркер заполнения этой ячейки. 3. Нажать правую клавишу мыши и протащить её через весь за- полняемый диапазон ячеек. 4. Отпустить правую клавишу и в появившемся контекстном ме- ню выбрать тип заполнения диапазона (например, Прогрессия). §6. Форматы ячеек Форматирование используется для придания ячейкам или тек- сту того или иного внешнего вида и для обработки требуемого типа информации. Наряду с методами форматирования, анало- гичным методам, применяемым при работе с текстовым редакто- ром Word 2007, программа Excel 2007 предоставляет пользовате- лю ряд дополнительных возможностей. Так, наряду с форматиро- ванием текстовой или цифровой информации по центру или по краю ячейки, имеется возможность форматирования по центру 109 выделенной области. В выделенной области строки или столбца в контекстном меню выбирается Формат ячеек—› Выравнива- ние—›Объединение ячеек, и надпись располагается в середине области. Такое форматирование целесообразно применять для создания заголовков таблиц. По умолчанию устанавливается Общий формат информации, т.е. информация, представляется в том виде, в каком её вводят с клавиатуры. Имеется возможность представлять числа в так на- зываемом денежном формате, когда после числа ставится буква «р.» — рубли, и в процентном формате — число, набранное в числовом формате, автоматически умножается на 100 и после не- го ставится знак %. Вид десятичного разделителя определяется настройкой операционной системы. В русифицированных про- граммах это, как правило, запятая. Числовой формат позволяет задать количество десятичных знаков. Дробный формат — пред- ставляет числа обыкновенными дробями. Причём из неправиль- ной дроби будет автоматически выделяться целая часть. Отдель- но следует рассмотреть представление дат. При преобразовании целого числа в дату числу ставится в соответствие количество дней, прошедших после 1.01.1900 года, и путём несложных вы- числений определяется текущая дата. Обратное преобразование даёт возможность определить количество дней, прошедших с на- чала века до указанной даты. Такой сложный способ определения даты упрощает решение задачи вычисления продолжительности интервала времени между двумя событиями. §7. Расчёты с использованием функций Для выполнения сложных расчётов программа включает око- ло сотни различных функций, некоторые из которых будут опи- саны ниже. Нужную функцию можно вызвать, набрав её имя и список аргументов в информационной строке, или выбрать из списка, который выводится на экран при помощи кнопки Вста- вить функцию. Далее открывается диалоговое окно Мастер функций (рис.3.10), в котором перечислены категории функций (Мате- матические, Статистические, Финансовые и т.д.), после вы- 110 бора, которых можно указать название функции, относящейся к выбранной категории. Рис. 3.10 Математические функции Одна из наиболее часто используемых математических функций -суммирование. Вызов функции суммирования с ис- пользованием мастера функций сопровождается появлением ок- на, которое даёт возможность правильно ввести функцию и запи- сать её аргументы (рис. 3.11). Аналогичные окна используются и при вызове других функций Excel. После ввода всех аргументов нажимают кнопку «Ok». Допускается вводить слагаемые, либо как числа, либо как адреса ячеек, в которых эти числа содержат- ся. На панели вкладки Главная имеется кнопка автосуммирова- ния, помеченная знаком Σ , упрощающая процедуру вызова ука- занной функции. При автосуммировании аргументами функции СУММ() являются ячейки, расположенные либо сверху, либо слева от выделенной ячейки и содержащие числовые данные. 111 Рис. 3.11 Среди прочих математических функций чаще других исполь- зуются: СУММКВ(...) — сумма квадратов, аргументами, которой яв- ляются числа или адреса ячеек таблицы. Функция возвращает значение суммы квадратов аргументов. СУММПРОИЗВ(...) — сумма произведений. Её аргументами являются массивы чисел или адресов ячеек таблицы. Функция СУММПРОИЗВ(...) возвращает сумму произведений элементов массивов — аргументов, имеющих одинаковые номера (a1b1c1+a2b2c2+...). Расчёт величины определителя квадратной матрицы произво- дится с использованием функции МОПРЕД. Аргументом являет- ся массив ячеек, в котором находятся элементы матрицы. Напри- мер, МОПРЕД(А1:С3). Среди математических функций следует отметить также ок- ругление, перевод градусной меры углов в радианную и радиан- ной в градусную, тригонометрические функции и др., выполняе- мые в соответствии с обычными математическими формулами. 112 Пример с использованием функций представлен на рис. 3.12 и 3.13, где вычисляются расстояния от начала координат до точек замкнутого теодолитного хода, заданных координатами Х и У. Рис. 3.12 Рис. 3.13 Здесь формула вычисления расстояния вводится в ячейку D3, а затем копируется в ячейки D4:D9. 113 Задание В следующем столбце Е (рис. 3.13) рассчитайте самостоя- тельно расстояние от i–ой точки (i – ваш номер в журнале) до ка- ждой точки хода как корень из суммы квадратов разностей коор- динат. Вычислите расстояние между каждой парой смежных точек хода, длину теодолитного хода и площадь полигона внутри хода по формулам ) ( 2 ) (( ) ( 1 1 1 1 1 2 1 2 1 − = + − = + + − = − + − = ∑ ∑ k n k k k n i i i i i y y x P y y x x L Контроль расчета площади выполните по формуле: ). ( 2 1 1 1 + = − − = ∑ k n k k k x x y P Постройте график полигона по координатам его вершин. Функции даты и времени Другая группа часто используемых функций — функции да- ты и времени. Самая простая из этой группы функций — функ- ция Сегодня(), возвращающая дату, определяемую системными часами компьютера. Эта функция не имеет аргументов. Формат даты можно выбирать при помощи команды Формат —› Ячей- ки. Если в ячейке вместо даты появится бессмысленная комбина- ция символов, следует поменять формат или увеличить ширину ячейки. Другая аналогичная функция — ТДАТА(). В отличие от Сегодня(), ТДАТА() наряду с текущей датой возвращает и время. Имеются функции, возвращающие год, месяц, день недели и т. д. Для определения интервала между двумя датами достаточно из конечной даты вычесть начальную. Например, 08.04.97– 06.04.97—›2. Если даты поменять местами, ответ получится от- рицательный. Допускается использование адресов ячеек, содер- жащих исходную информацию. Логические функции 114 Наиболее распространённой логической функцией является функция ЕСЛИ. По своим возможностям она соответствует ус- ловному оператору в обычных алгоритмических языках. Форма записи этой функции следующая ЕСЛИ(логическое_ выраже- ние; значение_ если_ истина; значение_ если_ ложь). Используются также функции: логическое сложение ИЛИ(логическое_ выражение1; логи- ческое_ выражение2; ..;логическое_ выражениеN); логическое умножение И(логическое_ выражение1; логиче- ское_ выражение2;…; логическое_ выражениеN); отрицание НЕ(логическое_ выражение). Ниже приведен пример с использованием логической функ- ции ЕСЛИ, в котором отбираются лица, достигшие 18 лет и не достигшие 18 лет (рис. 3.14, 3.15). Задание Столбцы «От 20 до 30» и «От 30 до 40» рассчитайте самостоя- тельно. Выведите фамилии в случае попадания в диапазон и вы- ведите пробел в случае не попадания в него. Используйте логиче- скую функцию И для определения попадания в диапазон. Напри- мер, И(E4>=20;E4<30). Рис. 3.14 115 Рис. 3.15 Допускается использование вложенных логических функций. Пусть заданы рост и вес человека. В качестве нормального примем вес, равный 0,9 от роста в сантиметрах минус 100. При весе, отличающемся от нормы не более чем на 2 кг, будем выда- вать сообщение «Норма», при весе, отличающемся от нормы бо- лее чем на 20 кг, – «Обратиться к врачу», в остальных случаях при весе меньшем нормы — “Надо поправиться”, при весе боль- шем нормы — «Надо похудеть». Соответствующий пример приведен на рис. 3.16 и 3.17. Здесь формулы набираются в ячейках E3 и F3, а затем копи- руются в диапазон E4:F7. Рис. 3.16 116 Рис. 3.17 Задание 1. Используя вложенные логические функции, добавить в таб- лицу на рис. 3.14 столбец, в котором люди, не достигшие 18 лет, должны характеризоваться как «дети», люди от 18 до 20 лет – как «юноши», от 20 до 30 лет - как «молодые люди» и от 30 лет и выше - как «взрослые». Добавить в табл. на рис. 3.14 ряд записей так, чтобы в каждую категорию попал хотя бы один человек. 2. Добавить в табл. 3.16 столбец «пол», содержащий указания М - мужчины и Ж – женщины. Расчет нормального веса выпол- нить с учетом пола: если М - 0,9(Рост – 100), иначе 0,8(Рост – 100). Рекомендации тоже выполнить с учетом поля: Если М, в со- ответствии с таблицей 3.17, иначе «норма» это норма+4, «обра- щение к врачу» это ABS(вес-норма)>25. В качестве еще одного примера рассмотрим расчёт суммы на- лога на стоимость имущества, которое переходит в порядке на- следования или дарения. Ставки налога на имущество, переходящее в порядке насле- дования, в зависимости от его стоимости и степени родства на- следника приведены на рис. 3.18. Структура таблицы расчёта налога приведена на рис. 3.19 и 3.20. 117 Наследование Дарение 0) Стоимость имущества менее 850 МРОТ Вне зависимости от степени родст- ва С тавка налога рав- на 0% От 80 до 850 — МРОТ Детям, родителям —3 % стоимости им–ва, пре- выш. 80 МРОТ Другим физическим ли- цам — 10 % ст–сти им–ва, превыш. 80 МРОТ 1) Стоимость имущества от 850 до 1700 МРОТ Наследники 1–й очереди(дети, ро- дители) 5% стоимости им- ва, превышающей 850 МРОТ 23,1МРОТ + 7% стоимо- сти имущества, превы- шающей 850 МРОТ Наследники 2–й очереди(дед, баб- ка, братья, сёст- ры) 10% стоимости имущества, превы- шающей 850 МРОТ 77 МРОТ + 20 % стоимо- сти имущества, превы- шающей 850 МРОТ Другие наслед- ники 15%стоимости им- ва, превышающей 850 МРОТ 2) Стоимость имущества от 1700 до 2550 МРОТ Наследники 1–й очереди 42,5 МРОТ +10% стоимости им-ва, превышающей 1700 МРОТ 82,6 МРОТ + 11% стоимо- сти имущества, превы- шающей 1700 МРОТ Наследники 2–й очереди 85 МРОТ +20% стоимости имуще- ства, превышаю- щей 1700 МРОТ 247 МРОТ + 30% стоимо- сти имущества, превы- шающей 850 МРОТ Другие наслед- ники 170 МРОТ +30% стоимости имуще- ства, превышаю- щей 1700 МРОТ 3) Стоимость имущества превышает 2550 МРОТ Наследники 1–й очереди 127,5 МРОТ +15% стоимости имуще- ства, превышаю- щей 2550 МРОТ 176,1 МРОТ + 15% стои- мости имущества, превы- шающей 2550 МРОТ 118 Наследники 2–й очереди 255 МРОТ +30% стоимости имуще- ства, превышаю- щей 2550 МРОТ 502 МРОТ + 40 % стоимо- сти имущества, превы- шающей 2550 МРОТ Другие наслед- ники 425 МРОТ +40% стоимости имуще- ства, превышающе- го 2550 МРОТ Рис. 3.18 Рис. 3.19 Здесь столбцы A, C, E и G заполняются в соответствии с данными таблицы на рис. 3.18. В ячейку В4 вводится действую- щая величина МРОТ (в сентябре 2007 г. 2300 руб.). Для перевода стоимости имущества из МРОТ в рубли в ячейку В5 вводится формула =А5*В$4 и копируется в ячейки В6 и В7. Налог на пер- вое граничное значение (850 МРОТ) равен 0 (D5). Для вычисления величины налога на второе граничное значение стоимости имущества (1700 МРОТ) для наследников первой очереди в ячейку D6 вводится формула =D5+($B6- $B5)*C5/100. Для вычисления величины налога на следующее граничное значение стоимости имущества (2250 МРОТ) для на- следников первой очереди эта формула копируется из ячейки D6 в ячейку D7. Для вычисления соответствующих граничных вели- чин налога для наследников второй очереди и других наследни- 119 ков содержимое ячеек D6:D7 копируется последовательно в F6:F7 и H6:H7. Рис. 3.20 Для вычисления налога на наследство для наследников первой очереди с любой конкретной стоимости имущества, по- мещаемой в ячейку В9, в D9 вводится соответствующая формула (см. таблицу на рис. 3–20). Для вычисления налога для наследников второй очереди и других наследников формула из ячейки D9 копируется последо- вательно в ячейки F9 и H9. Формула для получения налога на на- следство в зависимости от номера очереди наследников вводится в ячейку В11. Для контроля правильности расчетов полезно ввести в B9 стоимость имущества, соответствующую второй (1700 МРОТ) либо третьей (2250 МРОТ) границам. При этом величина налога, вычисленная по общей формуле, должна равняться величине на- лога, вычисленного для соответствующего граничного значения. Например, для стоимости имущества, равной 2250 МРОТ, долж- ны совпадать налоги, определенные в ячейках D9 и D7, F9 и F7, H9 и H7. 120 Рассмотрим ещё один пример использования вложенных логических функций — расчёт заработной платы. Структура таб- лицы представлена на рис. 3–21. Столбец «Сумма1» в таблице вычисляется по формуле: Сумма1=Оклад/25.6*Рабочие дни. Сумма2=Сумма1+Другие начисления. Удержание в пенсионный фонд составляет 1% от «Суммы 2». Для расчёта льгот на подоходный налог используются дан- ные из таблицы учёта льгот, количество детей и МРОТ. Общий вид формулы вычисления льгот: Л = если(Сумма2 – ПФ + СД <= 15000;2*MМОТ + 2*Д*МРОТ; если(Сумма2 – ПФ + СД <=50000; MМОТ+ Д*МРОТ;0)) , где ПФ – налог в пенсионный фонд, СД – совокупный доход за предыдущий период. Формула вычисления льгот в табл. на рис. 3.22: =ЕСЛИ((F3-G3+J2) <= 15000;2*F$19+2*F$20*F$19; ЕСЛИ (F3-G3+J2<=50000;F$19*1+F$19*F$20*1;0)) Облагаемая сумма = Сумма2 - Пенсионный фонд - Льготы. Совокупный доход = Совок. Доход за предыдущий период + Обл.Сумма. 121 Рис. 3.21 В методических целях подоходный налог рассчитывается на основе шкалы подоходного налога (рис. 3.22), совокупного дохо- да и облагаемой суммы. (После 2002 г. реальный подоходный на- лог составляет 13% от облагаемой суммы). 122 Рис. 3.22 В данном примере ставка ПН и ОС меняется при СД+ОС>50000 рублей и при СД+ОС>150000 рублей. При этом возможны следующие шесть вариантов исчисления ПН. Если СД (за предыдущий период)+ОС<=50000 руб., ПН со- ставляет 12% от ОС. Если СД больше 150000 руб., ПН составляет 30% от ОС. Если СД >50000 и СД+ОС<=150000, ПН составляет 20% от ОС. Если СД <=50000 и СД+ОС<=150000, то ПН с части СД+ОС, превышающей 50000, берётся по ставке 20%, а с остальной части ОС – по ставке 12%. Если СД >50000 и СД+ОС>150000, то ПН с части СД+ОС, превышающей 150000, берётся по ставке 30%, а с остальной час- ти ОС – по ставке 20%. Наконец, в последнем из возможных случаев (его можно уже формально не записывать), если СД <50000 и СД+ОС>150000, то ПН с части ОС, где СД+ОС больше 150000, берётся по ставке 123 30%, ПН с части от 50 до 150000 берётся по ставке 20%, а с ос- тальной части ОС по ставке 12%. Формула расчёта подоходного налога в табл. на рис. 3–22 : =ЕСЛИ(J2+I3<=50000;I3*0,12;ЕСЛИ(J2>=150000;I3*0,3;ЕСЛИ (И(J2>50000;(J2+I3)<=150000);I3*0,2;ЕСЛИ(И(J2<=50000;(J2+I3) <=150000);(J2+I3-50000)*0,2+(I3-(J2+I3- 50000))*0,12;ЕСЛИ(И(J2>50000;(J2+I3)>150000);(J2+I3- 150000)*0,3+(I3-(J2+I3-150000))*0,2;(J2+I3- 150000)*0,3+100000*0,2+(I3-(J2+I3-150000)-100000)*0,12))))). Формулы расчета показаны в табл. на рис. 3.22. Контролем правильности расчетов в табл. на рис. 3.21 служит равенство суммы подоходных налогов по месяцам (K17) и подо- ходного налога на сумму облагаемых сумм (K16). |