Назначение данной книги — научить читателей эффективно использовать мощные средства программы Excel в задачах, относящихся к сфере экономики и организации производства. Материал излагается в доступной форме по принципу от простого к сложному. Рассматриваются такие интересные темы, как расчет доходов, налогов, ведение данных о персонале, его окладах и рабочем времени. Даются советы по организации учета денежных средств в малом бизнесе и домашнем хозяйстве. Особое внимание уделено налогообложению физических лиц в России. Полезным для читателя будет мате
риал о правилах заполнения налоговой декларации. Завершает книгу справочник по функциям Excel, систематизированный как по назначению, так и по алфавиту. Разработанные в книге таблицы помещены на прилагаемую дискету. Книга окажется большим подспорьем как при ведении бизнеса, так и при учете личных доходов и расходов.
Содержание
Глава 1. Подготовка к работе с Excel
Словарь терминов
Запуск Excel
Окно Excel
Строка заголовка
Строка меню
Панели инструментов
Строка формул
Окно рабочей книги
Диалоговые окна
Диалоговое окно Параметры, вкладка Вид
Создание новой книги и папок для хранения информации
Создание новой книги
Сохранение книги
Открытие книги
Организация информации на диске
Создание структуры папок для хранения информации
Глава 2. Простейшие действия над числами
Принятие решения о создании электронной таблицы
Подготовка таблицы к работе
Выбор оптимального масштаба отображения таблицы
Расположение таблицы на рабочем листе
Ввод данных в таблицу
Ввод чисел
Первое знакомство с формулами и функциёми
Формулы
Функции
Мастер функций
Ввод формул с клавиатуры
Пример ввода простой формулы
Форматирование содержимого ячеек
Ввод формул в таблицу, производящую шесть простых действий
Создание ссылок на ячейки области ввода
Ввод формулы сложения
Ввод остальных пяти формул
Оформление таблицы
Цветовое оформление
Линии и рамки
Представление результатов
Числовое форматирование
Вкладка Вычисление диалогового окна Параметры
Округление результатов
Тестирование созданной таблицы
Практическое применение полученных знаний
Расчет НДС
Элементарный расчет налогов и прибыли
Глава 3. От таблицы умножения к элементарным
расчетам денежных потоков
Создание таблицы умножениё
Заполнение вертикального ряда сомножителей
Заполнение горизонтального рёда сомножителей
Заполнение области вычислений
Оформление таблицы
Создание области ввода в таблице умножения
Смещение таблицы
Выделение диапазона ячеек
Управление таблицей из области ввода
Практическое применение таблицы
Таблица расчета процентов по вкладу
Таблица определения влияния инфляции на стоимость денег
Таблица определения реальной стоимости денег
Модуль расчета реальной стоимости денег
Таблица расчета процентов по остаткам на расчетном счете
Таблица расчета сложного процента на сумму вклада
Модуль расчета сложного процента на сумму вклада
Глава 4. Создание табличной базы данных сотрудников
Списки
Формирование заголовка списка
Перенос данных из текстового редактора в электронные таблицы
Обработка перенесенных текстовых данных
Занесение информации в базу данных
Форматирование табличной базы данных
Просмотр табличной базы данных
Работа с окнами
Разделение таблицы на области
Закрепление областей
Фильтрациё списка
Поиск данных в списке
Диалоговое окно Форма
Изменение имени листа
Удаление листов
Вывод таблиц на печать
Предварительный просмотр документа
Выбор размера и расположениё таблицы на странице
Создание колонтитулов
Определение параметров вывода листа на печать
Задание области печати
Запуск процесса печати
Глава 5. Должностные оклады и премии
Создание отдельного списка с указанием окладов сотрудников
Определение количества сотрудников в каждом отделе
Определение количества сотрудников, занимающих определенные должности
Контроль рассчитываемых значений
Изменение должностных окладов
Использование диалогового окна Специальнаё вставка
Применение формул
Использование коэффициентов
Соединение двух списков
Сортировка данных
Проверка данных
Если работник уволен
Сравнение табельных номеров
Сравнение фамилий
Создание сложной формулы методом вложениё
Расчет премии за выслугу лет
Ввод условий для начисления премии
Определение полного количества лет работы на предприятии
Расчет суммы премии
Скрытие столбцов
Формирование приказа о премии за выслугу лет
Составление перечней
Глава 6. Написание числовых данных прописью
День недели прописью
Дата прописью
Модуль, формирующий дату прописью
Преобразование даты в формат Excel
Написание суммы прописью
Алгоритм работы таблицы
Разбивка числа на разряды
Формирование числительных
Формирование названий разрядов
Формирование названия единицы измерениё
Соединение всех компонентов надписи и их текстовая обработка
Тестирование таблицы
Модуль для написания суммы прописью
Зависимости
Использование зависимостей при вложении формул
Минимизациё размера таблицы
Глава 7. Электронный табель учета рабочего времени
Понятие табеля
Двухстрочный табель
Функции двухстрочного табеля
Создание бланка табеля
Заполнение области ввода
Расчетнаё область
Формирование дат в формате Excel в табеле
Определение праздничных дней в табеле
Определение выходных дней в табеле
Определение часов, отработанных в выходные и праздничные дни
Удобочитаемость табеля: управление отображением строк и столбцов
Защита листа
Заполнение табеля
Однострочный табель
Характеристика и круг задач
Определение нормативного количества рабочих часов
Заполнение области ввода
Расчетная область
Глава 8. Учет и налогообложение доходов физических лиц
Доходы физических лиц
В виде материальной выгоды
По договорам страхования и негосударственного пенсионного обеспечения
От долевого участиё в организации (дивиденды)
От продажи имущества
Налоговые вычеты
Социальные налоговые вычеты
Имущественные налоговые вычеты
Профессиональные налоговые вычеты
Стандартные налоговые вычеты
Налог по основному месту работы
Учет стандартных вычетов
Расчеты для одного работника за год
Модуль расчета налога по основному месту работы
Учет дохода и расчет налога за год по месту работы
Основное место работы
Работа по совместительству
Учет всех доходов
Формулы массива
Определение дохода за месяц
Декларация о доходах
Состав электронной модели декларации
Последовательность заполнениё декларации
Ввод данных в декларацию
Глава 9. Учет доходов и расходов в быту и бизнесе
Вы решили вести учет семейных доходов и расходов
Доходы и расходы семьи
Система учета
Журнал регистрации
Анализ данных с помощью сводной таблицы
Создание собственных средств анализа данных
Использование изученных ранее методов
Использование формул массива
Создание модуля с вложенной формулой массива
Применение модуля для анализа данных
Таблица 1: расходы на каждого члена семьи и по статьям
Таблица 2: расходы на каждого члена семьи по статьям
Таблица 3: расходы по статьям с детализацией
Таблица 4: расходы на каждого члена семьи с детализацией
Таблица 5: доходы
Анализ доходов семьи в разные периоды времени
Предварительнаё таблица анализа доходов
Доход члена семьи за конкретный период времени
Учет при мелкотоварном производстве
От журнала регистрации к журналу операций
Таблица 1: покупка и расход материалов
Таблица 2: расход материалов на изделия
Таблица 3: распределение выручки по изделиям
Таблица 4: распределение прибыли по изделиям
Глава 10. Функции рабочего листа
Финансовые функции
Функции даты и времени
Математические и тригонометрические функции
Статистические функции
Функции просмотра и ссылки
Функции баз данных
Текстовые функции
Логические функции
Функции проверки свойств и значений и информационные функции
ОТРЫВОК
Глава 4
Создание табличной базы данных сотрудников
Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, справочники адресов и телефонов, различного рода информацию о магазинах и предлагаемых товарах и т. д.
В этой главе мы расскажем, как создать базу данных, которая содержит информацию о работниках. Такая база данных удобна по следующим причинам:
она обеспечивает удобный метод поиска информации о работнике (фамилия, имя и отчество, место проживания, телефон, должность, дата рождения, количество детей и т. д.);
с ее помощью можно выполнять различного рода анализ, например, определять структуру кадров или причины их текучести;
пользуясь хранящейся в ней информацией, можно быстро формировать должностные оклады, рассчитывать суммы отпускных, начислять зарплату и премии за выслугу лет.
Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на предприятиях учет данных о сотрудниках ведется в электронных таблицах Excel.
Списки
Аналогом простой базы данных в Excel служит список. Список - это группа строк таблицы, содержащая связанные данные. Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, цену за единицу товара и т. д.
Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию (в нашем примере он располагается в строке 1). Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, д
олжны отличаться от параметров, назначенных для строк данных. Для отделения заголовка от расположенных ниже данных применять пустые строки не следует.
Формирование заголовка списка
Предположим, что перечень столбцов списка, который будет применяться при создании базы данных, набит в текстовом редакторе Word (рис. 4.1).
На данном рисунке льготы по ПН (пункт 18) - это льготы, которыми пользуется сотрудник при начислении подоходного налога. Пункт "Совместитель - многодетный", расположенный ниже, содержит сведения о том, является ли данный сотрудник совместителем или многодетным родителем. Справочный столбец предназначен для внесения дополнительной информации.
Перенос данных из текстового редактора в электронные таблицы
Поскольку перечень полей табличной базы данных находится в документе Word, надо переместить его в электронные таблицы. Вот как можно быстро и без ошибок выполнить эту операцию:
1. Выделите перечень в документе Word и скопируйте его в буфер обмена.
2. Перейдите в программу Excel (если она запущена, то это можно осуществить посредством клавиш [Alt+Tab]).
3. Поместите табличный курсор в любую ячейку рабочего листа, расположенную под строкой 1 (в строке 1 будут находиться имена полей табличной базы данных). Допустим, вы выбрали ячейку А4.
4. Произведите вставку текста, находящегося в буфере обмена. В результате заголовки будут расположены друг под другом и займут 21 ячейку рабочего листа.
Обработка перенесенных текстовых данных
Обратите внимание, что все заголовки оформлены в одном формате:
порядковый номер;
точка;
пробел;
непосредственно текст заголовка;
точка с запятой.
Однако из заголовка нам нужен только текст, образующий имя поля списка. Поэтому следующим этапом работы будет очистка текста от лишних символов. Конечно, эту операцию можно произвести вручную, до или после копирования текста в электронные таблицы. Но если записей будет несколько десятков или сотен, то вы потратите на это очень много времени и, скорее всего, внесете ошибки.
Справиться с данной работой можно довольно быстро, если использовать текстовые функции Excel. Алгоритм одного из таких методов представлен на рис. 4.2.
При этом предполагается, что формат перечня заголовков точно соответствует приведенному выше описанию. В противном случае (если, например, после точки вместо одного пробела введены два) алгоритм работать не будет.
Определение длины текста заголовка
Для определения количества символов в тексте, содержащемся в ячейке, используется функция ДЛСТР. Рассмотрим ячейку А4 со вставленным в нее текстом 1. Порядковый номер;. Поместите табличный курсор в ячейку В4, которая находится справа от ячейки А4, и вызовите окно Мастер функций. В списке Категория выберите элемент Текстовые, в списке Функция выделите имя функции ДЛСТР, нажмите кнопку ОК, и на экране появится панель функции ДЛСТР (рис. 4.3).
Данная функция возвращает количество символов в текстовой строке. Синтаксис ее таков:
ДЛСТР(текст)
где текст - это текст, в котором определяется количество символов. Имеющиеся в тексте пробелы также считаются символами.
Введите в поле Текст значение A4, нажмите кнопку ОК, и в ячейке В4 появится следующая формула:
=ДЛСТР(A4)
Таким образом мы узнаем, что в ячейке А4 находится текст, содержащий 20 символов.
Удаление последнего символа из заголовка
Следующий этап нашей работы - удаление символа ";" в конце строки. Выберите из текста, находящегося в ячейке А4, все символы, кроме последнего. Для этой цели следует использовать текстовую функцию ЛЕВСИМВ (рис. 4.4). Она возвращает заданное количество символов текстовой строки, отсчитанных от ее начала, то есть слева. Синтаксис функции имеет такой вид:
ЛЕВСИМВ(текст;количество_символов)
где текст - это текстовая строка, из которой извлекаются символы. Параметр количество_символов определяет, сколько символов должна извлечь функция ЛЕВСИМВ из текста.
Количество извлекаемых символов во втором аргументе функции следует задавать в виде числа. Это число может находиться в ячейке, адрес которой указан в данном аргументе. Введите функцию ЛЕВСИМВ в ячейку С4. Первым аргументом функции является адрес ячейки А4, где расположен исходный текст. В качестве второго аргумента использовано число, которое возвращает функция ДЛСТР, находящаяся в ячейке В4. Но если во второй аргумент ввести только адрес ячейки В4, то в ячейке С4 будет присутствовать все содержимое ячейки А4. Однако для удаления из исходного текста посл
еднего символа во втором аргументе необходимо указать число, которое на единицу меньше количества символов, присутствующих в исходном тексте. Поэтому формула в ячейке С4 должна выглядеть следующим образом:
=ЛЕВСИМВ(A4;B4-1)
Удаление начальных символов из заголовка
Для удаления из исходного текста начальных символов (то есть порядкового номера и пробела) можно использовать текстовую функцию ПРАВСИМВ, аналогичную описанной выше функции ЛЕВСИМВ. Синтаксис функции ПРАВСИМВ такой же, только она отсчитывает символы, начиная с конца указанного текста, то есть справа.
При вводе этой функции в ячейку D4 в качестве исходного текста задаем строку без одного последнего символа, полученную в результате преобразования в ячейке С4.
Количество символов во втором аргументе не является одинаковым для всех формул диапазона D4:D24 и зависит от порядкового номера поля в исходном тексте. Для заголовков с порядковыми номерами от 0 до 9 во втором аргументе должно находиться число, равное количеству символов, рассчитанному функцией ДЛСТР в ячейке B4, уменьшенное на 4:
порядковый номер - 1 символ;
точка после порядкового номера - 1 символ;
пробел после точки - 1 символ;
символ ";", который уже удален предыдущей функцией ЛЕВСИМВ, но учитывается функцией ДЛСТР в ячейке В - 1 символ.
Таким образом, в ячейке D4 у нас будет такая формула:
=ПРАВСИМВ(C4;B4-4)
В заголовках с порядковыми номерами от 10 до 99 необходимо вычесть уже 5 символов, потому что данные номера состоят из двух цифр, для номеров от 100 до 999 - 6 символов и т. д. В данном случае мы создаем формулу, которая нуждается в последующей корректировке второго аргумента.
На рис. 4.5 приведен рабочий лист для преобразования заголовков с формулами, а на рис. 4.6 - результаты преобразований в текстовом виде.
Экономические и финансовые расчеты в Excel. Самоучитель (+дискета). / В. Пикуза, А. Гаращенко - СПб: Питер, 2002. - 400 с.
|