Как сделать справочник в excel

Выпадающие списки в Excel ⁠ ⁠

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

Рад видеть в нашем тематическом сообществе интересные и полезные посты авторов @Petrov210217, @navferty, @zoodes, @FelixJanssen, @zoixnet, @kda2495, @avvacat, продолжайте в том же духе))

Итак, по просьбе @LordTachanka выкладываю пост, как создать выпадающие списки разными способами.

В пустой ячейке под списком нажимаем сочетание клавиш «Alt+стрелка вниз», либо в меню правой кнопки мыши «Выбрать из раскрывающегося списка» и выбираем ранее введённые значения, которые будут отсортированы в алфавитном порядке.

Способ работает даже если в списке имеются пустые ячейки. Также при добавлении новых значений они автоматически попадают в выпадающий список. Способ удобен если в столбце часто вводятся одинаковые названия.

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

Плюсом является также то, что можно задать уведомление об ошибке при вводе товара, отсутствующего в списке:

В результате будет выходить такое сообщение:

Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».

С помощью макроса (с возможностью добавления новых элементов)

Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.

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

Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная — Форматировать как таблицу, чтобы создать «умную» таблицу:

Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем списке. Для этого выделим в списке уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Легенды):

После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.

Проверить диапазон можно на вкладке Формулы — Диспетчер имен:

Таким образом, при дописывании новых имен к списку будет расширяться наша «умная» Таблица3, а за ней и наш именованный диапазон Легенды.

Теперь создаём выпадающий список в ячейке

Выделяем зелёные ячейки и жмём на вкладке «Данные» кнопку «Проверка данных».

Далее выбираем из выпадающего списка «Тип данных» позицию «Список» и вводим в строку» Источник» ссылку на созданный именованный диапазон =Легенды:

Чтобы Excel позволил нам в будущем ввести в список новые имена, снимем галочки на вкладках «Сообщение для ввода» и «Сообщение об ошибке» и жмём ОК. Выпадающий список готов!

Причем, если, например, вручную дописать новое имя в список в столбце А, то оно автоматически появится в выпадающем списке в любой из зелёных ячеек, поскольку имена берутся из динамического диапазона Легенды:

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

Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем «Просмотреть код». В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:

Private Sub Worksheet_Change(ByVal Target As Range)

Set p = Range(«Легенды»)

If Target.Cells.Count > 1 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(«D2:D11»)) Is Nothing Then

If WorksheetFunction.CountIf(p, Target) = 0 Then

r = MsgBox(«Добавить новое имя в справочник?», vbYesNo)

If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target

End If

End If

End Sub

Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:

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

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

С интересом просматриваю Ваши посты. Вот бы вы еще придумали как победить автоматическое форматирование в ячейках. Как же это бесит. Почему это нельзя отключить?

Выгружаешь из базы данных какую-нибудь сводку в формате CSV. А в ней, к примеру, колонка с тоннами. И значения типа «14.02» (тонн). «Умный» Excel заботливо заменит это на «14 фев.» Сука. И ничего сделать невозможно с этим. Приходится начала открывать файл в текстовом редакторе и автозаменой как-то править ячейки. Или сразу добавлять «т.» ко всем ячейкам в столбце еще на этапе SQL-запроса.

Читать ещё на Пикабу

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.

Важное примечание!

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

1 — Объединение ячеек.

Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 — «Правильная» таблица

Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

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

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 — Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

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

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать — это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

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

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

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

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

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

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

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

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

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

Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

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

Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

После стрипухи все возвращаются в кафе на завтрак, при этом по дороге подбирая уже сбежавшего под утро Ваню. В кафе решают платить по честному! Поэтому каждый платит за то, сто съел (Платит Ваня):

Создание в excel справочника

​Смотрите также​ программы работа в​ ценой). Вводим знак​ /строку (передвигаем вручную)​ в конце статьи​ справочник из другого​помогите мне пожалуйста. 111​ другом складе, чтобы​ ПРОВЕРКА ДАННЫХ –​ там, но и​ щелкните вкладку, чтобы​, чтобы перейти к​Перейдите к представлению Backstage,​Примечание:​Сочетание клавиш. Чтобы быстро​ ввода названий товаров​в поле Тип данных​Справочник состоит из двух​ Эксель с таблицами​ умножения (*). Выделяем​ – автоматически изменится​ вы уже будете​

​ документа? и как​hakervanya​

​ в будущем сделать​ ТИП ДАННЫХ –​ ее всегда может​ свернуть ленту.​ представлению Backstage.​ на вкладку​ Вкладка​ создать пустую книгу,​ используем Проверку данных:​ выберем Другой и​ таблиц: справочной таблицы,​

Таблица Товары

​ стала интересней и​ ячейку С2 (с​ размер всех выделенных​ понимать, что лучшего​ можно создать соподчиненный​: Ну почему всё​ заказ и довезти​ СПИСОК).​ использоваться для выполнения​Редактирование в классическом приложении​Нажмите​Открыть​Личные шаблоны​ вы также можете​

​выделите диапазон​ введем формулу, проверяющую​ в строках которой​ динамичней. Когда на​ количеством). Жмем ВВОД.​ столбцов и строк.​ инструмента для создания​ справочник?Возможною.​ за всех приходится​ недостающие продукты.​Нужно сделать так, чтобы​ различных манипуляций, сопоставления​Если в Excel Online​Создать​.​

​содержит созданные вами​ нажать клавиши CTRL+N.​C4:C14​ вводимое значение на​ содержатся подробные записи​

• ​ листе сформирована умная​​Когда мы подведем курсор​​Примечание. Чтобы вернуть прежний​​ таблиц, чем Excel​​Открыть другой документ,​
• ​ додумывать?​
• ​Выделим оба списка (без​ при выборе какой-то​ с другими списками​ нет нужной вам​. Следом за опцией​

​ шаблоны. Если вы​Советы:​;​​ уникальность:​​ о некоторых объектах​ таблица, становится доступным​ к ячейке с​​ размер, можно нажать​​ не придумаешь.​ взять данные, закрыть.​Как я понял​ шапок) с помощью​ фамилии, в остальных​ и т.п.​
​ функции, файл можно​Пустая книга​Компьютер​ не видите шаблон,​​ ​​вызовите Проверку данных;​=ПОИСКПОЗ(A2;$A:$A;0)=СТРОКА(A2)​ (сотрудниках, товарах, банковских​ инструмент «Работа с​ формулой, в правом​ кнопку «Отмена» или​Работа с таблицами в​Если вдруг это​ (если понял), у​

​ клавиши CTRL. Свободное​ ячейках автоматически проставлялись​Чтобы впоследствии справочник стал​ отредактировать в классическом​расположены несколько шаблонов.​, а затем​ который хотите использовать,​По умолчанию новые книга​

​в поле Тип данных​При создании новых записей​ реквизитах и пр.)​ таблицами» — «Конструктор».​

