Как сделать выпадающий список в google таблицах

ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных — это может быть CRM, ERP система или просто таблица в Экселе

Создание управленческой таблицы

ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных — это может быть CRM, ERP система или просто таблица в Экселе.

Рассмотрим довольно простую, но правильную структуру организации учета.Предположим, что у нас несколько источников доходов: могут быть как отдельные виды деятельности, так и филиалы одной сети.

Инструментом ведения учета в рамках примера будут Гугл Таблицы.

Рассмотрим очень простую, но правильную структуру организации учета.
Предположим, что у нас несколько источников доходов — могут быть как отдельные виды деятельности, так и филиалы одной сети.

Инструментом ведения учета в рамках примера будут Гугл Таблицы.

1. Создаем таблицу, в которую будут записываться основные данные:

Как сделать выпадающий список в google таблицах

Таблица выглядит как реестр операций. В нее будут записываться все операции прихода и расхода на каждом из объектов деятельности.
Каждую операцию будем относить к наличному или безналичному расчету, поэтому для удобства анализа все операции группируем по статьям бюджета. Необходимо выделить основные направления движения денежных средств организации

2. Сделаем ввод данных максимально удобным и стандартизированным
(
это очень важно!) Дату можно вводить выбором из выпадающего календаря. Для этого выделяем столбец с датой, выбираем пункт меню «Данные» на панели управления и в выпадающем меню выбираем пункт «Проверка данных»

Как сделать выпадающий список в google таблицах

В поле выбора правил проверки выбираем значение «Дата» из выпадающего списка:

Как сделать выпадающий список в google таблицах

Нажимаем кнопку «Сохранить» . Теперь двойным мыши нажатием в столбце ввода даты появляется календарь:

Как сделать выпадающий список в google таблицах

Организуем выбор объекта из выпадающего списка. Для этого есть два варианта:

Вариант 1: Выделяем столбец «Объект», выбираем пункт меню «Данные», «Проверка данных».
В поле выбора правил выбираем «Значение из списка» и вписываем через запятую наименования объектов

Как сделать выпадающий список в google таблицах

Получаем выпадающий список с введенными значениями:

Как сделать выпадающий список в google таблицах

Вариант 2: Список объектов хранится в таблице на любом из листов. В примере это будет лист «Справочник»

Как сделать выпадающий список в google таблицах

Выделяем столбец «Объект» на листе «Данные». Переходим в пункт меню «Данные», выбираем «Проверка данных» и в поле правила выбираем «Значение из диапазона»

Как сделать выпадающий список в google таблицах

Далее указываем диапазон, содержащий нужные нам значения наименований объектов:

Как сделать выпадающий список в google таблицах

Далее создаем аналогичным образом выпадающий список для столбца «Тип операции» и «Тип платежа». В рамках примера предполагаются операции прихода и расхода, а платежи наличные и безналичные. По очереди выделяем столбцы, выбираем «Проверка данных» в пункте меню «Данные», выбираем правило «Значение из списка» и вводим нужные значения через запятую. Получаем выпадающие списки:

Как сделать выпадающий список в google таблицах

Статьи бюджета удобнее вывести в справочник. Там их будет удобнее редактировать и дополнять. Выделяем столбец «Статьи бюджета». Переходим в меню «Данные», «Проверка данных». Выбираем «Значения из диапазона» и указываем диапазон

Теперь осталось только привести в порядок таблицу, чтобы она имела презентабельный вид. Сперва нужно удалить проверку с заголовка таблицы. Выделяем всю строку заголовка, выбираем пункт меню «Данные», «Проверка данных» и нажимаем «Удалить проверку» в появившемся диалоговом окне

Далее задаем внешний вид:

Ввод данных и разграничение прав пользователей

Ввод данных и разграничение прав пользователей

Теперь в таблицу можно вводить данные. Гугл таблицы удобны тем, что ими может пользоваться одновременно почти неограниченное число пользователей, для которых администратор распределяет права доступа. В правом верхнем углу располагается пункт меню «Настройки доступа».

Пользователь вводит логин аккаунта 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, я назвал Данные, на нем я размещу исходные данные.

Как сделать выпадающий список в google таблицах

Теперь перейдем на наш главный лист Результат, где мы будем делать сам выпадающий список. Поставим курсор где нам необходимо, в моем случае разницы нет и я размещу выпадающий список в ячейке A3.

Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:
Как сделать выпадающий список в google таблицах

Как сделать выпадающий список в google таблицах

В котором мы видим следующие пункты:

  • Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
  • Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
    Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными.
  • Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
  • Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
    А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки».
  • Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»

Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:
Как сделать выпадающий список в google таблицах

Выпадающий список в Гугл таблицах с использованием условного форматирования

Сделать-то мы сделали выпадающий список, но теперь нам необходимо потренироваться как его использовать в работе.

Теперь добавим немного магии и воспользуемся условным форматированием для того, чтобы мы могли налету отличать данные которые выбраны в той или иной ячейке с выпадающим списком.

Допустим у нас есть некие данные, в нашем случае это Ягоды, Фрукты и Овощи. У вас это могут быть другие данные, но не это главное. Если у нас приличное количество выпадающих списков с различными данными, то выглядит все достаточно запутанно и вообще поди пойми где и что.

Чтобы как-то разбавить эту серую массу данных, нам потребуется инструмент условного форматирования. При помощи его мы в несколько кликов раскрасим наши товары в соответствующие цвета и нам будет значительно проще различать где какой вид продукта выбран.

Как сделать выпадающий список в google таблицах

Затем пройдем путь в меню: Формат -> Условное форматирование или кликнем правой кнопкой мыши и в открывшемся контекстном меню выберем Условное форматирование.

В открывшемся окне справа мы увидим что мы применять будем форматирование к диапазону A1:C20. Ниже в форме Форматирование ячеек выберем Текст содержит, еще ниже в поле введем, например, Фрукты. Сразу увидим, что наши ячейки, которые содержат слово Фрукты, окрасились в серый цвет — так Гугл таблицы по умолчанию окрашивают ячейки.

Но нам же надо окрасить в разные цвета разные ячейки, поэтому выберем ниже способ форматирования, а именно окрасим ячейки с фруктами, скажем, оранжевым цветом. Для этого выберем нужный цвет в форме с изображенным ведерком.

  • Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!
  • Теперь повторим эти действия с другими данными, нажав на кнопку Добавить правило справа, только теперь вводим в поле не Фрукты, а Ягоды и на последнем этапе Овощи, и наблюдаем вот такую картину:
    Как сделать выпадающий список в google таблицах
  • Теперь когда мы выберем нужные данные в выпадающем списке, ячейка эта будет окрашиваться в соответствующий цвет.

Вот и все, надеюсь статья была полезной. Если у вас есть еще какие-то вопросы, то пишите их в х, я с удовольствием на них отвечу!

Возможно вам будет интересно:

  • Полный гайд по основным возможностям Гугл таблиц
  • Формулы в Гугл таблицах

Источник: 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’);

}

Как сделать выпадающий список в google таблицах

Источник: 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

Ссылка на основную публикацию