Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка
Рассмотрим особенности создания выпадающих списков на примере:
Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.
Рабочие файлы по ссылке ниже
Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!
Видимо так и придется.
Проблема только что в собес надо отдавать отчеты по субсидиям и льготам, а там дос и дбф формат, так что пока из экселя в эксель. Но наверное проще в Аксесс всё сделать, и в итоге и квитанции и отчеты из него формировать. Попробую сегодня, насколько это удобно получится.
Как в Экселе подключить набор данных из другой таблицы? — Хабр Q&A
Можно пойти другим путем. Кликнуть на «Сохранить» — выглядит как дискета в панели 1С. Она всегда находится в верхнем углу (доступно для ПО после обновления). Запустить окно сохранения легко с помощью «Ctrl+s».
Вариант 1. Добавление таблицы под таблицу
Этот способ применяется, когда у вас есть несколько таблиц с одинаковой шапкой, но они находятся в разных местах, например на нескольких листах или в разных файлах. В таком случае, чтобы построить сводную таблицу, мы можем просто одну таблицу добавить под другую. Обращаю ваше внимание, что таблицы объединяются как-бы по вертикали.
Как на основе таблиц с одинаковой шапкой построить единую базу? Добавив таблицы одну под другую
Раньше процедуру добавления можно было выполнить только с помощью копирования. Отсюда – много ручной работы при внесении новых данных (ну, или писали макросы). Однако с момента, как в Excel появился Power Query, у нас появилась возможность добавлять таблицы одну под другую несколькими щелчками мыши:
Подробности про объединение данных с помощью Добавления смотрите в видео.
Магия Excel: 10 самых полезных «фишек» для работы с таблицами — БизнесБизнес
- По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с одинаковой шапкой находятся в разных файлах или на разных листах.
- По горизонтали – слияние таблиц, похожее на ВПР. А здесь есть еще варианты — не только аналогичные ВПР, но и ВПР-наоборот, и ВПР-неВПР… Целых шесть видов объединения таблиц.
Тип соединения «Внешнее соединение справа» используется, если в первую таблицу нужно не только добавить новые столбцы, но и отфильтровать её. Вторая таблица используется как источник данных и управляет содержимым первой таблицы.
Как в Экселе подключить набор данных из другой таблицы?
У меня есть таблица в Экселе с ФИО и определенными цифрами в колонках к каждой строке.
Есть еще одна таблица, там набор текста, где надо вставлять в определенном месте ФИО и цифры из первой таблицы. И в итоге сформировать на печать для каждой строки свою табличку.
Подскажите как именно такую задачу сделать максимально просто и быстро?
В Word’е не всегда проще, —
для Слияния следите за форматами в таблице Excel (в частности, чтобы во всей колонке был одинаковый формат).
Квитанцию делаете в MS Word.
Таблица с данными в MS Excel.
Далее в Word через «Письма и рассылки» -> «Слияние» вставляете данные в квитанции и печатаете.
Спасибо. Пока я именно так и делаю. Но странно что нельзя обойтись только Экселем, потому и спрашиваю — может я чего недопонимаю. Неужели силами самого Экселя не получится сделать такого массового слияния, как с Вордом?
Это «слияние» не костыль?! Удачи с ним в многопользовательском доступе и работе с файлами по сети.
Можно всё, — было бы желание изучить.
Дѣаволъ: да, можно и на руби написать. но зачем, если есть уже таблица в экселе, данные из которой надо подставить в квитанции. Спрашиваю у тех, кто может знать может ли то же самое делать сам Эксель. «Слияние» с данной задачей справляется вполне успешно, без лишнего усложнения. Если бы было УПРОЩЕНИЕ (например силами только Экселя) — вот это был бы ответ на мой вопрос.
lukoie: ну так на один лист — бланк квитанции. На другом листе — данные по квартирам.
И с помощью ВПР с листа на лист вставляйте нужную информацию.
Xander017: и как это сделать для 100 квартир, чтобы в итоге квитанции на квартплату распечатывать каждый месяц?
lukoie: vba скрипт вам в помощь ( goo.gl/7vYTkO ). Но не понимаю, чем не устраивает слияние через Word? На выходе будете получать один файл с данными по всему дому. На печать это дело отправите тоже одной кнопкой.
John Smith: «Либо не мучатся и использовать MS Access»
Видимо так и придется.
Проблема только что в собес надо отдавать отчеты по субсидиям и льготам, а там дос и дбф формат, так что пока из экселя в эксель. Но наверное проще в Аксесс всё сделать, и в итоге и квитанции и отчеты из него формировать. Попробую сегодня, насколько это удобно получится.
если знать Access. Если есть время, гугл, и базовое понимание принципов устройства баз данных — не так трудно. Если нет — сложнее.
John Smith: Вы правы, это я про свои нужды писал — «квитанции» — это документ жильцам, а «отчеты» — в разные гос.органы.
Надо найти ту золотую серединку, чтобы эта работа выполнялась удобно и быстро. Потому что мне кажется что сейчас чего-то не хватает, или что-то лишнее. В любом случае, формирование документов сейчас выглядит шаманством.
Если во второй книге есть колонка, где значения совпадают со значениями из колонки в первой книге — функция ВПР должна помочь (таблица должна быть отсортирована по убыванию по нужным полям на случай, если будут найдены дубликаты искомых данных). Подробно.
Спасибо, это тоже полезно — я так могу добавить сумму субсидии из отдельной таблицы к таблице с оплатами.
Но вопрос остается — мне нужно сделать из таблицы со сводными суммами квитанции для оплаты. Чтобы автоматически сформировались для каждой строки(квартиры) из таблицы.
Можно и так. Я для пакетной обработки VBS-скрипты пишу через позднее связывание объектов. Разницы особой нет, если есть понимание решения задачи.
Хотя обычно такие вещи делают либо в специализированных системах, либо в БД, да тупо в MS Access, и никаких мейл мерджей не понадобится, свел данные в отчет — и отдыхаешь.
«Фриланс тут вообще не при чем, перестаньте.»
Да ну. Здесь задают вопросы типа:
— какой функцией воспользоваться, чтобы получить А из таблицы Б по идентификатору С
— почему не работает %вот_такая_описанная_в_вопросе_реализация%
— как лучше сделать — так или эдак?
А у вас явно рабочая задача, которую можно решить, имея ТЗ. Которое вы, кстати, поленились сделать — скриншот или перечень таблиц, столбцов, с примером данных, скрин квитанции ну никак не помешали бы. Ну понятно, тогда бы это окончательно стало бы похоже на задание, которое вы сами сделать не хотите\не можете, а просите сделать других, бесплатно.
Бесплатно просят советы, а не выполнить работу. Вот и разница.
John Smith: соответственно, Xander017 дал вам отличный ответ, как сделать квитанции на основе одной сведенной таблицы.
lukoie: Вы не поверите, но я могу себе представить 10000 дополнительных соглашений заполненных от руки по шаблонам. На вкус и цвет как говорится)) Так что сотней-другой квартир и собесом не удивили.
А в гифке ответ на вашу же фразу — «Ни ВПР ни аналог такого не сделает.» Не более и чуть-чуть сарказма.
Для ваших целей я бы использовал «Слияние» в Ворде. Уж телодвижений там точно минимум и проще некуда.
Но выбор за вами.
John Smith: «Либо у вас данные по квартирам уже сформированы, тогда прямая дорога вам в мейл мердж вордовский, как уже написали; что более вероятно — данные не сведены в одну таблицу,»
данные БЫЛИ сведены в одну таблицу. я разнес по трем(льготы, субсидии, и оплаты для всех с учетом льготников и субсидиантов), и есть еще четвертая с данными каждого жильца.
Я ищу наиболее простое, лаконичное и дешевое решение для данной задачи. Да, пока мердж таковым является.
» которую, опять же, можно отправить в вордовский мейл-мердж.»
Ну вот, приходится переключаться в Ворд. Я искал возможность обойтись внутренними инструментами Экселя.
Хотя обычно такие вещи делают либо в специализированных системах, либо в БД, да тупо в MS Access, и никаких мейл мерджей не понадобится, свел данные в отчет — и отдыхаешь.
Специализированные — либо досовские(на турбопаскале, думаю сами видели во всяких гос.учреждениях), либо платные, что для данной задачи не стоит того.
Конечно, можно развернуть sql-сервер, сделать бекенд с фронтэндом. Но опять же, избыточно, если для сотни-другой квартир.
«Да ну. Здесь задают вопросы типа.
Бесплатно просят советы, а не выполнить работу. Вот и разница.»
Здесь был вопрос именно о совете, и никто выполнить работу не просил — работа и так вполне себе выполняется.
Xander017: «А в гифке ответ на вашу же фразу — «Ни ВПР ни аналог такого не сделает.» Не более и чуть-чуть сарказма. «
эта моя фраза была сразу после предложения, чего именно не сделает. Так вот гифка показывает костыль, но не решение того, что описывалось там на предложение выше.
Так ведь можно дойти и до утверждения что на мой вопрос может быть решением блокнот.екзе! И гифку в подтверждение.
: )
Xander017: John Smith: Дѣаволъ: и кстати, упираемся в ограничение:
[url=http://s018.radikal.ru/i514/1611/73/4f73eb9ba053.p.
если таблица с субсидиями считает суммы, то итоговая сумма не воспринимается функцией ВПР
То есть вот такой запрос:
=IFERROR(VLOOKUP($J$2:$J$91,’03821016.xlsx’!$J$2:$AX$165,41,FALSE),0)
не отдаст результат, если сравнивать по запросу «4 Total». В скриншотной таблице он автоматически суммирует, а функция ВПР такую строку не видит, потому результат не подставляет.
lukoie: У вас ошибка в фотохостинге
Дѣаволъ: та нет там ошибки. адблок видимо ваш.
Вот в коммент прямо вставляю ссылку с фотохостинга:
s018.radikal.ru/i514/1611/73/4f73eb9ba053.png
lukoie: adblock, да мой любимый — не отключается ))
Из описания и обрезка картинки я ничего не понял. Вам John Smith написал, что вы поленились сделать ТЗ, поэтому и у вас ничего не получается, и мы не понимаем какие данные откуда получить.
ТЗ нужно писать просто и понятно, как задачи по математики для начальной школы.
К тому же вы не разобрались с функцией VLOOKUP, у вас в 1-м аргументе указан массив, а должно быть значение или ссылка на ячейку. В нижней части дано описание к аргументу и ссылка на полное описание по работе с функцией.
Объединение таблиц в Power Query |
Что происходит, когда вы выбираете «Внутреннее соединение»: таблицы будут объединены с помощью заданного общего столбца. При этом из первой таблицы исчезнут все не найденные в «общем» столбце второй таблицы строки. Из второй таблицы – исчезнут не найденные в первой. В математике эта операция называется «пересечением множеств».
Как сделать выгрузку клиентов или отчетов из 1С в Excel программным путем
Для продвинутых пользователей есть более сложные варианты произвести быстрое сохранение отдельных областей на компьютер. Сделать это можно 2 основными способами, о которых мы поговорим ниже.
Метод 1: вызываем сохранение таблицы
У каждого табличного документа имеется функция «записать», среди параметров которого есть тип файла. Следует выбрать заполнение табличного пространства, кликнуть на «записать».
Метод 2: задействуем механизм Excel.Aplication
Эту возможность проще всего реализовать в среде операционных систем Windows. Код важно отладить со стороны клиента, иначе придется долго дополнительно отстраивать серверную часть программного обеспечения.
Всю документацию по этому объекту можно найти на официальном сайте Microsoft. Для записи в нужной последовательности и формате пишут простейший код. Из всех инструментов ПО непосредственно к копированию информации относится строчка:
Как сопоставить данные в Excel?
- mxl — специально созданный для возможности сохранять сразу в формате таблицы, даже сбрасывать в базы на других компьютерах;
- xls — стандартный тип;
- txt — текстовый, вся информация в блокноте;
- pdf — подойдет, если планируется работать с разновидностями Adobe Reader;
- html — веб-формат, используется для размещения на странице в интернете.
Чтобы удалось оформить номенклатуру, следует сначала ее развернуть и отыскать правильный файл. Если конфигурация ПО управляемая, то вверху экрана ищем плашку «еще». Выпадет меню, где надо искать связанные со списками строки, чтобы войти в требующийся перечень.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Конечно, можно развернуть sql-сервер, сделать бекенд с фронтэндом. Но опять же, избыточно, если для сотни-другой квартир.
Как выгружать таблицы из 1С в Excel: выгрузка с 1C в Эксель, скопировать и перенести данные, документы, отчеты, файлы
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Также можно вставить информацию на лист другим способом. Направить курсор в клетку начала, а затем открыть вкладку «Главная», нажать на «Вставить» (его можно найти в части «Буфер обмена»). После всех этих действий содержимое окажется в табличном пространстве.
# 1 — Сопоставление данных с помощью функции ВПР
VLOOKUP используется не только для получения необходимой информации из таблицы данных; скорее, его также можно использовать как инструмент согласования. Когда дело доходит до согласования или сопоставления данных, формула ВПР возглавляет таблицу.
У нас есть две таблицы данных, первая — Данные 1 и второй Данные 2.
Теперь нам нужно согласовать, совпадают ли данные в двух таблицах или нет. Самый первый способ сопоставить данные — это функция SUM в Excel для двух таблиц, чтобы получить общие продажи.
Я применил функцию СУММ для столбца «Сумма продажи» таблицы. На самом начальном шаге мы получили разницу в значениях. Данные 1 таблица, показывающая общий объем продаж 2,16,214, и Данные 2 таблица, показывающая общий объем продаж 2,10 214.
Теперь нам нужно изучить это подробнее. Итак, применим функцию ВПР для каждой даты.
Выберите массив таблиц как Данные 1 спектр.
Нам нужны данные из второго столбца, а диапазон поиска — ЛОЖЬ, т. Е. Точное совпадение.
В следующей ячейке вычтите исходное значение из полученного значения.
Теперь скопируйте и вставьте формулу во все ячейки, чтобы получить значения дисперсии.
В Данные 1, у нас есть 12104 на дату 04 марта 2019 года, а в Данные 2, у нас есть 15104 на ту же дату, поэтому разница составляет 3000.
Аналогично на дату 18 марта 2019 г. Данные 1, у нас 19351, а в Данные 2, у нас 10351, значит разница 9000.
Удаление пробелов
Хоть в диалоговом окне таблицы расположены одна под другой (просто так удобнее технически), связь все равно горизонтальная. В диалоговом окне вы указываете столбец, по которому выполняется объединение.