​ нижнем углу сформируется​ комбинацию горячих клавиш​ Excel для чайников​

• ​ всё происходит в​​ тебя есть справочник​​ место между списками​
• ​ соответствующие данные. Ячейки​ действительно полезным массивом,​
• ​ приложении Excel 2016. Чтобы​Выберите шаблон, чтобы посмотреть​
• ​Обзор​ убедитесь, что он​ содержит три листа,​
• ​ выберите Список;​

Таблица Накладная

​ о товарах (например,​ и таблицы, в​Здесь мы можем дать​ крестик. Он указываем​ CTRL+Z. Но она​ не терпит спешки.​ Экселе — то​ работа/оплата, где в​ (т.е. столбец B)​ с телефоном выделили​ нужно правильно его​ переключиться на классическое​ его.​. Кроме этого Вы​
​ находится в правильной​ но их количество​в качестве формулы введите​ в ячейке​

• ​ которую заносятся данные​​ имя таблице, изменить​​ на маркер автозаполнения.​
• ​ срабатывает тогда, когда​
• ​ Создать таблицу можно​ можно не открывая​
• ​ две колонки перечислены​ нам не нужно.​ зеленым, потому что​ создать.​

​ приложение, сделайте следующее:​Откроется предварительный просмотр и​ можете открыть файлы,​ папке. Пользовательские шаблоны​

​ можно изменить.​ ссылку на ранее​А10​ связанные с этими​ размер.​

​ Цепляем его левой​

​ делаешь сразу. Позже​

​ разными способами и​

​ другой документ брать​ виды работ и​ Затем на вкладке​ это самая важная​Как сделать справочник в​Щелкните​ дополнительная информация об​ хранящиеся в службе​ обычно хранятся в​

​Дополнительные сведения см. в​ созданный Именованный диапазон​), EXCEL автоматически скопирует​
​ объектами. Указав в​
​Доступны различные стили, возможность​

​ кнопкой мыши и​ – не поможет.​ для конкретных целей​ данные с помощью​ соответствующая им стоимость​

Создание книги

​ ГЛАВНАЯ выбираем УСЛОВНОЕ​ информация.​ Excel? Для создания​Изменить в Excel​ использовании шаблона.​ OneDrive (ранее SkyDrive).​

​ папке​ статье Изменение количества​

Создание книги

​ Список_товаров, т.е. =Список_Товаров.​

​ правило Проверки данных​​ ячейке лишь ключевое​​ преобразовать таблицу в​ ведем до конца​

​Чтобы вернуть строки в​

Создание книги на основе шаблона

​ каждый способ обладает​​ ВПР().​​ (работа может артикулом​​ ФОРМАТИРОВАНИЕ – ПРАВИЛА​​В ячейку J6 (там,​

​ телефонного справочника нужны,​

Откройте новую пустую книгу.

​Появится диалоговое окно​​Шаблоны​​ листов в новой​

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

​ обычный диапазон или​​ столбца. Формула скопируется​​ исходные границы, открываем​​ своими преимуществами. Поэтому​​А что такое​

​ обзываться, тогда еще​ ВЫДЕЛЕНИЯ ЯЧЕЕК –​ где ИМЯ) вводим​ минимум, два столбца:​

​Запустится приложение Excel, в​​Создать​

​Открытие документа​(стандартный путь в​ книге.​ можно будет вводить​

​А9​ сотрудника или код​ сводный отчет.​ во все ячейки.​

​ меню инструмента: «Главная»-«Формат»​ сначала визуально оценим​ «соподчиненный справочник»?при «проверка​

​ одна колоночка, всего​ ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ.​ команду =ИНДЕКС и​ имя человека или​ котором откроется файл.​

Создание книги на основе существующей

​, чтобы воспользоваться выбранным​​. Найдите и выберите​​ Windows Vista: C:\Пользователи\имя_пользователя\AppData\Local\Microsoft\Шаблоны;​

​При необходимости также можно​​ только из таблицы​​– в этом​

​ товара, можно вывести​​Возможности динамических электронных таблиц​​Обозначим границы нашей таблицы.​​ и выбираем «Автоподбор​​ ситуацию.​

​ данных» из другого​​ артикул, название, цена).​​Появится небольшое окно, где​ начинаем заполнять аргументы.​ организации и, собственно,​Выполните с ним нужные​ шаблоном.​

​ нужный файл, а​ стандартный путь в​​ добавить и удалить​​ Товары.​

Создание книги на основе шаблона

​ проявляется одно преимуществ​​ в смежных ячейках​​ MS Excel огромны.​

​ Выделяем диапазон с​​ высоты строки»​​Посмотрите внимательно на рабочий​

​ файла справочник не​Итак, ты хочешь​

​ можно выбрать, чтобы​Массив: выделяем всю таблицу​ номер телефона. Но​ действия.​​Откроется новая книга на​​ затем нажмите​​ Microsoft Windows XP:​​ листы.​Теперь заполним формулами столбцы​

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

​ лист табличного процессора:​ дает присоединить. соподчиненный​​ сделать так, чтобы​​ команда показывала повторяющиеся​ заказов вместе с​​ можно сразу сделать​​При сохранении изменений в​​ основе шаблона.​​Открыть​ C:\Documents and Settings\имя_пользователя\Application​

​Дополнительные сведения о добавлении​​ накладной Ед.изм., Цена​​ формате Excel 2007,​​ справочной таблицы. Другими​ навыков ввода данных​ «Главная»-«Границы» (на главной​ не актуален. Нажимаем​Это множество ячеек в​ справочник — это​ вставляя на лист​ или уникальные значения.​ шапкой. Делаем его​​ список более информативным,​​ приложении они также​Шаблон можно подобрать по​.​ Data\Microsoft\Шаблоны).​ и удалении листов​ и НДС. Для​

​ по сравнению с​​ словами, структура Справочник​ и автозаполнения:​ странице в меню​ «Формат» — «Ширина​​ столбцах и строках.​​ выбираешь одну позицию,​​ ‘счет’ артикул, у​​ Выберем УНИКАЛЬНЫЕ. Они​ абсолютным, фиксируя клавишей​ добавив дополнительные строки.​ сохраняются в OneDrive.​

Создание и открытие рабочих книг Excel

​ категории или воспользоваться​Если Вы открывали данный​Совет:​ см. в статье​ этого используем функцию​ обычными диапазонами ячеек.​ снижает количество ручного​Выделяем ячейку, щелкнув по​ «Шрифт»). И выбираем​ по умолчанию». Запоминаем​ По сути –​ и от нее​ тебя автоматически вытаскивались​ подсветятся цветом, который​ F4.​Шаблон готов. Шапка может​

​ Не нужно использовать​​ строкой поиска, чтобы​​ документ недавно, то​ Другие шаблоны книг можно​ Вставка и удаление​ ВПР():​Проверка данныхсрабатывает, если​ ввода и уменьшает​ ней левой кнопкой​ «Все границы».​ эту цифру. Выделяем​ таблица. Столбцы обозначены​

Создание новой пустой книги

1. ​ еще один справочник​​ данные из справочника​​ можно выбрать справа.​
2. ​Номер строки: сюда вводим​​ быть другой, какие-то​​ функцию «Сохранить как»​​ найти более редкий​​ удобнее будет найти​
3. ​ скачать с сайта​

Открытие существующей книги Excel

​ листов.​=ЕСЛИОШИБКА(ВПР(C4;Товары;2;ЛОЖЬ);»»)​ после ввода значения​ количество опечаток.​ мыши. Вводим текстовое​Теперь при печати границы​ любую ячейку в​ латинскими буквами. Строки​

1. ​ открывается​ — название и​​ У нас это​​ ПОИСКПОЗ и заполняем​
2. ​ столбцы должны быть​​ и повторно добавлять​​ шаблон.​​ его в списке​​ Microsoft Office.com. В​Откройте вкладку​или аналогичную ей формулу​ в ячейку нажата​
3. ​Создадим Справочник на примере​​ /числовое значение. Жмем​​ столбцов и строк​ столбце, границы которого​ – цифрами. Если​​возможно, я не​​ цена, в конце​

​ красный.​ уже аргументы этой​ добавлены, какие-то исключены.​ файл.​​Не все шаблоны созданы​​Последние книги​ разделе​

Закрепление книги в Excel

​Файл​=ИНДЕКС(Товары;ПОИСКПОЗ(C4;Список_Товаров;0);2)​ клавиша​ заполнения накладной.​ ВВОД. Если необходимо​ будут видны.​

1. ​ необходимо «вернуть». Снова​ вывести этот лист​​ совсем корректно выразился. ​​ выдавало сумма?​Скачать телефонный справочник шаблон​
2. ​ функции. Искомым значением​ Осталось только заполнить​Excel легко справляется с​ компанией Microsoft. Многие​, чем искать на​Доступные шаблоны​.​
3. ​Преимущество этой формулы перед​ENTER​В накладной будем выбирать​ изменить значение, снова​С помощью меню «Шрифт»​

​ «Формат» — «Ширина​ на печать, получим​ под справочником я​Вариантов реализации 3.​ в Excel​ будет ячейка с​ справочник информацией.​ числами. В Excel​​ созданы сторонними компаниями​​ компьютере.​​щелкните​​Щелкните​ функцией ВПР() состоит​. Если значение скопировано​ наименование товара, а​

Использование шаблонов в Excel

​ ставим курсор в​ можно форматировать данные​ столбца» — вводим​ чистую страницу. Без​ подразумеваю выпадающий список,​Первый — есть​Теперь можно скопировать все​ выпадающим списком –​Дополнительно можно провести еще​ можно упростить ввод​

Как создать новую книгу на основе шаблона

1. ​ и даже частными​​Если Вы часто работаете​​Шаблоны Office.com​Создать​
2. ​ в том, что​​ из Буфера обмена​​ цена, единица измерения​​ эту же ячейку​​ таблицы Excel, как​
3. ​ заданный программой показатель​ всяких границ.​
4. ​ где можно выбрать​ специальная функция в​ красные ячейки из​
5. ​ J6 (плюс F4).​​ одну манипуляцию: определить​​ данных с помощью​ пользователями, поэтому некоторые​
6. ​ с одним и​, выберите определенную категорию​

​.​ ключевой столбец Наименование​ или скопировано через​ и НДС, будут​ и вводим новые​

​ в программе Word.​ (как правило это​Сначала давайте научимся работать​ одно значение.​ экселе: ВПР(). При​ левого столбца и​ Просматриваемым массивом является​ формат ячеек. По​

​ функции автозаполнения. Затем​ шаблоны могут работать​
​ тем же документом,​
​ и дважды щелкните​

Работа в Excel Online

​ в таблице Товары​

​ Маркер заполнения, то Проверка​ подставляться в нужные​ данные.​​Поменяйте, к примеру, размер​ ​ 8,43 — количество​​ с ячейками, строками​​hakervanya​

​ правильно написанной функции​​ добавить их в​​ столбец с фамилиями​ умолчанию формат каждой​​ на основе этих​​ лучше, а некоторые​ то удобнее будет​

​ шаблон, который хотите​

​Шаблоны​ не обязан быть​​ данных не срабатывает,​​ ячейки автоматически из​При введении повторяющихся значений​ шрифта, сделайте шапку​ символов шрифта Calibri​

​, выложите лучше книги​

​ все будет заключаться​ правый и наоборот.​ (вместе с шапкой):​ ячейки значится как​ данных можно получить​

​ хуже других.​

​ закрепить его в​ скачать.​щелкните​ самым левым в​ а лишь помечает​ справочной таблицы Товары,​ Excel будет распознавать​

​ «жирным». Можно установить​ с размером в​​ с примерными данными​

​ в вбивании артикулов​

​ Получатся два равнозначных​ A1:A13 (плюс F4).​ ОБЩИЙ. Можно оставить​ рекомендации и одним​Урок подготовлен для Вас​ представлении Backstage.​Прежде чем начать работу​

​Из существующего документа​​ таблице, как в​​ ячейку маленьким зеленым​

​ содержащей перечень товаров​ их. Достаточно набрать​

​ текст по центру,​ 11 пунктов). ОК.​

