Электронные таблицы Google на службе у бухгалтера и аудитора
Недавно знакомый аудитор поделился следующей проблемой. Регулярно выезжая на проверки к клиентам в другие города, он работает с их информационными базами: формирует отчеты в бухгалтерской программе (чаще всего попадается «1С» разных версий) и экспортирует в Excel. Бывает, что нужно привлечь к работе коллег, оставшихся в головном офисе аудиторской фирмы: разослать им файл, а потом свести результаты работы всех воедино. Согласитесь, при большом объеме данных эта задача — сама по себе не из легких, не говоря уже о том, что важно не запутаться в разных версиях файла. Решить эту проблему поможет один из сервисов популярного интернет-портала Google — «Документы Google», а точнее, его табличный редактор Spreadsheets. Юрий ЦЫГАНОК, главный редактор, сертифицированный бухгалтер-практик (CAP) |
В свое время на страницах «Б & К» были в общих чертах описаны возможности сервиса «Документы Google» (см. «Б & К», 2008, № 3, 4). Этот интернет-сервис находится по адресу
http://docs.google.com и подобно обычному пакету офисных программ содержит в себе табличный редактор, текстовый редактор и средство создания электронных презентаций. То есть в ряду случаев «Документы» (будем называть их так для краткости) в состоянии заменить тот же MS Office, который к тому же стоит немалых денег. Впрочем, здесь нужно сделать оговорку: «Документы» не предназначены для оформления документов, полностью готовых к выводу на печать. Конечно, распечатать таблицу или текст можно (и команда такая в меню имеется), но вот создать печатный бланк, каждая ячейка которого должна иметь на бумаге строго заданную ширину и высоту, — вряд ли (для этого набранный текст или таблицу лучше перенести в обычный офисный пакет и уже там смакетировать и распечатать).Зато у «Документов» есть несомненное преимущество — возможности совместной работы. Чтобы организовать работу нескольких человек над одним и тем же документом или электронной таблицей, вам больше не придется рассылать файлы по электронной почте, а затем, получив обратно результаты труда коллег, сверять их и сводить результаты работы воедино. Достаточно дать доступ к документу или электронной таблице Google всем своим коллегам, которые имеют к нему отношение (для этого они должны быть зарегистрированы в бесплатной почтовой службе Gmail), и тогда несколько человек одновременно, находясь в разных местах, смогут заполнять электронную таблицу, вводить свои данные и выполнять вычисления. Такую возможность высоко оценят аудиторы, работающие на выезде у своих клиентов и нуждающиеся в том, чтобы к работе с данными клиента привлечь своих коллег, оставшихся в офисе. Не говоря уже о том, что любой пользователь может работать со своими таблицами и документами везде, где есть компьютер с доступом к Интернету, не занимаясь постоянным копированием на флешку и переносом файлов с места на место, а также не пытаясь судорожно вспомнить, та ли это версия документа. В «Документах» вам всегда доступна одна версия вашего документа — самая последняя, и при этом с историей всех изменений.
Но давайте обо все по порядку. Вначале, проводя аналоги со знакомым всем MS Excel, я расскажу, как создать в «Документах» новую электронную таблицу, заполнить ее данными, внести формулы и провести вычисления. Затем — как перенести электронную таблицу с отчетом из «1С: Бухгалтерии». А в следующем номере пользователям, которые нуждаются в средствах коллективной работы, поясню, как предоставить доступ к электронной таблице другим пользователям и наладить совместную работу. Наконец, покажем, как выгрузить результаты коллективной работы в «Документах» во внешний файл, например, в формате того же Excel. Итак…
Создание новой электронной таблицы
Приступая к созданию новой электронной таблицы, предполагаем, что вы уже не новичок в использовании сервисов
Google и у вас уже есть там аккаунт (учетная запись). Выяснить, есть ли он у вас, очень просто. Если у вас есть бесплатный почтовый ящик на сервисе Gmail.com (например, buhgalter@gmail.com), значит, у вас уже есть и аккаунт для доступа ко всем остальным сервисам Google, включая «Документы».В таком случае заходим на страницу
http://docs.google.com любым доступным вам способом — то ли непосредственно введя этот адрес в адресную строку, то ли через список сервисов Google в самом верху страницы. Во втором случае, если вы еще не использовали «Документы», то, скорее всего, вам нужно будет открыть страницу со списком всех сервисов (ссылка «Еще — Все продукты >>») и уже из него выбрать то, что вам нужно.Предположим, что вы сделали это, и теперь перед вами главная страница сервиса «Документы» (рис. 1).
Жмем кнопку «
Новый» (она расположена слева вверху, под логотипом Google) и в открывшемся ниспадающем списке выбираем тип документа, который хотим создать. Чтобы создать новую электронную таблицу, нужно выбрать «Spreadsheet» (а если же вам нужно создать текстовый документ, выберите «Document»; если электронную презентацию — «Presentation»).В результате будет создана новая пустая электронная таблица с надписью «
Несохраненная таблица». Можете сразу же приступать к работе с ней.
Ввод данных и вычисления
Электронная таблица Google Spreadsheets по своей структуре очень похожа на привычный
MS Excel.Изначально в новой электронной таблице только один рабочий лист — его ярлычок «
Лист1» вы обнаружите на привычном месте, слева внизу под таблицей. Значок «стрелка вниз» справа от названия листа открывает возможные варианты действия с листом («Удалить», «Создать копию», «Переименовать», «Защита листа», «Сдвинуть вправо>>», «<<Сдвинуть влево»). А слева в углу находится кнопка «Добавить лист».Сам рабочий лист представляет собой заготовку таблицы размером 20 столбцов (
A…T) на 100 строк. Ячейки таблицы, как и в Excel, адресуются в стиле «A1», но в отличие от последнего нельзя задать стиль ссылок «R1C1», при котором столбцы, как и строки, нумеруются цифрами (впрочем, большинству пользователей это и не нужно). При необходимости строки и столбцы можно добавлять.Что примечательно: первую строку Google по умолчанию предлагает использовать для заголовков колонок таблицы. Поэтому эта строка зафиксирована, а линейка прокрутки действует только начиная со второй строки. Если же в вашей таблице ряды данных должны быть расположены не по столбцам, а по строкам, то можно зафиксировать первую колонку с заголовками — вертикальный разделитель разместить между столбцами «
A» и «B» (а горизонтальный, если он не нужен, поднять выше, над строкой 1, — там он станет неактивным).Ввод данных и формул в ячейки таблицы также аналогичен MS Excel. Чтобы что-либо ввести в ячейку, ее следует сделать активной («подсветить» ячейку мышью либо перейти к ней навигационными клавишами со стрелками) и начать ввод.
Если вы хотите ввести формулу, то вначале следует вести в ячейку знак равенства «
=». Далее следует математическое или логическое выражение, которое может использовать ссылки на другие ячейки. Если нужно сослаться на другой рабочий лист, то имя листа и ячейки на ней разделяются восклицательным знаком «!». Например, ссылка на ячейку А1 листа «Лист1» выглядит следующим образом: «=Лист1!A1».Как видите, основные правила работы с электронными таблицами в Google такие же, как и в MS Excel, поэтому подробно на них останавливаться нет смысла. А вот что отличается, так это названия вычислительных функций. Поэтому для вашего удобства ниже приведена таблица соответствия финансовых и некоторых других, наиболее часто востребованных функций MS Excel и Google Spreadsheets.
Некоторые функции MS Excel и их аналоги в Google Spreadsheets
Функция MS Excel | Функция Google Spreadsheets | Что делает |
1 | 2 | 3 |
Финансовые | ||
Чпс() | NPV (Ставка, значение_1, значение_2, ... значение_30) | Чистая приведенная стоимость инвестиции с учетом ряда периодических денежных потоков и ставки дисконта. Ставка — это ставка дисконта за период. Значение_1, значение_2, ... значение_30 — это значения, соответствующие вложениям и изъятиям |
Всд() | IRR(значения, оценка) | Внутренняя норма прибыли на инвестиции. Значения соответствуют значениям движения денежных средств через равные промежутки времени. Хотя бы одно значение должно быть отрицательным (выплаты), и хотя бы одно значение должно быть положительное (прибыль). Значения — это массив значений. Оценка (необязательный параметр) — это расчетное значение. При наличии небольшого числа значений вычисление начнется только после указания начальной оценки |
Пс() | PV(ставка, КОЛ_ПЕР, ПЛТ, БС, тип) | Текущая стоимость инвестиции, вытекающая из серии регулярных выплат. Ставка — это процентная ставка за период. КОЛ_ПЕР — это общее количество периодов выплат. ПЛТ — это регулярный платеж за период. БС (необязательный параметр) — это будущая стоимость, которая останется после совершения последней выплаты. Тип (необязательный параметр) определяет срок выплаты в начале (1) или в конце (0) периода |
КПер() | NPER(ставка, ПЛТ, ТС, БС, тип) | Количество периодов для инвестиции исходя из периодических и постоянных платежей, а также постоянной процентной ставки. Ставка — это периодическая процентная ставка. ПЛТ — это постоянный платеж (аннуитет), выплачиваемый за каждый период. ТС — это текущая (денежная) стоимость последовательности платежей. БС (необязательный параметр) — это будущая стоимость, которая будет достигнута в конце последнего периода. Тип (необязательный параметр) определяет срок выплаты в начале (1) или в конце (0) периода |
Ставка() | RATE(КОЛ_ПЕР, ПЛТ, ТС, БС, тип, оценка) | Постоянная процентная ставка за период аннуитета. КОЛ_ПЕР — это общее число периодов, в течение которых осуществляются выплаты (период выплаты). ПЛТ — это постоянный платеж (аннуитет), выплачиваемый за каждый период. ТС — это денежная стоимость в последовательности платежей. БС (необязательный параметр) — это будущая стоимость, которая будет достигнута на момент окончания периодических выплат. Тип (необязательный параметр) определяет срок выплаты в начале (1) или в конце (0) периода. Оценка (необязательный параметр) определяет предполагаемое значение процента в результате интерактивного вычисления |
Плт() | PMT(ставка, КОЛ_ПЕР, ТС, БС, тип) | Периодический платеж по аннуитету согласно постоянным процентным ставкам. Ставка — это периодическая процентная ставка. КОЛ_ПЕР — это число периодов, в течение которых выплачивается аннуитет. ТС — это текущая (денежная) стоимость последовательности платежей. БС (необязательно) — это будущая стоимость, которую необходимо получить на окончание периодических платежей. Тип (необязательный параметр) определяет срок выплаты в начале (1) или в конце (0) периода |
Математические, статистические, функции даты и прочие | ||
Сумм() | SUM (число_1, число_2, ... число_30) | Складывает все числа в диапазоне ячеек. Число_1, число_2, ... число_30 — до 30 аргументов, сумму которых необходимо найти. Диапазон можно также ввести с помощью ссылок на ячейки |
СуммЕсли() | SUMIF(диапазон, критерии, суммарный_диапазон) | Суммирует ячейки, определенные заданными критериями. Диапазон — это диапазон, к которому применяются критерии. Критерии — это ячейка, в которой отображается критерий поиска, или критерий поиска сам по себе. Суммарный_диапазон — это диапазон, значения в котором суммируются. Если он не указан, суммируются значения, найденные в диапазоне |
СуммПроизв() | SUMPRODUCT(массив 1, массив 2, ...массив 30) | Перемножает соответствующие элементы в заданных массивах и выводит сумму таких произведений. Массив 1, массив 2, ... массив 30 — это массивы, соответствующие элементы которых нужно перемножить. Хотя бы один массив должен входитьв список аргументов. Если задан только один массив, суммируются все элементы массива |
Счет() | COUNT(значение_1, значение_2, ... значение_30) | Подсчитывает количество чисел в списке аргументов. Текстовые значения не учитываются. Значение_1, значение_2, ... значение_30 — это значения или диапазоны для подсчета |
СчетЕсли() | COUNTIF(диапазон, критерии) | Возвращает число элементов, соответствующих определенным критериям, в заданном диапазоне ячеек. Диапазон — это диапазон, к которому применяются критерии. Критерии — это критерии в виде числа или символа, по которым считаются ячейки |
СчетЗ() | COUNTA(значение_1, значение_2, ... значение_30) | Подсчитывает количество значений в списке аргументов. Текстовые значения также учитываются, даже если они содержат пустые строки нулевой длины. Если аргумент является массивом или ссылкой, пустые ячейки внутри массива или ссылки не учитываются. Значение_1, значение_2, ... значение_30 — это до 30 аргументов, соответствующих значениям для подсчета |
СчитатьПустоты() | COUNTBLANK(диапазон) | Возвращает число пустых ячеек. Диапазон — это диапазон ячеек, в котором необходимо подсчитать число пустых ячеек |
Если() | IF (проверка, значение_тогда, значение_иначе) | Определяет правила проведения логической проверки. Проверка — это любое значение или выражение, которое может иметь значение ИСТИНА или ЛОЖЬ. Значение_тогда (необязательный параметр) — это значение, которое возвращается, если логическая проверка имеет значение ИСТИНА. Значение_иначе (необязательный параметр) — это значение, которое возвращается, если логическая проверка имеет значение ЛОЖЬ |
Макс() | MAX (число_1, число_2, ... число_30) | Возвращает максимальное значение в списке аргументов. Число_1, число_2, ... число_30 — это числовые значения или диапазоны |
Мин() | MIN (число_1, число_2, ... число_30) | Находит минимальное значение среди параметров |
Округл() | ROUND(число, счет) | Округляет заданное число до определенного количества знаков после запятой согласно математическим правилам. Счет (необязательный параметр) — это число разрядов, до которого необходимо округлить заданное число. Если параметр счета отрицательный, округляется только целая часть числа. Она округляется до позиции, установленной счетом |
Год(Дата), Месяц(), День() | YEAR(дата), MONTH(дата), DAY(дата) | Эти функции выделяют из даты номер года, месяца и дня. Например, значение «YEAR (“25.11.2007”)» равно «2007», «MONTH(“25.11.2007”)» равно «11», «DAY(“25.11.2007”)» равно «25». Кавычки в данном случае обязательны. Разумеется, в качестве параметра можно указать ячейку рабочего листа, в которой записана дата |
Остат() | MOD(делимое, делитель) | Возвращает остаток после деления заданного числа на делитель. Делимое — это число, которое делится на делитель. Делитель — это число, на которое делится делимое. |
Сегодня() | TODAY() | Возвращает текущую системную дату компьютера. Значение обновляется при перерасчете документа |
Текст() | TEXT(число, формат) | Преобразует число в текст заданного формата. Число — это числовое значение, которое необходимо преобразовать. Формат — это текст, который определяет формат. Используются разделители десятичных и тысячных разрядов в зависимости от параметров языка, указанных в формате ячейки. |
Впр() | VLOOKUP(критерий_поиска, массив, индекс, порядок_сортировки) | Выполняет вертикальный поиск со ссылкой на соседние ячейки справа. Если заданное значение содержится в первом столбце массива, значение возвращается в строку указанного столбца массива, определенную индексом. Критерий_поиска — это точное значение, по которому будет выполняться поиск в первом столбце массива. Массив — это ссылка, которая должна включать не менее двух столбцов. Индекс — это номер столбца в массиве, содержащем значение, по которому выполняется поиск. Первый столбец имеет номер 1. Порядок_сортировки (необязательный параметр) указывает, нужно ли сортировать первый столбец массива по возрастанию |
Электронную таблицу со всеми ее данными и формулами, которую вы создадите, нужно сохранить. Но сохраняется она не на вашем компьютере, а непосредственно на сервере Google, там же, где вы с ней и работали. И поверьте, эти сервера много крат надежнее любого настольного компьютера. Для того чтобы сохранить таблицу, нужно в меню «
Файл» выбрать команду «Сохранить и закрыть» и в ответ на вопрос «Сохранить таблицу как:» ввести имя вашей таблицы. После этого вы сможете работать с ней везде, где есть компьютер с подключением к сети Интернет.
Загрузка в «Документы Google» существующих файлов
Если вам нужно работать в «Документах» с уже существующей электронной таблицей, документом или презентацией MS Office, то прежде всего их нужно загрузить в Google.
Есть определенные требования к типу и размеру файлов, которые можно загружать в «Документы». В частности, электронные таблицы должны иметь размер до 1 Мб и храниться в файлах Microsoft Excel (*.xls, *.xlsx) и OpenDocument (*.ods). Есть еще одни допустимый формат — текст с разделителями-запятыми (*.csv), но он распространен гораздо меньше.
Загрузить любой из таких файлов не составит труда. Для этого, находясь в главном меню «Документов», нужно нажать кнопку «
Загрузить» (рис. 1). Затем на следующей странице в секции «Загрузить файл» нужно нажать кнопку «Обзор», найти и выбрать нужный файл, а затем нажать здесь же кнопку «Загрузить файл» (рис. 3).В результате ваш файл окажется в Google, пригодный для дальнейшей обработки или предоставления совместного доступа другим пользователям.
Команда загрузки файла доступна не только в главном меню «Документов», но и в каждом из приложений: табличном редакторе, текстовом редакторе и т. п. Для этой цели в меню «
Файл» есть команда «Импорт…». Если ее выбрать, появится окошко «Импорт файла» с предложением указать загружаемый файл. Достаточно найти и выбрать нужный файл, а после того как Google немного подумает и сообщит вам «Файл успешно импортирован», нажать на ссылку «Открыть сейчас».
перенос данных из
«1С: Бухгалтерии 7.7»
Как уже было сказано выше, возможности «Документов Google» по организации совместной работы с данными очень востребованы аудиторами (именно из такой потребности и родилась эта статья). Данные могут быть получены из любой бухгалтерской программы, позволяющей экспортировать их в файлы электронных таблиц. Есть такая возможность и в «1С: Бухгалтерии» различных версий. Начнем с версии 7.7.
Итак, в «1C» формируем нужный отчет, когда он готов, даем команду «
Файл — Сохранить как», вводим название файла (или соглашаемся с названием по умолчанию), место его сохранения и выбираем тип файла «Таблица Excel (*.xls)», после чего нажимаем кнопку «Сохранить».В принципе можно экспортировать отчет и в текстовый формат *.txt, но тогда будут потеряны такие параметры форматирования, как размеры и начертание шрифтов, ширина колонок, границы и т. п. Если же вы выберете экспорт в формат «
HTML Документ (*.htm)», то потом в «Документах» сможете открыть его только в текстовом редакторе. Понятно, что в этом случае вы не сможете обрабатывать численные данные отчета. Так что наиболее предпочтительным форматом экспорта отчетов «1С» для дальнейшего использования в табличном редакторе Google SpreadSheets является именно формат Excel (*.xls).Следующий нюанс, связанный с экспортом, заключается в том, что «1С: 7.7» сохраняет данные в формат Excel версии 5.0/95. Это очень старый формат, и при переносе в «Документы» тексты, набранные кириллицей, будут отображаться некорректно.
Чтобы избежать этого, необходимо открыть сохраненный файл в Excel и выполнить команду «
Файл — Сохранить как». В открывшемся окне вы убедитесь, что тип файла, в котором ваш отчет был сохранен по умолчанию, — «Книга Microsoft Excel 5.0/95 (*.xls)». Нужно выбрать здесь более новый универсальный формат «Книга Microsoft Excel 97-2003 и 5.0/95 (*.xls)» (он идет следующим по порядку в ниспадающем списке типов файлов) и нажать кнопку «Сохранить».Сохраненный таким образом файл можно смело загружать в табличный редактор Google Spreadsheets.
Перенос данных из «1С: Бухгалтерии 8.0»
Здесь почти все так же, как и в предыдущей версии, с небольшим отличием: для выгрузки сформированного отчета следует воспользоваться командой «
Файл — Сохранить копию…» (именно «Сохранить копию…», а не «Сохранить как…»), выбрав тип файла «Лист Excel (*.xls)».Как и предыдущая версия, «1С: 8» сохраняет данные в формат Excel версии 5.0/95. Поэтому для правильного переноса кириллицы в табличный редактор Google Spreadsheets таблицу необходимо пересохранить в более новый универсальный формат «
Книга Microsoft Excel 97-2003 и 5.0/95 (*.xls)».Порядок дальнейшей загрузки электронной таблицы в табличный редактор Google Spreadsheets идентичен описанному выше в подразделе о переносе данных из версии «1С: 7.7».
Перенос данных из «1С: Бухгалтерии 8.1»
В отличие от версии 8.0 в версии 8.1 уже на этапе экспорта отчета можно выбрать версию формата таблиц Excel: 95 или 97. Естественно, для наших с вами целей — переноса данных в табличный редактор Google Spreadsheets — нужно выбрать более поздний тип файла «
Лист Excel97 (*.xls)».Порядок дальнейшей загрузки электронной таблицы в табличный редактор Google Spreadsheets идентичен описанному выше в подразделе о переносе данных из версии «1С: 7.7».
Предоставление совместного доступа
Как происходит процесс совместной работы?
Прежде всего вы открываете новый документ в «Документах Google» или пересылаете туда один из документов со своего компьютера. Как это сделать, описано выше.
После открытия файла необходимо зайти в раздел «
Совместный доступ», который находится в верхнем правом углу экрана. Откроется окно, изображенное на рис. 4.Далее выбираете из своей адресной книги Gmail людей, которых хотите пригласить к работе, и отправляете им приглашения или просто вводите их адреса в поле «
Пригласить».Перед отправкой нужно уточнить права приглашаемых: будут они соавторами или только читателями, смогут ли потом сами приглашать других пользователей (о возможных правах — чуть позже).
Приглашенные получают
письмо вида:«
Вам предоставлен доступ к документу "журнал":http://spreadsheets.google.com/ccc?key=t55 evnzOr6KlR8VGDgfPaAA& inv=alexanlevin@gmail.com&t=8629511237735092161&guest
Этот документ не прилагается к письму, а хранится в Интернете, в службе "Документы Google". Чтобы открыть документ, перейдите по приведенной выше ссылке.»
Когда ваш коллега получит такое письмо, он поймет, что его приглашают к работе над документом, перейти к которому можно по указанной ссылке. Причем перейти по этой ссылке может не кто угодно, а только обладатель аккаунта (учетной записи) Google, указанного в свойствах этого документа. Остальным будет выдана ошибка. Так что, даже если письмо по ошибке придет постороннему человеку, он не сможет получить доступ к конфиденциальной информации (каковой, естественно, является бухгалтерская информация клиента, с которой работает аудитор).
Ну что ж, на сегодня достаточно. Полагаю, изложенного в статье достаточно, чтобы показать: Google Spreadsheet (да и вообще «Документы Google») является не только полноценным табличным редактором, но и инструментом коллективной работы с электронными таблицами. А такую возможность вам не предоставит ни один пакет офисных программ, будь то MS Office, OpenOffice или любой другой.