Выбор картинки в excel
= Мир MS Excel/Статьи об Excel
Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
Приёмы работы с формулами [13] |
Настройки Excel [3] |
Инструменты Excel [4] |
Интеграция Excel с другими приложениями [4] |
Форматирование [1] |
Выпадающие списки [2] |
Примечания [1] |
Сводные таблицы [1] |
Гиперссылки [1] |
Excel и интернет [1] |
Excel для Windows и Excel для Mac OS [2] |
Шаг первый: Создаём список значений и их графических соответствий, например так:
Шаг второй: Создаём выпадающий список в ячейке.
Шаг третий: Создаём (Ctrl+F3) именованый диапазон, содержащий ссылки на изображения
Шаг четвёртый: Вставляем любое изображение туда, где мы хотим видеть результат
Шаг пятый: Выделяем это изображение, переходим в строку формул и пишем название именованого диапазона после знака =
0 Спам
Здравствуйте!
Не удается совладать с этим приемом.
А точнее с последним пунктом. Никак не укладывается в голове как можно выделить картинку и при этом ввести еще что-то в строку формул. У меня строка формул к примеру блокируется во время выделения картинки.
Заранее спасибо за ответ!
0 Спам
0 Спам
Бред - не работает! Что не активно поле для ввода формул после выделения картинка - это очевидно (Ни F2, ни что другое не помогает). И второй момент, автор не изволил даже написать откуда и каким образом появилась эта формула: =ИНДЕКС('1'!$A$1:$A$2;ПОИСКПОЗ(Лист1!$B$5;'1'!$B$1:$B$2;0)) Вывод - статья сырая, да еще и не рабочая!
0 Спам
Вит, я выложу видео, на нём будет видно подробнее
ЗЫ Файл скачайте и тогда убедитесь что всё работает
ЗЫЗЫ Формула появляется на третьем шаге, на картинке это видно
0 Спам
Рисунки которые в ячейках A1 и A2 по формату отличаются от рисунка в ячейке B5 - Почему? Не договорили в чем отличие - отсюда и проблемы (шаг 4). Вы попробуете вставить в строку формул у тех картинок которые в ячейках A1 и A2. :-) А люди копируют эти картинки из A1 в B5 - в которой соответственно не возможно вставить в строке формул название Logo. Упущен Шаг 4 - откуда эта картинка, у которой свойства совсем другие нежели те, которые в ячейках A1 и A2.
0 Спам
У всех картинок формат одинаковый - Jpeg, разный только размер
люди копируют эти картинки из A1 в B5 - в которой соответственно не возможно вставить в строке формул название Logo
Да что мешает-то?
Ткнули мышкой в картинку - она выделилась, потом ткнули мышкой в строку формул - и пишите в ней необходимое
Вит, Вы бы потрудились сначала скачать файл-пример и повторить пошагово по статье. Ничего сложного в этом приёме нет, он доступен даже новичку
ЗЫ С одним согласен: Формулу именованого диапазона надо было не только на картинке показать, но и в тексте. Исправлю. А так же довложу видео
0 Спам
Файл скачан и проверен, но те картинки которые в ячейках A1 и A2 - не получается (так что Ваше: "Ткнули мышкой в картинку - она выделилась, потом ткнули мышкой в строку формул", с картинками в яч. A1 и A2 - не возможно вбить в строку формул данные, при активных картинках) - с картинкой которая в ячейке B5 - НЕТ проблем, в том и дело в чем разница с теми двумя картинками. Вы так и не поняли проблему.
P.S. Свойства разные у картинок в ячейках A1,A2 с картинкой в ячейке B5 = Почему?!
0 Спам
И последнее. Вит, а почему Вы вообще прицепились к А1 и А2?
Ведь мы начинаем с того что создаём список значений и их графических соответствий в диапазоне А1:В2 (это отчетливо видно на картинках). А на четвёртом шаге вставляем любое изображение туда, где мы хотим видеть результат. Но мы ведь не можем хотеть видеть результат там где он уже есть, там откуда мы его и взяли. Какая логика-то в этом?
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
0 Спам
проблема в том что при выделенной картинке в строку формул нельзя внести НИЧЕГО,на ней не отображается курсор
0 Спам
0 Спам
Так же как и я добавил - выложить видео на какой-нибудь ресурс (я выкладывал на YouTube), а сюда ссылку
0 Спам
0 Спам
К сожалению на видео не видно курсора, но если принять за истину что у Вас заблокирована строка формул, то пишите мне в личку. Свяжемся по TeamViewer и устраним этот баг(?)
0 Спам
курсора в строке формул не отображается потому что он исчезает при попадании в строку формул,у меня уже было такое,и была какая-то хитрость,которую я не помню,и как написать вам в личку,почта скрыта,TeamViewer у меня установлен
0 Спам
проблема осталась и пока не решили,раньше я находил на каком-то форуме очень простое решение,наверное тот форум закрыли
0 Спам
Андрей, проблема именно у некоторых пользователей возникает, пока непонятно почему
Я проверил этот приём на трёх версиях Excel (2003, 2010, 2013) - у меня работает
= Мир MS Excel/Статьи об Excel
Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
Приёмы работы с формулами [13] |
Настройки Excel [3] |
Инструменты Excel [4] |
Интеграция Excel с другими приложениями [4] |
Форматирование [1] |
Выпадающие списки [2] |
Примечания [1] |
Сводные таблицы [1] |
Гиперссылки [1] |
Excel и интернет [1] |
Excel для Windows и Excel для Mac OS [2] |
Шаг первый: Создаём список значений и их графических соответствий, например так:
Шаг второй: Создаём выпадающий список в ячейке.
Шаг третий: Создаём (Ctrl+F3) именованый диапазон, содержащий ссылки на изображения
Шаг четвёртый: Вставляем любое изображение туда, где мы хотим видеть результат
Шаг пятый: Выделяем это изображение, переходим в строку формул и пишем название именованого диапазона после знака =
0 Спам
вообще это не проблема,я уже делал все это только давно,тоже не получалось,но на каком-то форуме нашел совет,что-то надо зажимать при копировании,и все получается,это было пол-года назад,а когда понадобилось сделать наглядную программку сейчас ,я очень пожалел что не оставил пометку,как я это делал,и форума в закладках не найду
0 Спам
Не надо ничего нажимать, должно работать и так. Дело в каких-то локальных настройках именно Вашей версии Excel, а возможно и операционки. Причину трудно установить, но буду пробовать
Кстати, Вы сами попробуйте в другой версии сделать аналогичные действия
0 Спам
Ребята, все работает!!
Вопрос, нельзя ли сделать второй, такой же выпадающий список ячейкой ниже, чтобы те же логотипы отображались в той же самой ячейке, в общем то же управление но из разных ячеек?
0 Спам
0 Спам
Вы знаете, я пытался, не получается, оказывается выделеному изображению должна соответствовать только одна формула- =logo, попытка присвоить два именованого диапазона, заканчивается ошибкой.
0 Спам
0 Спам
Решение курсора. Копировать ячейку с рисунком, просто копировать. А вставлять связь с рисунком. Эксель 2007, иначе никак.
0 Спам
У кого при выделении рисунка неактивна строка формул зайдите в скаченный файл "пример" скопируйте эту картинку из него себе в документ, все заработает!
0 Спам
Господа, у самого на прямую не получалось сделать так как описано выше, ибо пользуюсь 2007, а описание соответствует 2010
у меня сразу все получилось в 2010 и работает в более ранних версиях, автору респект
но в 2007 немного не так,а именно Шаг 4 - нужно вставлять не любую картинку, а именно Вставка - Обьект - bitmap image
после (выделив нововставленную картинку) в строке формулы прописать формулу
и вуаля
0 Спам
Проблема решается так: вставка-объект (в 2007 справа в конце)- выбираем точечный рисунок- затем объекту прописываем в данном случае =logo и всё начинает работать.
0 Спам
Спасибо огромное, разобрался, подладил под свою задачу, всё заработало именно так, как я хотел.
Только есть одно но, на мониторе и даже на странице просмотра для печати вид нормальный (Рис.1) , a на распечатанном листе изображения сдвинуты. Также когда, конвертирую в формат PDF, изображения тоже смещены в таблице (Рис.2)
Подскажите, пожалуйста, как избавиться от этого эффекта
0 Спам
Всё получилось, спасибо. Но вот хотелось бы когда в списке пробел и изображения бы не было, а то остаётся последнее.
0 Спам
Можно ли получить ссылку именно на рисунок, а не на снимок ячейки в которой был рисунок? Так как при разном размере рисунков, евозможно подобрать размер ячеек. чтобы не было лишних фрагментов.
Как создать выпадающий список с изображениями в Excel?
В Excel мы можем быстро и легко создать раскрывающийся список со значениями ячеек, но пробовали ли вы когда-нибудь создать раскрывающийся список с изображениями, то есть, когда вы щелкаете одно значение из раскрывающегося списка, его относительное изображение будет отображаться сразу, как показано ниже. В этой статье я расскажу о том, как вставить выпадающий список с изображениями в Excel.
Создайте раскрывающийся список с изображением с помощью функции именованного диапазона
Обычно вы можете создать именованный диапазон, а затем использовать связанный рисунок для решения этой задачи в Excel.
1. Во-первых, вы должны создать два столбца, которые содержат значения ячеек и их относительные изображения, см. Снимок экрана:
Примечание: Чтобы быстро и легко вставить несколько изображений на основе значений ячеек, нажмите, чтобы посмотреть здесь.
2. Затем вам нужно создать раскрывающийся список со значениями ячеек, щелкните одну ячейку, в которой вы хотите вывести значение раскрывающегося списка, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:
3. В проверка достоверности данных диалоговое окно под Настройки , выберите Список из Разрешить раскрывающийся список, а затем выберите значения ячеек, которые вы хотите создать в раскрывающемся списке под Источник раздел , см. снимок экрана:
4. Затем нажмите OK и создан раскрывающийся список со значениями ячеек, выберите один элемент из раскрывающегося списка, см. снимок экрана:
5, Затем нажмите Формулы > Определить имя, см. снимок экрана:
6. В Новое имя диалоговом окне выполните следующие действия:
- Введите имя под названием мой образ в Имя и фамилия текстовое окно;
- Затем скопируйте и вставьте эту формулу: =INDEX(Sheet1!$A$2:$B$6,MATCH(Sheet1!$E$2,Sheet1!$A$2:$A$6,0),2) в текстовое поле Ссылается на.
Внимание: В приведенной выше формуле:
- Sheet1! $ A $ 2: $ B $ 6: рабочий лист и диапазон, содержащий значения и изображения, которые вы хотите использовать;
- Sheet1! $ E $ 2: это ячейка на листе, в которой вы создали раскрывающийся список;
- Sheet1! $ A $ 2: $ A $ 6: это список ячеек, на основе которых вы создали выпадающий список;
- Номер переменной 2 - номер столбца, содержащего изображения. Если ваши изображения находятся в столбце C, вы должны ввести 3.
7, Затем нажмите OK чтобы закрыть диалог. Затем вам нужно скопировать и вставить соответствующее изображение на основе значения ячейки в E2 из исходного диапазона в ячейку F2, см. Снимок экрана:
8. Затем вы должны выбрать изображение в ячейке F2, а затем ввести эту формулу = myimage (мой образ - это имя диапазона, которое вы назвали на шаге 5) в строке формул, см. снимок экрана:
9. После ввода формулы нажмите Enter клавиша, и теперь, когда вы выбираете один элемент из выпадающего списка, его относительное изображение будет отображаться немедленно. Смотрите скриншот:
Создавайте несколько раскрывающихся списков с изображениями с помощью мощной функции
Если у вас есть Kutools for Excel, С его Раскрывающийся список изображений функцию, вы можете создать несколько раскрывающихся списков с их относительными изображениями всего за несколько щелчков мышью.
Примечание:Чтобы применить это Раскрывающийся список изображений, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Во-первых, вы должны создать два столбца, которые будут содержать значения и относительные изображения на вашем листе.
2. А затем нажмите Кутулс > Раскрывающийся список > Раскрывающийся список изображений, см. снимок экрана:
3. В Раскрывающийся список изображений В диалоговом окне, если вы создали столбцы значений ячеек и изображений, проигнорируйте шаг 1, затем выберите исходные данные и диапазон изображений и диапазон вывода, см. снимок экрана:
4. А затем нажмите Ok При нажатии кнопки раскрывающиеся списки с изображениями были созданы в выбранных ячейках, и при выборе элемента из раскрывающейся ячейки соответствующее изображение будет отображаться, как показано ниже в демонстрации:
Вставка нескольких изображений на основе значений ячеек с полезной функцией
Чтобы быстро вставить относительные изображения на основе значений ячеек, не вставляя их по одному, я могу порекомендовать мощный инструмент -Kutools for Excel, С его Сопоставить Импорт изображений Утилита, вы можете закончить эту работу удобно.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс Плюс > Импорт Экспорт > Сопоставить Импорт изображений, см. снимок экрана:
2. В Сопоставить Импорт изображений диалоговом окне выполните следующие действия:
- Выберите значения ячеек, в которые вы хотите вставить изображения на основе под Диапазон соответствия текстовое поле ;
- Затем нажмите Добавить кнопка для выбора и вставки соответствующих картинок в список;
- Затем нажмите Размер импорта кнопку, чтобы указать размер изображений в раскрывающемся списке Размер импортированного изображения диалоговое окно;
- Наконец, нажмите Импортировать кнопку.
3. Затем в появившемся диалоговом окне выберите ячейку, в которую вы хотите вставить изображения, см. Снимок экрана:
4. Нажмите OK, изображения были вставлены в определенные ячейки, которые соответствуют значениям ячеек.
Как легко выделить все объекты (картинки и диаграммы) в Excel?
Как выбрать все объекты, например все изображения и все диаграммы? В этой статье представлены сложные способы выбора всех объектов, выделения всех изображений и простого выбора всех диаграмм на активном листе в Excel.
- Выбрать все объекты на активном листе
- Выбрать все изображения на активном листе
- Выбрать все диаграммы на активном листе
- Удалить все объекты / изображения / диаграммы / формы на активных / выбранных / всех листах
Выбрать все объекты на активном листе
Вы можете применить Перейти к команда, чтобы легко выбрать все объекты. Вы можете сделать это, выполнив следующие действия:
Шаг 1: нажмите F5 , чтобы открыть Перейти к диалоговое окно.
Шаг 2: нажмите Особый в нижней части, чтобы открыть Перейти к Специальное диалоговое окно.
Шаг 3: в Перейти к специальному диалоговое окно, проверьте Объекты опцию.
Шаг 4: нажмите OK. Затем он выбирает все типы объектов на активном листе, включая все изображения, все диаграммы, все формы и т. Д.
Легко вставляйте несколько картинок / изображений в ячейки в Excel
Обычно изображения вставляются над ячейками в Excel. Но Kutools для Excel Импорт изображений Утилита может помочь пользователям Excel пакетно вставлять каждое изображение / изображение в одну ячейку, как показано ниже. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Выбрать все изображения на активном листе
Кажется, нелегкий способ выбрать все изображения, кроме ручного выбора каждого из них. Фактически, макрос VB может помочь вам быстро выбрать все изображения на активном листе.
Шаг 1: Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
Шаг 2: нажмите Вставить > Модулии вставьте следующий макрос в окно модуля.
VBA: выбрать все изображения на активном листе
Шаг 3: нажмите F5 ключ для запуска этого макроса. Затем он сразу выбирает все изображения на активном листе.
Выбрать все диаграммы на активном листе
Макрос VB также может помочь вам выбрать все диаграммы на активном листе.
Шаг 1: Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
Шаг 2: нажмите Вставить > Модулии вставьте следующий макрос в окно модуля.
VBA: выбрать все диаграммы на активном листе
Шаг 3: нажмите F5 ключ для запуска этого макроса. Этот макрос будет выбирать все виды диаграмм на активном листе в мгновение ока.
Быстро удалить все объекты / изображения / диаграммы / формы на активных / выбранных / всех листах
Иногда вам может потребоваться удалить все изображения, диаграммы или фигуры из текущего листа, текущей книги или указанных листов. Вы можете применить утилиту Kutools for Excel для удаления иллюстраций и объектов, чтобы легко ее заархивировать.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Нажмите Кутулс > Удалить > Удалить иллюстрации и объекты.
2. В открывшемся диалоговом окне вам необходимо:
(1) В Удалить В разделе укажите типы объектов, которые вы хотите удалить.
В нашем случае мы хотим удалить диаграммы и изображения, поэтому мы проверяем опцию Charts и опцию Pictures.
(2) В Заглянуть раздел, укажите область удаления.
В нашем случае мы хотим удалить диаграммы и изображения с нескольких указанных листов, поэтому мы проверяем Избранные листы вариант, а затем отметьте указанный лист в правом поле. Смотрите левый снимок экрана:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
3. Нажмите Ok кнопку.
Затем все диаграммы и изображения удаляются с указанных рабочих листов.
Демо: легко удалять все объекты (изображения и диаграммы) в Excel
Выпадающий список в Excel (или раскрывающийся список) — это список в ячейке Excel, из которого можно выбрать одно из нескольких заранее заданных значений. Это удобно для быстрого и правильного заполнения данных: не вбивать руками, а просто выбрать. Тут же можно настроить контроль, чтобы пользователи не могли вносить значения, не предусмотренные в списке.
Выпадающий список работает так: выбираете ячейку и справа от нее появляется кнопка со стрелкой вниз (правда, стрелка больше похожа на треугольник).
После нажатия на кнопку списка появится перечень доступных значений, одно из которых можно выбрать.
Значений в списке может быть много, но в обзор помещается до 8 строк. Если значений в списке больше восьми, справа от них появится полоса прокрутки.
Чтобы создать выпадающий список, выделите ячейку, где он должен появиться (или группу ячеек) и перейдите на вкладку Данные -> Проверка данных.
В появившемся окне укажите тип данных – Список, поставьте галочку рядом со строкой «Список допустимых значений».
Источником данных может быть:
- Текст – пишется через точку с запятой «;» и без знака равно «=», например
Материалы;Заработная плата;Амортизация
- Ссылки на ячейки:
=$A$1:$A$7
- Именованный диапазон:
=ИмяДиапазона
Связанные выпадающие списки
Связанные выпадающие списки – это списки, в которых выпадающие значения появляются не «просто так», а в зависимости от уже заполненных данных. Так, для выбранной группы появится только список входящих в неё наименований.
Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.
Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.
Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.
Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов
Исходные данные: таблица с названиями групп в заголовках столбцов.
В этом способе используется всего одна простая формула — ДВССЫЛ (правда, непривычная обычным пользователям) и форматированная smart-таблица Excel (иногда их еще называют «умные» таблицы). Зато вы получите взаимозависимые списки и будете пользоваться ими по принципу «сделал и забыл». Не нужно будет переживать о том, что «слетит» диапазон и переделывать всю архитектуру данных. Просто один раз сделаете и будете пользоваться.
Справка:
Форматированная («умная») таблица Excel
Форматированная таблица – это таблица, у которой есть свое имя, свойства и структура. Такая таблица представляет из себя именованный «саморасширяющийся» диапазон. При добавлении в нее новых данных границы таблицы автоматически «захватят» новое значение.
У форматированной таблицы множество преимуществ по сравнению с обычной. Поэтому на курсах и семинарах я советую использовать такие таблицы везде, где есть такая возможность.
Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.
Формула ДВССЫЛ
Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.
Например, записываем в ячейке B1 адрес ячейки А1. Формула ДВССЫЛ( B1 ) «увидит», какой адрес записан в ячейке B1, а результатом вычисления формулы будет текст, записанный в ячейке А1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ( « А1 » ).
С помощью ДВССЫЛ можно обратиться к ячейке по адресу с помощью других формул, например СЦЕПИТЬ, & или ЕСЛИ и т.д. Так, формула на рисунке ДВССЫЛ( B1 & C1 ) обращается к тексту в ячейке А1. После нажатия Enter в ячейке, где вводилась формула ДВССЫЛ( B1 & C1 ) появится значение из ячейки A1, в нашем случае это «текст».
Пошаговая инструкция по созданию связанных выпадающих списков
Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
- Присвойте созданной таблице имя: выделите любую ячейку таблицы, перейдите на вкладку Конструктор, введите имя — «Источник».
Столбец таблицы: = Источник[Материалы]
Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.
Шаг 2. Создайте выпадающий список с группами.
Готово! В столбце «группа» появился выпадающий список.
Шаг 3. Создайте выпадающий список со статьями.
- Выделите столбец «статья» в таблице.
- Перейдите в меню Данные -> Проверка данных.
- В появившемся окне выберите тип данных — Список, а в строке Источник введите формулу: =ДВССЫЛ(«Источник[«&$G3&»]»)
В формуле $G3 – это первая ячейка из столбца «группа». Ссылка на столбец «зафиксирована» с помощью знака доллара $, а строка может изменяться.
Готово! В столбце «статья» появляется только список статей, входящих в группу.
Теперь в форматированную smart-таблицу можно добавлять новые группы и статьи. Добавим, например, новый столбец «Прочее», и такая группа сразу же появится в выпадающем списке.
Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами - во втором
Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.
На самом деле в сети можно найти несколько вариантов реализации этого способа. Но у них у всех есть один недостаток: такой список нужно «администрировать». Потому что таблица должна быть всегда отсортирована по названиям групп – нельзя, чтобы группы располагались произвольно. Если группы будут идти «как попало», то формула, с помощью которой это всё сделано (СМЕЩ) не сработает, и список будет создаваться с ошибкой. Т.е. пользователю нужно все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще потребуется записывать где-то отдельно сами названия групп и это тоже нужно будет делать «вручную».
Мы придумали, как обойти эту проблему: с помощью сводных таблиц , которые будут за нас упорядочивать данные. Благодаря этому постоянное «администрирование» будет заменено на простую процедуру «обновить».
Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.
Справка:
Формула СМЕЩ
СМЕЩ выдает ссылку на диапазон ячеек, находящийся в указанном количестве ячеек от исходной. Ссылка определяется с учетом заданного в формуле размера диапазона – числа строк и столбцов. Другими словами, этой формулой вы можете «сказать» Excel-ю на сколько ячеек он должен отступить и какой диапазон «захватить».
Синтаксис формулы СМЕЩ такой:
СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где
- ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
- смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
- смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
- [высота] – число строк возвращаемой ссылки (необязательный);
- [ширина] – число столбцов возвращаемой ссылки (необязательный).
Формула ПОИСКПОЗ
Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.
Синтаксис ПОИСКПОЗ такой:
ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )
- искомое_значение – значение, которое ищем. Может быть числом, текстом, логическим значением или ссылкой на ячейку;
- просматриваемый_массив – диапазон ячеек, где будем искать нужное значение;
- [тип_сопоставления] — число -1, 0 или 1, которое показывает, как сравнивать искомое значение с ячейками просматриваемого массива. Не переживайте, если не поняли, когда и что ставить, потому что 90% случаев нужно выбирать ноль.
Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями
Формула СЧЁТЗ
СЧЁТЗ просто считает количество непустых ячеек в диапазоне.
Формула СЧЁТЕСЛИ
Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.
Пошаговая инструкция по созданию списков
Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
- Присвойте таблице имя: на вкладке Конструктор введите имя таблицы — «статьи».
Форматированная таблица «статьи» создана.
Шаг 2. Создайте две сводные таблицы – одну с названиями групп, вторую — со статьями.
Для чего используем сводные таблицы? Во-первых, чтобы вручную не создавать перечень групп, а во-вторых, как уже упоминали выше, чтобы вручную не сортировать справочники статей (что иногда забывают сделать пользователи, а это важно, иначе формула СМЕЩ «срабатывает» с ошибкой). «Ручную» работу сделает вместо нас кнопка «Обновить» в меню Данные — нажимаем ее каждый раз после появления новых статей.
- Создайте первую сводную таблицу с группами статей.
Выделите любую ячейку таблицы с исходными данными, перейдите в меню Вставка -> Сводная таблица. Добавьте сводную таблицу на существующий лист и поместите группы в область строк.
- Создайте вторую сводную таблицу со статьями: меню Вставка -> Сводная таблица. В область строк поместите группы и статьи.
- Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.
- Скройте строку «Общий итог» в обеих таблицах справочников. Перейдите на вкладку Конструктор -> Общие итоги -> Отключить для строк и столбцов.
В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).
Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.
- Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.
- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон:
=СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
Пояснения к формуле:
СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.
- $A$1 – это первая ячейка в справочнике групп.
- Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
- СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
- Последнее число 1 в формуле – это количество столбцов.
Нажмите ОК. Названия листов в формуле появятся сами.
- Точно так же создайте в диспетчере имен список статей.
Введите имя ГруппыСтатей, а для диапазона – формулу:
=СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
Пояснения к формуле:
СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.
- $С$1 – это первая ячейка в столбце с группами.
- ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 Определяет, на сколько строк нужно отступить от первой ячейки. ПОИСКПОЗ ищет название группы, выбранной в таблице с данными (столбец $G) среди ячеек справочника (столбец $C). В адресе ячейки $G2 не «закрепляем» номер строки с помощью знака $, чтобы формула работала для каждой ячейки в столбце.
- Следующая цифра 1 – это отступ на 1 столбец вправо, т.е. переходим к столбцу «статьи», откуда нужно брать данные.
- СЧЁТЕСЛИ( $C:$C ; $G2 ) – считаем число ячеек в столбце $C, в которых названия групп такие же, как в столбце с данными. Здесь тоже не «закрепляем» номер строки у ячейки $G2 с помощью знака $.
- Последнее число 1 в формуле – это количество столбцов.
Шаг 4. Создайте выпадающие списки.
Выделите ячейки в столбце «группы», перейдите в меню Данные -> Проверка данных. Задайте тип данных Список, источник =ГруппыСписок.
То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи
Выпадающие списки готовы. Форматированные smart-таблицы позволят «захватить» все данные, а сводные таблицы – избежать ошибок, отсортировать справочник и создать список групп.
Читайте также:
- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон: