ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL
Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных — это может быть CRM, ERP система или просто таблица в Экселе
Создание управленческой таблицы
ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL
Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных — это может быть CRM, ERP система или просто таблица в Экселе.
Рассмотрим довольно простую, но правильную структуру организации учета.Предположим, что у нас несколько источников доходов: могут быть как отдельные виды деятельности, так и филиалы одной сети.
Инструментом ведения учета в рамках примера будут Гугл Таблицы.
Рассмотрим очень простую, но правильную структуру организации учета.
Предположим, что у нас несколько источников доходов — могут быть как отдельные виды деятельности, так и филиалы одной сети.
Инструментом ведения учета в рамках примера будут Гугл Таблицы.
1. Создаем таблицу, в которую будут записываться основные данные:
Таблица выглядит как реестр операций. В нее будут записываться все операции прихода и расхода на каждом из объектов деятельности.
Каждую операцию будем относить к наличному или безналичному расчету, поэтому для удобства анализа все операции группируем по статьям бюджета. Необходимо выделить основные направления движения денежных средств организации
2. Сделаем ввод данных максимально удобным и стандартизированным
(
это очень важно!) Дату можно вводить выбором из выпадающего календаря. Для этого выделяем столбец с датой, выбираем пункт меню «Данные» на панели управления и в выпадающем меню выбираем пункт «Проверка данных»
В поле выбора правил проверки выбираем значение «Дата» из выпадающего списка:
Нажимаем кнопку «Сохранить» . Теперь двойным мыши нажатием в столбце ввода даты появляется календарь:
Организуем выбор объекта из выпадающего списка. Для этого есть два варианта:
Вариант 1: Выделяем столбец «Объект», выбираем пункт меню «Данные», «Проверка данных».
В поле выбора правил выбираем «Значение из списка» и вписываем через запятую наименования объектов
Получаем выпадающий список с введенными значениями:
Вариант 2: Список объектов хранится в таблице на любом из листов. В примере это будет лист «Справочник»
Выделяем столбец «Объект» на листе «Данные». Переходим в пункт меню «Данные», выбираем «Проверка данных» и в поле правила выбираем «Значение из диапазона»
Далее указываем диапазон, содержащий нужные нам значения наименований объектов:
Далее создаем аналогичным образом выпадающий список для столбца «Тип операции» и «Тип платежа». В рамках примера предполагаются операции прихода и расхода, а платежи наличные и безналичные. По очереди выделяем столбцы, выбираем «Проверка данных» в пункте меню «Данные», выбираем правило «Значение из списка» и вводим нужные значения через запятую. Получаем выпадающие списки:
Статьи бюджета удобнее вывести в справочник. Там их будет удобнее редактировать и дополнять. Выделяем столбец «Статьи бюджета». Переходим в меню «Данные», «Проверка данных». Выбираем «Значения из диапазона» и указываем диапазон
Теперь осталось только привести в порядок таблицу, чтобы она имела презентабельный вид. Сперва нужно удалить проверку с заголовка таблицы. Выделяем всю строку заголовка, выбираем пункт меню «Данные», «Проверка данных» и нажимаем «Удалить проверку» в появившемся диалоговом окне
Далее задаем внешний вид:
Ввод данных и разграничение прав пользователей
Ввод данных и разграничение прав пользователей
Теперь в таблицу можно вводить данные. Гугл таблицы удобны тем, что ими может пользоваться одновременно почти неограниченное число пользователей, для которых администратор распределяет права доступа. В правом верхнем углу располагается пункт меню «Настройки доступа».
Пользователь вводит логин аккаунта Gmail и определяет права:
В расширенных настройках можно определить полномочия делегируемых прав
Можно включить доступ по ссылке, который в свою очередь можно предоставить с правом редактирования и без редактирования
Просмотр истории изменений и откат таблицы
Просмотр истории изменений и откат таблицы
В верхней части таблицы отображается информация о времени последнего изменения в таблице и аккаунте, который их произвел.
В гугл таблицах предусмотрена возможность просмотра истории изменений. область просмотра истории активируется нажатием на строку с записью о статусе сохранения документа
Перед пользователем открывается таблица, в которой цветом выделены изменяемые области, а в окне справа отображены время изменения и пользователь, который внес редакцию
Анализ данных и отчетность
Анализ данных и отчетность
Рассмотрим распространенный вариант анализа данных операционной деятельности компании. Сформируем таблицу по основным статьям бюджета в разрезе каждого месяца по каждому объекту в отдельности:
Верхняя таблица содержит суммарные результаты всех нижних таблиц, в которых должна отображаться деятельность каждого из объектов. Для каждого объекта будем подтягивать данные из листа «Данные» формулой «СУММЕСЛИМН».
Функция СУММЕСЛИМН суммирует ячейки, удовлетворяющие заданному набору условий.
Вид функции: =СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1; Диапазон условия 2; Условие 2;…)
Поехали. Сперва добавим на лист «Данные» столбец с названием «Месяц» и выведем формулой порядковый номер месяца из значения даты. Встаем курсором в ячейку H2 и вводим простую формулу: =МЕСЯЦ(A2). Протягиваем ее до конца таблицы:
Далее приступаем к самому главному. Перемещаемся на лист отчета. Встаем в ячейку D10 и вводим в строке формул «=СУММЕСЛИМН(…» Выделяем на листе «Данные» столбец F с суммами и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F;
Далее выделяем столбец с критериями для суммирования Е – статьи бюджета и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F; 'Данные ' ! $E : $E;
Выделяем условие отбора критериев – ячейка С10 и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F
; 'Данные ' ! $E : $E
;$C10;
Далее указываем второй столбец с критериями – столбец В с наименованиями объектов и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;
И указываем наименование объекта в ячейке В10 в качестве критерия и фиксируем перемещение по столбцам и строкам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B; $B$10;
Далее добавляем условие отбора данных по месяцам. В качестве критериев отбора выделяем столбец H и фиксируем перемещение по столбцам, а в качестве условия выбираем ячейку D2 заголовка таблицы отчета и фиксируем перемещение по строкам. Получаем формулу:
=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;$B$10;
' Данные ' ! $H : $H;D$2) Протягиваем формулу на все статьи бюджета и каждый месяц в таблице объекта 1
=СРЗНАЧ(D10:O10) Аналогичным образом считаем итоги, используя функцию «СУММ». Аргументом формулы является набор или массив данных. Получаем заполненную расчетами таблицу:
Данные в таблице имеют разный формат. Приведем все к одному виду, оставив в таблице только целые числа. Выделяем всю область значений таблицы, переходим в пункт меню «Другие форматы» и выбираем «Число»
Нажимаем два раза на кнопку «Уменьшить число знаков после запятой»
Получаем отчет по объекту 1:
Производим аналогичные вычисления для других объектов. Получаем отчет следующего вида:
Считаем сумму, складывая данные бюджета каждого объекта:
Получаем отчет по всем объектам деятельности в разрезе статей бюджета на каждый месяц года:
В данном кейсе рассмотрена укрупненная схема организации управленческого учета компании. Информация на листе «Данные» может представлять собой данные в системе учета или данные которые формируются вручную из разных источников. Лист отчет представляет собой отчет о прибылях и убытках. Отчет можно отнести к данным для первичного анализа. По разбросу его значений можно выявить положительные или отрицательные факторы и при необходимости искать причины в других источниках информации. В отчет можно добавить сравнение с аналогичными данными прошедших периодов, сравнение с плановыми значениями, а так же расчеты таких показателей как рентабельность, валовая прибыль и т. д.
Источник: https://helpexcel.pro/org__uchet
Выпадающий список в Гугл таблицах — как сделать и для чего нужен
В данной статье мы научимся делать выпадающий список в Гугл таблицах, потренируемся его применять вместе с условным форматированием, используя встроенные инструменты Google Sheets.
39 структурированных видеоуроков, которые научат вас пользоваться таблицами
Для чего же нужны выпадающие списки в Гугл таблицах?
Ну, во-первых списки очень облегчают работу с большим количеством одинаковых данных, когда вам в различных колонках необходимо использовать одни и те же значения, например количество проданных товаров в разные периоды времени. Товары-то одни и те же, а вот периоды разные, вот в таком случае к нам и спешат на помощь выпадающие списки.
Во-вторых, использование списков с заранее заготовленными значениями исключают ошибки при введении одинаковых данных. И потом, когда вам однажды понадобится делать проверки данных формулами, то допущенная помарочка в слове или цифре может натворить весьма немало неприятностей в расчетах. Тогда как списки из введенных единожды правильно данных исключат подобные казусы.
Как сделать простой выпадающий список в Гугл таблицах
Чтобы реализовать выпадающий список красиво и удобно (не испортив внешний вид таблицы), мы будем использовать два листа. Для этого давайте сперва добавим второй лист как это описано тут и переименуем их, как это описано в соответствующей статье здесь.
Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.
Теперь перейдем на наш главный лист Результат, где мы будем делать сам выпадающий список. Поставим курсор где нам необходимо, в моем случае разницы нет и я размещу выпадающий список в ячейке A3.
Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:
В котором мы видим следующие пункты:
- Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
- Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными. - Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
- Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки». - Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»
Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:
Выпадающий список в Гугл таблицах с использованием условного форматирования
Сделать-то мы сделали выпадающий список, но теперь нам необходимо потренироваться как его использовать в работе.
Теперь добавим немного магии и воспользуемся условным форматированием для того, чтобы мы могли налету отличать данные которые выбраны в той или иной ячейке с выпадающим списком.
Допустим у нас есть некие данные, в нашем случае это Ягоды, Фрукты и Овощи. У вас это могут быть другие данные, но не это главное. Если у нас приличное количество выпадающих списков с различными данными, то выглядит все достаточно запутанно и вообще поди пойми где и что.
Чтобы как-то разбавить эту серую массу данных, нам потребуется инструмент условного форматирования. При помощи его мы в несколько кликов раскрасим наши товары в соответствующие цвета и нам будет значительно проще различать где какой вид продукта выбран.
Затем пройдем путь в меню: Формат -> Условное форматирование или кликнем правой кнопкой мыши и в открывшемся контекстном меню выберем Условное форматирование.
В открывшемся окне справа мы увидим что мы применять будем форматирование к диапазону A1:C20. Ниже в форме Форматирование ячеек выберем Текст содержит, еще ниже в поле введем, например, Фрукты. Сразу увидим, что наши ячейки, которые содержат слово Фрукты, окрасились в серый цвет — так Гугл таблицы по умолчанию окрашивают ячейки.
Но нам же надо окрасить в разные цвета разные ячейки, поэтому выберем ниже способ форматирования, а именно окрасим ячейки с фруктами, скажем, оранжевым цветом. Для этого выберем нужный цвет в форме с изображенным ведерком.
- Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!
- Теперь повторим эти действия с другими данными, нажав на кнопку Добавить правило справа, только теперь вводим в поле не Фрукты, а Ягоды и на последнем этапе Овощи, и наблюдаем вот такую картину:
- Теперь когда мы выберем нужные данные в выпадающем списке, ячейка эта будет окрашиваться в соответствующий цвет.
Вот и все, надеюсь статья была полезной. Если у вас есть еще какие-то вопросы, то пишите их в х, я с удовольствием на них отвечу!
Возможно вам будет интересно:
- Полный гайд по основным возможностям Гугл таблиц
- Формулы в Гугл таблицах
Источник: http://google-suite.info/google-tablicy-vypadayushhij-spisok/
Как в гугл таблицах сделать выпадающий список?
Главная » Прочее »
Вопрос знатокам: Как в таблицах Google сделать выпадающий список?
С уважением, Николай Чернявский
Лучшие ответы
Таблицы Google. Как сделать выпадающий список — s .youtube m/watch?v=ce3k6aw_wy0
скорее всего также как и в простом Excel
ВОт тебе урок на ютуб на эту тему. s youtu.be/S9smsC_v0DE
Видео-ответ
Это видео поможет разобраться
Ответы знатоков
есть вопросы.или формулы ставить на список на второй лист и его копироватьили городить на ВБА. макросы.ещё s support.office m/ru-ru/article/Создание-связи-между-двумя-таблицами-в-excel-fe1b6be7-1d85-4add-a629-8a3848820be3ну и ютюб
Удачи!
«Сделав один раз такой список, я его копирую несколько раз в нужных мне местах по листу» — это о чём?
Почему не дать имя этому списку и пользоваться им?
У автора этого сайта и канала есть видео на эту тему s stmsolution /gugl-tablits-1-urok-pervoe-znakomstvo-i-bazovoe-formatirovanie/
Канал s .youtube m/channel/UCY_USXQy0NBG_f4vuo9WDRQ?view_as=subscriber
Ну а вообще в проверке данных выберите значения из диапазона. Укажите диапазон и в том диапазоне пропишите необходимые данные все теперь копируйте куда хотите выпадающий список, оно везде будет одинаково и когда будете менять или дополнять диапазон данными, они будут меняться везде. Вот кстати видео как раз на ваш вопрос s .youtube m/watch?v=S9smsC_v0DE&t=14s
Никак. Формулой — никак! ТДАТА пересчитывается при каждом открытии листа или файла!Только макросом, который будет проверять, заполнена ячейка с датой или нет.
Полосатый жираф Алик верно говорит!
Красивый развод на бабло. Формула то верная была, ты был прав на 85%, проблема в том что тебе нужна была вторая формула в другой ячейке, и нужно было создать цикличность, Но цикличность работает только при включении итерации. Но право лучшего ответа получил барыга.
Сделать можно! Ставишь формулу например в ячейке А1 «=ТДАТА ()», а в ячейке А2 делаешь выпадающий список на ячейку А1. Чтоб сделать выпадающий список: наводишь мышкой на ячейку А2, правая кнопка, проверка данных, значения из диапазона и выбираешь ячейку А1.
function onEdit(e) {var sheet = e.source.getActiveSheet();var idCol = e.range.getColumn();var idRow = e.range.getRow();if ( idCol == 3 && sheet.getName() ==’Лист1′ ) {var Value = e.range.offset(0, -2).getValues(); // Смотрим что в ячейке с лева на 2
if ( Value == «» ) {
var vartoday = getDate();var varnow = getTime();
sheet.getRange(idRow, 1).setValue( vartoday );sheet.getRange(idRow, 2).setValue(varnow);}}
}
// Returns YYYYMMDD-formatted date.function getDate() {var today = new Date();today.setDate(today.getDate());//return Utilities.formatDate(today, ‘PST’, ‘yyyy.MM.dd’);return Utilities.formatDate(today, ‘GMT+03:00’, ‘yyyy.MM.dd’);
}
function getTime() {var today = new Date();today.setDate(today.getDate());//return Utilities.formatDate(today, ‘PST’, ‘yyyy.MM.dd’);return Utilities.formatDate(today, ‘GMT+03:00’, ‘HH:mm’);
}
Источник: https://dom-voprosov.ru/prochee/kak-v-gugl-tablitsah-sdelat-vypadayushhij-spisok
Как вы делаете динамические/зависимые выпадающие списки в Google Таблицах? — validation | fooobar.com
Изменить: ответ ниже может быть удовлетворительным, но он имеет некоторые недостатки:
- Существует заметная пауза для запуска script. Я нахожусь на задержке в 160 мс, и этого достаточно, чтобы раздражать.
- Он работает, создавая новый диапазон каждый раз, когда вы редактируете данную строку. Это приводит к недопустимому содержимому предыдущих записей часть времени
Я надеюсь, что другие могут немного почистить это.
Здесь другой способ сделать это, что экономит вам тонну именования диапазонов:
Три листа на листе: назовите их Main, List и DRange (для динамического диапазона).
На главном листе столбец 1 содержит метку времени. Эта метка времени изменяется наEdit.
В списке ваши категории и подкатегории упорядочены как простой список. Я использую это для инвентаря растений на моей деревенской ферме, поэтому мой список выглядит следующим образом:
Group | Genus | Bot_Name
Conifer | Abies | Abies balsamea
Conifer | Abies | Abies concolor
Conifer | Abies | Abies lasiocarpa var bifolia
Conifer | Pinus | Pinus ponderosa
Conifer | Pinus | Pinus sylvestris
Conifer | Pinus | Pinus banksiana
Conifer | Pinus | Pinus cembra
Conifer | Picea | Picea pungens
Conifer | Picea | Picea glauca
Deciduous | Acer | Acer ginnala
Deciduous | Acer | Acer negundo
Deciduous | Salix | Salix discolor
Deciduous | Salix | Salix fragilis
…
Где | указывает на разделение на столбцы.
Для удобства я также использовал заголовки в качестве имен для именованных диапазонов.
Уравнение A1 имеет формулу
=Max(Main!A2:A1000)
Это возвращает самую последнюю временную метку.
A2 — A4 имеют вариации на:
=vlookup($A$1,Inventory!$A$1:$E$1000,2,False)
- при этом 2 увеличивается для каждой ячейки справа.
- При запуске A2-A4 будут отобраны выбранные группы, роды и виды.
- Ниже каждого из них есть команда filter что-то вроде этого:
- = уникальный (фильтр (Bot_Name, REGEXMATCH (Bot_Name, С1)))
- Эти фильтры заполнят блок ниже соответствующими совпадениями с содержимым верхней ячейки.
- Фильтры могут быть изменены в соответствии с вашими потребностями и в формате вашего списка.
- Back to Main: проверка данных в Main выполняется с использованием диапазонов от DRange.
- script Я использую:
function onEdit(event) {
//SETTINGS
var dynamicSheet='DRange'; //sheet where the dynamic range lives
var tsheet = 'Main'; //the sheet you are monitoring for edits
var lcol = 2; //left-most column number you are monitoring; A=1, B=2 etc
var rcol = 5; //right-most column number you are monitoring
var tcol = 1; //column number in which you wish to populate the timestamp
//
var s = event.source.getActiveSheet();
var sname = s.getName();
if (sname == tsheet) {
var r = event.source.getActiveRange();
var scol = r.getColumn(); //scol is the column number of the edited cell
if (scol >= lcol && scol
Источник: http://qaru.site/questions/188273/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets
Загрузка...