​Чтобы выделить весь столбец,​ и поясните, что​ в счет и​ списка.​ Тип сопоставления: точное​ все как есть,​ щелчком создать диаграммы.​

Создание книги

​ командой сайта office-guru.ru​Перейдите к представлению Backstage,​ с Microsoft Excel​.​ случае использования ВПР().​ треугольником в левом​ с указанием, соответственно,​ на клавиатуре несколько​ назначить переносы и​Выделяем столбец /строку правее​ щелкаем по его​ нужно сделать.хорошо. в​ копировании формулы.​hakervanya​ совпадение, т.е. 0.​

​ но для столбца​

​Источник: http://www.gcflearnfree.org/office2013/excel2013/5/full​​ затем нажмите​​ Вы обязаны создать​

​В диалоговом окне​

​В столбцах Цена и​

​ верхнем углу ячейке.​ цены, единицы измерения,​ символов и нажать​

​ т.д.​ /ниже того места,​ названию (латинской букве)​ файле «объемы потребления. «​

​Второй — написать​: подскажите пожалуйста как​Номер столбца: снова нужен​

​ с номером телефона​ помощью гистограмм, цветовой​Автор/переводчик: Антон Андронов​

​Открыть​ новый документ, либо​Создание из имеющейся книги​ НДС введите соответственно​Через меню Данные/ Работа​

​ НДС.​ Enter.​Простейший способ создания таблиц​​ где нужно вставить​ левой кнопкой мыши.​

​ в первом столбце​​ обработчик события на​ создать справочник в​

Телефонный справочник в Excel готовый шаблон скачать

​ ПОИСКПОЗ. Искомое значение:​ можно задать специальный​ кодировки и значков​Автор: Антон Андронов​. Появятся последние открытые​ открыть уже существующий.​выберите диск, папку​ формулы:​ с данными/ Проверка​Эту таблицу создадим на​

​Чтобы применить в умной​ уже известен. Но​ новый диапазон. То​Для выделения строки –​

Шаблон телефонного справочника

​ есть перечень материалов.​ изменение листа, который​ ексел, в каком​ I7. Просматриваемый массив:​ формат. Для этого​ можно с легкостью​Создание книги​ книги.​ Вы можете создать​ или адрес в​

​=ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»»)​ данных/ Обвести неверные​ листе Товары с​ таблице формулу для​ в Excel есть​ есть столбец появится​

​ по названию строки​ в файле «справочники»​ при добавлении артикула​ он должен быть​ шапка массива, т.е.​ надо выделить ячейки​ выявить тенденции и​Выполните вход на странице​Наведите указатель мышки на​ пустую книгу или​ Интернете, где находится​=ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)​ данные можно получить​ помощью меню Вставка/​ всего столбца, достаточно​ более удобный вариант​

​ слева от выделенной​ (по цифре).​ есть этот же​ автоматически вытащит нужные​ файле? Может примерчик​

Как пользоваться справочником

​ из этого столбца,​ закономерности.​ office.com/signin и выберите​ книгу, которую необходимо​ воспользоваться уже готовым​ книга, которую требуется​Теперь в накладной при​ информацию о наличии​ Таблицы/ Таблица, т.е.​ ввести ее в​ (в плане последующего​ ячейки. А строка​Чтобы выделить несколько столбцов​

​ перечень, но необходимо,​ данные из справочника​ какой нибудь есть?​ Тип сопоставления: 0.​ правой кнопкой вызвать​Создание книги​ в​ закрепить. Рядом с​ шаблоном. Помимо этого​ открыть.​ выборе наименования товара​ данных, которые были​ в формате EXCEL​ одну первую ячейку​ форматирования, работы с​ – выше.​ или строк, щелкаем​ чтобы в первом​

​Третий — написать​помогите пожалуйста​Получили следующее. Формула универсальна,​ меню, выбрать ФОРМАТ​Откройте Excel.​средстве запуска приложений Office 365​ ней появится значок​ в рамках данного​Щелкните книгу, а затем​

​ автоматически будут подставляться​ введены с нарушением​ 2007(см. файл примера).​ этого столбца. Программа​

1. ​ данными).​Нажимаем правой кнопкой мыши​ левой кнопкой мыши​ файле можно было​ систему для ввода​
2. ​Hugo121​ ее можно протянуть​ ЯЧЕЕК.​Выберите элемент​плитку​ в виде канцелярской​ урока мы рассмотрим,​ нажмите кнопку​ его единица измерения,​ требований Проверки данных.​ По умолчанию новой​ скопирует в остальные​Сделаем «умную» (динамическую) таблицу:​
3. ​ – выбираем в​ по названию, держим​ выбрать из выпадающего​ видов работ по​: Воистину правильная постановка​ и на остальные​

​Среди предоставленных вариантов выбрать​Пустая книга​Excel​ кнопки. Нажмите на​ как закрепить файлы​Создать​ цена и НДС.​Для контроля уникальности также​ таблице EXCEL присвоит​

​ ячейки автоматически.​Переходим на вкладку «Вставка»​ выпадающем меню «Вставить»​ и протаскиваем.​ списка. в пределах​ данному клиенту с​ задачи — половина​ строки в заготовке.​ ДОПОЛНИТЕЛЬНЫЙ. Справа откроется​.​.​ значок.​ и папки в​.​Книга — это файл, который​

Как сопоставить два списка в Excel

​ можно использовать Условное​ стандартное имя Таблица1.​Для подсчета итогов выделяем​ — инструмент «Таблица»​ (или жмем комбинацию​Для выделения столбца с​ одной книги я​ формочкой, из которой​

​ решения.​ Теперь, при выборе​ мини-список, среди которых​Или нажмите клавиши CTRL+N.​Выберите​Книга окажется закрепленной. Для​ представлении Backstage для​Откройте вкладку​

​ содержит один или​ форматирование (см. статью​ Измените его на​ столбец со значениями​ (или нажмите комбинацию​ горячих клавиш CTRL+SHIFT+» Списки.» gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/sozdanie-v-excel-spravochnika_14_1.png»>

​ плюс пустая ячейка​ горячих клавиш CTRL+T).​

​Отмечаем «столбец» и жмем​ ставим курсор в​ помощью присвоения имени​ а она уже​ старче?​ вся остальная информация.​ НОМЕР ТЕЛЕФОНА.​

Создание справочника в Excel: нужен пример

​Чтобы ввести данные вручную,​​, откройте файл в​ закрепление, снова нажмите​ ним.​.​ упорядочения данных. Можно​Теперь, создадим Именованный диапазон​
​ через Диспетчер имен​

​ для будущего итога​​В открывшемся диалоговом окне​ ОК.​ любую ячейку нужного​
​ и проверки данных,​ атоматически подставит артикул,​
​Что ты понимаешь​ В том числе​​

​ выполните указанные ниже​​ списке​ на значок канцелярской​Файлы Microsoft Excel называются​Щелкните​ создать пустую книгу​ Список_Товаров, содержащий все​ (Формулы/ Определенные имена/​ и нажимаем кнопку​ указываем диапазон для​Совет. Для быстрой вставки​ столбца – нажимаем​ через файлы так​ цену, сумму в​ под словом ‘справочник’?​
​ и номер телефона.​Любой справочник нужен для​ действия.​Последние​ кнопки.​книги​Создать​ или воспользоваться шаблоном.​ наименования товаров:​ Диспетчер имен)​ «Сумма» (группа инструментов​
​ данных. Отмечаем, что​

​ столбца нужно выделить​​ Ctrl + пробел.​ уже не получается. ​ конец. Добавив формы​
​ Может, примерчик приведешь?​Получается, что команда ИНДЕКС​ того, чтобы по​Выделите пустую ячейку, например​или выберите один​Похожим образом в представлении​. Начиная новый проект​.​Более новые версии​выделите диапазон​К таблице Товары, как​
​ «Редактирование» на закладке​ таблица с подзаголовками.​ столбец в желаемом​ Для выделения строки​ предложения типа поместить​ для редактирования справочников,​hakervanya​ при задании критерия​ одному критерию можно​
​ A1, а затем​
​ из шаблонов.​ Backstage можно закреплять​ в Excel, необходимо​Выполните одно из указанных​ Office 2010 ​А2:А9​ к справочной таблице,​ «Главная» или нажмите​
​ Жмем ОК. Ничего​ месте и нажать​ – Shift +​ справочник в книгу​ получим прямо готовую​: мне нужно что​
​ из массива, выдает​ было легко узнать​ введите текст или​Присвоение имени файлу​ еще и папки​ создать новую книгу.​ ниже действий.​Откройте Excel.​;​ предъявляется одно жесткое​ комбинацию горячих клавиш​ страшного, если сразу​ CTRL+SHIFT+» Книга2″, «Книга3″​ представлении Backstage, перейдите​ создать новую пустую​ разделе​ CTRL+N. ​ Список_Товаров;​ ключевым. В нашем​ шапки, то мы​Примечание. Можно пойти по​ Нам придется расширять​ ячеек:​ для составления таблиц​ у меня такая​ в листик ексель,​ постоянно будем менять​ номер телефона этого​Чтобы заполнить ряд данных,​ и т. д.). Чтобы​ на вкладку​ книгу, воспользоваться существующим​

Как работать в Excel с таблицами для чайников: пошаговая инструкция

​Доступные шаблоны​Начните вводить текст.​убедитесь, что в поле​ случае, ключевым будет​ получим доступ к​ другому пути –​ границы, добавлять строки​Передвинуть вручную, зацепив границу​ и произведения расчетов.​ готовая есть простенькая​

​ всегда следующей, последней​ фамилии, чтобы узнавать​ человека. В Excel​ выполните указанные ниже​ присвоить ей более​Открыть​ шаблоном или открыть​щелкните​Откройте вкладку​ Диапазон введена формула​ поле, содержащее наименования​ дополнительным инструментам для​ сначала выделить диапазон​ /столбцы в процессе​ ячейки левой кнопкой​ Рабочая область –​ (выбирает из одного​

Как создать таблицу в Excel для чайников

​ записью. Как в​ номера телефонов людей,​ сделать это помогают​ действия.​ осмысленное название:​и выберите​ ранее сохраненный документ.​Образцы шаблонов​Файл​ =Товары[Наименование]​

​ Товара. Именно по​ работы с данными​

​ ячеек, а потом​ работы.​ мыши.​ это множество ячеек,​ ‘справочника’), по образу​ акссе сделать это​ мы дополнительно воспользовались​ функции ИНДЕКС и​Введите начало ряда в​Щелкните имя книги.​

​Компьютер​Выберите вкладку​, а затем дважды​

​и выберите пункт​

Как выделить столбец и строку

​нажмите ОК.​ этому полю будут​ таблицы.​ нажать кнопку «Таблица».​

​Заполняем вручную шапку –​Когда длинное слово записано​ которые можно заполнять​

​ и подобию разберешься,​ я знаю, а​ функцией ПОИСКПОЗ. Она​ ПОИСКПОЗ.​ двух ячейках, например:​

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

Как изменить границы ячеек

​ названия столбцов. Вносим​ в ячейку, щелкнуть​ данными. Впоследствии –​ как масштабировать на​

1. ​ с екселем вообще​ помогает искать позиции​Имеем небольшой справочник. В​
2. ​ «Янв» и «Фев»​Все, что вы делаете​ требуется закрепить, и​. Откроется представление Backstage.​Чтобы выбрать один недавно​.​
3. ​ предъявляется одно жесткое​ из справочной таблицы​ с огромными таблицами.​ в готовый каркас.​ данные – заполняем​

​ 2 раза по​ форматировать, использовать для​ нужное кол-во справочников. ​ первый раз сталкиваюсь.​ нужных нам строки​ действительности, в фирмах​ или «2014» и​ в Excel Online —​ нажмите на значок​

​Выберите​ использовавшихся шаблонов, щелкните​Откройте шаблон двойным щелчком.​ требование: все значения​ для подстановки в​ Чтобы посмотреть итоги,​ Если потребуется дополнительный​ строки. Сразу применяем​

​ границе столбца /​ построения графиков, диаграмм,​Могу намылить, если​ Даже не знаю​ и столбца.​

​ обычно более длинные​ «2015».​ от присвоения имени​ канцелярской кнопки.​Создать​Последние шаблоны​Щелкните и начните вводить​ в столбце (поле)​ накладную.​ нужно пролистать не​ столбец, ставим курсор​ на практике полученные​ строки. Программа автоматически​ сводных отчетов.​ нужно.​ с чего начать.​

Как вставить столбец или строку

​Работа со списками в​ списки, поэтому и​Выделите две ячейки, содержащие​ файлу до изменения​Шаблон – это предварительно​, затем нажмите​, а затем дважды​ текст.​

​ Товар должны содержаться​Для гарантированного обеспечения уникальности​ одну тысячу строк.​ в предназначенную для​ знания – расширяем​

​ расширит границы.​Работа в Экселе с​

​Да ты угодал,​У меня 5​ Excel подразумевает их​ искать в них​ ряд, а затем​

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

Пошаговое создание таблицы с формулами

1. ​ Удалить строки –​ названия ячейку. Вписываем​ границы столбцов, «подбираем»​Если нужно сохранить ширину​ таблицами для начинающих​ это то что​ разновидностей справочников, можно​ сопоставление. Т.е. сравнивание​
2. ​ информацию вручную сложно.​ перетащите маркер заполнения​Работа с данными​ для ускорения работы.​.​Чтобы использовать собственный шаблон,​Файл​ таблицы Товары. Другими​ Проверку данных (Данные/​ не вариант (данные​ наименование и нажимаем​ высоту для строк.​
3. ​ столбца, но увеличить​ пользователей может на​ мне нужно,​ ли разместить их​ данных, нахождение одинаковых​ Составим заготовку, в​через ячейки или​Присвоив имя файлу, можно​ Шаблоны содержат готовые​Откроется новая пустая книга.​ в разделе​
4. ​.​ словами, в накладную​ Работа с данными/​ впоследствии понадобятся). Но​ ВВОД. Диапазон автоматически​Чтобы заполнить графу «Стоимость»,​ высоту строки, воспользуемся​

​ первый взгляд показаться​пришли пожалуйста. ​ на 2-6 листе​

​ или уникальных позиций.​ которой будет значиться​ на них.​ добавить данные, отформатировать​

​ настройки, такие как​Помимо создания новой книги,​Мои шаблоны​Щелкните​ можно вводить только​ Проверка данных):​

Как создать таблицу в Excel: пошаговая инструкция

​ можно скрыть. Для​ расширится.​ ставим курсор в​ кнопкой «Перенос текста»​ сложной. Она существенно​[email protected]извеняюсь не тот​ книги, а потом​

​ Попробуем для примера​

1. ​ вся информация. А​Далее:​ их, ввести формулы,​ форматирование и дизайн,​
2. ​ существует необходимость открывать​, а затем на​Создать​ те товары, которые​выделим диапазон​ этой цели воспользуйтесь​Если необходимо увеличить количество​ первую ячейку. Пишем​ на панели инструментов.​

​ отличается от принципов​ адрес​ вставлять в первый​ сопоставить два простых​ появляться она будет​

​ Сохранение книги в​ создать диаграммы и​ с целью экономии​ ранее сохраненные документы.​ вкладке​.​ имеются в справочной​А2:А9​ числовыми фильтрами (картинка​

​ строк, зацепляем в​ «=». Таким образом,​Для изменения ширины столбцов​ построения таблиц в​[email protected]​

Как работать с таблицей в Excel

​ лист, по какой​ списка.​ по заданному критерию​ OneDrive​ т. д. Все​ времени и сил​ Для получения подробной​Личные шаблоны​В разделе​

​ таблице Товаров, иначе,​на листе​ выше). Убираете галочки​

​ нижнем правом углу​ мы сигнализируем программе​ и высоты строк​ Word. Но начнем​

​большое спасибо !Пожалуйста,​ нибудь кнопочке? Или​Имеется информация по двум​ – фамилия, поэтому​Excel удобно использовать для​

1. ​ возможности доступны на​ при создании нового​ информации обратитесь с​в диалоговом окне​Доступные шаблоны​ смысл создания Справочника​Товары​ напротив тех значений,​ за маркер автозаполнения​ Excel: здесь будет​
2. ​ сразу в определенном​ мы с малого:​ помогите и мне.​ это как то​ складам. Задача: проверить,​ сделаем этот пункт​
3. ​ создания телефонных справочников.​ вкладках вверху.​ проекта.​ уроку Сохранение и​Создать​дважды щелкните элемент​ пропадает.​;​
4. ​ которые должны быть​ и протягиваем вниз.​ формула. Выделяем ячейку​ диапазоне выделяем область,​ с создания и​ возможно ли хоть​ по другому делается,​ каких позиций нет​ в виде выпадающего​ Причем информация не​

​Если вкладки занимают слишком​Откройте вкладку​ автовосстановление книг в​дважды щелкните нужный​Пустая книга​Для формирования Выпадающего​вызовем Проверку данных;​

​ спрятаны.​С выходом новых версий​ В2 (с первой​ увеличиваем 1 столбец​ форматирования таблицы. И​ каким-нибудь способом привязать​ ума не приложу?​ на том и​ списка (ДАННЫЕ –​ просто надежно хранится​ много места, дважды​Файл​ Excel.​ шаблон.​.​

Телефонный справочник в Excel готовый шаблон скачать

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

Чтобы впоследствии справочник стал действительно полезным массивом, нужно правильно его создать.

Шаблон телефонного справочника

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

Шаблон готов. Шапка может быть другой, какие-то столбцы должны быть добавлены, какие-то исключены. Осталось только заполнить справочник информацией.

Дополнительно можно провести еще одну манипуляцию: определить формат ячеек. По умолчанию формат каждой ячейки значится как ОБЩИЙ. Можно оставить все как есть, но для столбца с номером телефона можно задать специальный формат. Для этого надо выделить ячейки из этого столбца, правой кнопкой вызвать меню, выбрать ФОРМАТ ЯЧЕЕК.

Среди предоставленных вариантов выбрать ДОПОЛНИТЕЛЬНЫЙ. Справа откроется мини-список, среди которых можно будет выбрать НОМЕР ТЕЛЕФОНА.

Как пользоваться справочником

Любой справочник нужен для того, чтобы по одному критерию можно было легко узнать остальные. Так, в телефонном справочнике мы можем ввести необходимую фамилию и узнать номер телефона этого человека. В Excel сделать это помогают функции ИНДЕКС и ПОИСКПОЗ.

Имеем небольшой справочник. В действительности, в фирмах обычно более длинные списки, поэтому и искать в них информацию вручную сложно. Составим заготовку, в которой будет значиться вся информация. А появляться она будет по заданному критерию – фамилия, поэтому сделаем этот пункт в виде выпадающего списка (ДАННЫЕ – ПРОВЕРКА ДАННЫХ – ТИП ДАННЫХ – СПИСОК).

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

В ячейку J6 (там, где ИМЯ) вводим команду =ИНДЕКС и начинаем заполнять аргументы.

1. Массив: выделяем всю таблицу заказов вместе с шапкой. Делаем его абсолютным, фиксируя клавишей F4.
2. Номер строки: сюда вводим ПОИСКПОЗ и заполняем уже аргументы этой функции. Искомым значением будет ячейка с выпадающим списком – J6 (плюс F4). Просматриваемым массивом является столбец с фамилиями (вместе с шапкой): A1:A13 (плюс F4). Тип сопоставления: точное совпадение, т.е. 0.
3. Номер столбца: снова нужен ПОИСКПОЗ. Искомое значение: I7. Просматриваемый массив: шапка массива, т.е. А1:Н1 (плюс F4). Тип сопоставления: 0.

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

Получается, что команда ИНДЕКС при задании критерия из массива, выдает нам номер его строки и столбца. Но т.к. критерий плавающий, и мы постоянно будем менять фамилии, чтобы узнавать номера телефонов людей, мы дополнительно воспользовались функцией ПОИСКПОЗ. Она помогает искать позиции нужных нам строки и столбца.

Как сопоставить два списка в Excel

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

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

Выделим оба списка (без шапок) с помощью клавиши CTRL. Свободное место между списками (т.е. столбец B) нам не нужно. Затем на вкладке ГЛАВНАЯ выбираем УСЛОВНОЕ ФОРМАТИРОВАНИЕ – ПРАВИЛА ВЫДЕЛЕНИЯ ЯЧЕЕК – ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ.

Появится небольшое окно, где можно выбрать, чтобы команда показывала повторяющиеся или уникальные значения. Выберем УНИКАЛЬНЫЕ. Они подсветятся цветом, который можно выбрать справа. У нас это красный.

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

Выпадающий список с добавлением новых элементов

Предположим, что у нас есть справочник с именами сотрудников и таблица, куда этих сотрудников нужно вносить:

Задача состоит из двух частей:

• Сделать выпадающий список, причем так, чтобы при дописывании новых людей к справочнику — они автоматически появлялись и в выпадающем списке.
• Реализовать возможность добавления новых людей в список и с другой стороны — при вводе нового имени в любую из жёлтых ячеек оно должно автоматически добавляться к справочнику (и в выпадающий список в будущем, само-собой).

Такая вот двухсторонняя связь справочника и выпадающего списка.

Шаг 1. Создаем умную таблицу

Сначала превратим справочник в «умную» таблицу, чтобы воспользоваться одним из главных её преимуществ — динамической автоподстройкой размеров при добавлении новых данных.

Для этого выделим весь справочник (ячейки A1:A7) и нажмём сочетание клавиш Ctrl + T или выберем Главная — Форматировать как таблицу (Home — Format as Table) . В следующем окне можно смело жать ОК:

Шаг 2. Создаем динамический именованный диапазон

Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем справочнике. Для этого выделим в справочнике уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Люди):

После ввода имени обязательно нужно нажать на клавишу Enter — слово Люди исчезнет из этого поля, но диапазон будет создан.

Хитрость тут в том, что поскольку мы выделяли столбец уже в «умной» таблице, то и именованный диапазон у нас получился завязанным на колонку [Справочник], а не на конкретные выделенные ячейки. Убедиться в этом можно, если выбрать на вкладке Формулы команду Диспетчер имен (Formulas — Name Manager) и посмотреть куда ссылается имя Люди:

Таким образом, при дописывании новых имен к справочнику будет расширяться наша «умная» Таблица1, а за ней и наш именованный диапазон Люди.

Шаг 3. Создаем выпадающий список в ячейке

Выделяем жёлтые ячейки и жмем на вкладке Данные (Data) кнопку Проверка данных (Data Validation)

Далее выбираем из выпадающего списка Тип данных (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон ( не забудьте перед именем диапазона поставить знак равенства! ):

Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) и нажмем ОК. Выпадающий список готов!

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

Шаг 4. Добавляем простой макрос

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

Щёлкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Просмотреть код (View Source) . Откроется модуль листа в редакторе Visual Basic, куда надо скопировать такой код:

Теперь при попытке ввести новое имя в любую из жёлтых ячеек Excel будет спрашивать:

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

Ссылки по теме

Да, мысль хорошая, подумаем

Очень полезный ресурс вы создали. Очень много интересного и нового.
Ну вот и у меня возник вопрос, причем достаточно срочный, спасайте: в данном примере (описаном выше) в качестве ячейки для выбора/ввода нового значения — указана одна ячейка. Как сделать так, чтобы при вводе данных в диапазон, к примеру, D2:D10, в любую из этих ячеек, происходила проверка и добавление в список. Прописать в стоорке If Target.Adress= и так далее 10 раз, можно через or, но мне нужен диапазон в 4000 строк . Помогите пожалуйста.

Это вам полноценную базу данных нужно. Копайте в сторону Access.

• имеется таблица (порядка 600 строк) с несколькими столбцами;
• к каждому столбцу привязаны выпадающие списки (именованные диаппазоны для списков на отдельном листе).

Николай, Range(«D2:D100000») не подходит. Указывая конкретные ячейки, мы ограничиваем диапазон срабатывания макроса. Поправил 6-ю строку на «If Target.Column = 4 Then» — работает .
Но теперь не работает 8-я строка, т.к. список находится на другом листе (в 12 строке Вашего примера изменения внес).

PS: про элементы управления и ActiveX я в курсе, спасибо за ссылку.

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

Подскажите пожааста что нужно изменить или добавить в макросе, что бы на одной странице работало две или больше ячейки , и добавляли новые данные так же в разные "умные таблицы" Напримере моего файла Сотрудники — это одна я чейка , а клиенты — другая.С одной ячейкой разобрался , но вот две не получается.
p.s. Я в программировании полный нубас , в написании макросов так же .Если
будет возможность помочь ответом, то напишите опираясь на мой файл плз.Спасибо!

Добрый день Николай, попробовал так как вы советуете:

Private Sub Worksheet_Change(ByVal Target As Range)
Set p = Range(«ГДО»)
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range(«H12:H100») Is Nothing Then
If WorksheetFunction.CountIf(p, Target) = 0 Then
r = MsgBox(«Добавить новый пункт справочника?», vbYesNo)
If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target
End If
End If
End Sub
Worksheets(«Лист1»).Range(«ГДО»).Cells(Worksheets(«Лист1»)Range(«ГДО»).Rows.Count + 1, 1) = Target

Список с данными на «Лист1», а выпадающий список на «Лист2». Но выдает ошибку: «Compile error: Only comments may appear after End Sub, End function, оr End Property.»
Если и выпадающий список и список с данными на одном листе(Лист2) то все работает.

Попробуйте использовать не событие Worksheet_Change, а Workbook_SheetChange — оно срабатывает при изменении любого листа книги. Тогда не надо будет руками вносить этот макрос в модуль каждого из 140 листов.

И проблема с несмежными диапазонам проверки решается легко — просто напишите несколько диапазонов через запятую внутри кавычек в операторе проверки:
If Not Intersect(Target, Range(«AI8:AI27,C2:C45,B25») Is Nothing Then

Глюк клавиатуры или руки дрожат?
К сожалению, Николай, причина в чем-то другом. Клавиатура не глючит, когда набираю текст на обычном листе, буквы повторяются именно при вводе в поле ИСТОЧНИК , и больше никогда (одно нажатие — дает сразу две одинаковые буквы). Проверила все галочки, сравнила с компом на работе. Всё везде одинаково, но на работе всё получается, а дома нет; точнее сказать получается и дома, просто вручную убираю лишние буквы. Возможно совпадение, возможно нет, но так стало, после того, как внезапно отключили в квартире электричество , потом естественно комп. перезагрузила, всё функционирует нормально, кроме ЭТОГО ммеессяяяцц! . Я думала есть какая-то хитрая галочка, которую я не вижу, а она мне всё портит.Ну всё равно, спасибо за ответ, продолжу поиск загадочной «галочки».

Спасибо за Ваш ресурс, но у меня одна проблема — на что нужно заменить «Добавить введенное имя » и » в выпадающий список?» в англ. версии Excel 2007, чтобы макрос работал. А то у меня вместо русского текста показывает вопросительные знаки. Помогите пожалуйста.

Спасибо Виталий, но у меня выводит желтым следующую строку и дальше не идет

If WorksheetFunction.CountIf(Worksheets(«Sheet2»).Range(«PrNos»), Target) = 0 Then

Мой средний ум буксует, может кто путное что подскажет.
Спасибо!

Интересно: включил пошаговое выполнение, редактор по очереди окрасил желтим все строки кода и после этого работает без проблем — новые данные в выподающий список вставляет.

Спасибо за сайт и за помощь ВСЕМ ЗНАТОКАМ и особенно АВТОРУ

Еще возник вопрос у меня национальная клавиатура и настройли редактора я поменял. В окне кода сообщение отоброжаетса праильно а вот в таблице специфические буквы(ā,ī,š) ошибочно.

Здравствуйте! Николай подскажите пожалуйста в чем ошибка (при попытке внести новое имя выскакивает макрос и выделяет цветом первую строку)? У меня офис 2010 и данные на одном листе, а таблица с ячейками куда вставлять данные на другом. И еще почему-то введя формулу =СМЕЩ(паспорта!$A$1;0;0;СЧЁТЗ(паспорта!$A$1:$A$100000);1) почему-то появляется список по порядку с другого запрашиваемого листа.

Заранее спасибо, ( на примере на одном листе пробовала все получалось правильно).

Private Sub Worksheet_Change(ByVal Target As Range)
Доброй ночи, такой вот макрос ошибку найти не могу

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(«f2:f100000»)) Is Nothing Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Sheets(«списки»).Range(«people»), Target) = 0 Then
lReply = MsgBox(«Äîáàâèòü ââåäåííîå èìÿ » & Target & » â âûïàäàþùèé ñïèñîê?», vbYesNo + vbQuestion)
If lReply = vbYes Then
Sheets(«списки»).Range(«people»).Cells(Sheets(«списки»).Range(«people»).Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

Не знаю что не так, новая запись в список не добавляется.Спасибо

Доброго времени суток! Сайт очень радует, и реально заражает Excel-ем)))
Стал активно юзать данный ресурс, и применять приемы в своей работе.
Относительно «Выпадающий список с добавлением новых элементов»:
-хотелось бы еще снабдить его очень полезным действием как интеллектуальное предложение данных из списка на вроде Т9 по первым набранным данным… Думаю выразился не очень но как то так))). Заранее СПС.

Добрый день!
Хочу поблагодарить за этот замечательный ресурс! Подобных в интернете еще не встречала. Очень помогает!
Николай, у меня вопрос. Модифицировала данный макрос для работы с повторяющимся выпадающим списком в нужном мне столбце. Данные при этом находятся на другом листе. Также добавила сортировку по алфавиту (спасибо другим участникам форума за советы). Получился следующий макрос:

Private Sub Workbook_SheetChange(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(«H6:H30»)) Is Nothing Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Worksheets(«hidden2»).Range(«stores»), Target) = 0 Then
lReply = MsgBox(«Would you like to add new store » & Target & » to your database?», vbYesNo + vbQuestion)
If lReply = vbYes Then
Worksheets(«hidden2»).Range(«stores»).Cells(Worksheets(«hidden2»).Range(«stores»).Rows.Count + 1, 1) = Target
End If
End If
End If

Sheets(«hidden2»).Range(«C1:C1000»).Sort Key1:=Sheets(«hidden2»).Range(«C1»), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal ‘this cod will help to range your stores
End Sub

Вопрос в следующем:
У меня в книге 12 листов с повторяющимися данными и, соответственно, одинаковым выпадающим списком. Попробовала применить выше описанную замену события Worksheet_Change на Workbook_SheetChange. Не работает. Что я делаю не так? Также можно ли в макросе просто прописать названия страниц или он будет работать для всей книги в целом (у меня есть лист итогов, где мне данный макрос на тех же самых ячейках не нужен совсем)?