313

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

Представления (Custom Views) — это своего рода альтернативный вариант «Группировки» и «Фильтра», при создании которого запоминается положение столбцов и ячеек, т.е. скрыты ли они или видны, какие группы строк-столбцов свёрнуты/развёрнуты и условия фильтрации.


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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Допустим, что вы часто используете фильтр мужчины/женщины. Выбираем в фильтре пол «М», затем на вкладе Вид нажимаем Представления «Добавить» и в открывшемся окне вписываем имя «Мужчины», затем проделываем то же самое с фильтром «Женщины».

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Для удобной работы с отчётными таблицами можно сделать Представления с подробной статистикой и по кварталам:

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Выделяем столбцы с месяцами C-E, G-I и скрываем их правой кнопкой мыши - Скрыть или нажав Ctrl+0. Затем создаём Представление тем же способом или вписав новое имя в выпадающий список на панели быстрого доступа и нажав Enter.

Теперь можно быстро переключаться между кратким и подробным вариантом с помощью выпадающего списка в левом верхнем углу окна Excel.


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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Примечание:

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

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


Примеры файлов можно скачать здесь

Для просмотра сперва нужно включить строку с представлениями

Найдены дубликаты

+4

Все это хорошо работает в случае плоских таблиц.  К которым данные в большинстве случаев ещё надо привести

раскрыть ветку 8
+1
Вот, вот, для работы с огромными массивами не удобно, сводные наше всё
раскрыть ветку 7
0

НЕНАВИСТЬ!!!

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

Так вот. Как же я блядь НЕНАВИЖУ тех кто вместо обычной плоской таблицы отправляет всякие сводные, таблички с иерархией, или вообще хер знает как оформленный файл со всякими рюшечками.

раскрыть ветку 6
0

О мастер по Экселю) подскажи как вбить одну формулу во все ячейки, что бы не вбивать персонально в каждую для получения ответа)

Иллюстрация к комментарию
раскрыть ветку 2
0

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

раскрыть ветку 1
0

это так не работает)
блин сработало)
спасибо)

0
А если я сделаю представление по фильтру, а потом отредактирую фильтруемый параметр (изменю, или добавлю строки) в представлении изменится вид или нет?
раскрыть ветку 1
0

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

0
Помогите пожалуйста решить проблему :
Есть два файла Excel с базой данных жителей.
1 файл содержит данные жителей по конкретному дому (столбцы: лицевой счёт, фио, номер квартиры) , 2 файл содержит данные всех жителей по ВСЕМ домам (столбцы: лицевой счёт, фио, номер квартиры, email и номер телефона)
ВОПРОС: Возможно ли перенести email и номера телефонов к первому файлу Excel?
Возможно написал не очень доходчиво! (
раскрыть ветку 4
+1
Можно с помощью функций ВПР или ПРОСМОТРX (в office 360). На ютубе найдите канал Николая Павлова и название функции, у него очень понятные видео уроки.
раскрыть ветку 1
+1
Спасибо большое
0

Возможно нечеткий поиск.он же fuzzy lookup вам поможет.тем более надстройка бесплатна полностью.по названию на ютубе найдете хорошие примеры задач

раскрыть ветку 1
+1
Спасибо большое
0

наверное, я что-то не понимаю, но разве не удобнее ли использовать для отчетов и статистики более продвинутые вещи?

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

научитесь в sql, и будет вам счастье.

0

Спрошу не по теме. Какие ресурсы порекомендуете для изучения основ excel и google docs. Вкатываюсь в скором времени в офисную работу, раньше только делал примитивную табличку в документах, которая считала мою зарплату в зависимости от сделанной работы.

раскрыть ветку 1
+1

На ютубе канал Николая Павлова.этого для первого года с головой хватит.

0
У меня другой вопрос. @Veseliy.4el, как сделать тему тёмной?
раскрыть ветку 4
0

Нужно установить MS Office 2019

раскрыть ветку 3
0
У меня есть перечень показателей (он каждый раз меняется, с каждым образцом), скрытие нужных срок ускоряет мою работу, но есть проблема, нужен порядковый номер, а он при скрытии тоже скрывается (
0
Стоит
раскрыть ветку 1
0

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

-2

Ну так-то Metallica как и Slayer к трэш-металлу относится :)

раскрыть ветку 1
0

Все смешалось уже)

Похожие посты
109

Поваренная книга Экселиста #0 - Пару слов об Экселе

Для начала - рад видеть, что моих любимых и обожаемых подписчиков стало 21.. Шутки про  "За 2 дня на Пикабу у меня - очко" оставлю себе))) В первую лавочку пишу для вас, ну и отдельная вам благодарочка за то, что вам это интересно.

Так как я такой же логичный, как стол-жираф-48, начну с того, с чего надо было начать изначально - а зачем, в принципе, Эксель то нужен, и что с ним можно делать (кроме Зиночка_Счет_В_Экселе_Сделает). Тут уже предвижу холивар, прям чувствую, потому сразу подкину на вентилятор.
Основные задачи в классическом понимании это НЕ ХРАНЕНИЕ данных, а их обработка. То есть, если рассматривать классическую MVC-модель - это контроллер. Потому что для представления есть бумага и принтер.

То, что мы храним в Экселе данные - ну да, нам так проще. Но по факту любая СУБД Эксель уничтожит по скорости работы непосредственно с данными. Но, чтобы что-то похожее на обработку организовать на уровне работы с СУБД - придется уже городить несколько этажей SQL запросов.

Потому данные выгружают в Эксель, а там уже и "понеслась звезда по кочкам".

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

Отсюда, как мне кажется, возникает одна такая ошибочка - Эксель это не способ сделать "правильно", это способ сделать БЫСТРО и РАБОЧЕ. 90% расчетных файлов в экселе понимает разработчик и Майкл Джексон, ситуация ровно такая же, как и с ремонтом ( "вот пусть тот криворукий исправляет пол, без пола лампочку не повесить").

Далее чуть объяснений и примеров...

В последнее время очень "модно и молодежно" вводить машинный анализ в прогнозирование возможного поведения клиента (про b2c сегмент говорим). То есть, допустим, на основе покупок клиента делать прогноз (не статистику собираем, а Вангу косплеим) совершения им следующей покупки (как даты, так и состава комплексной покупки) и о возможных similar к ней. И уже на основании этого делать какие-либо выводы и офферы разные предлагать (ну там сырный соус к покупке, ну вы поняли да?)) ).

Реализация этого в экселе занимает от часов до дня, реализация этого в CRM занимает от недель до "на третьи сутки после второго пришествия". Причем реализация на уровне CRM нифига не будет работать без прототипа, реализованного или на бумажке (в виде ТЗ) или в виде костылявых обработок в Экселе. Ведь программист, про кодера даже не будем, не обязан и не будет представлять себе вашу бизнес-логику, ему до нее как Ильичу до лампочки. Да и по поводу ТЗ крайне метко выразился мой препод в институте - то, что вы написали в ТЗ не будет работать так, как это вы написали, а будет так, как прочел (понял) разработчик. Потому мы делает что? Правильно - бьемся челом об сруб светлицы что-то невообразимо-кривое в Экселе, но при перемножении лося и порося получаем искомые 63,3%. Что и служит уже как и вашей (дядиной) прибыли, так и методом контроля разработки. То есть вы берете чистые данные, которые хранятся НЕ в Экселе, а в СУБД, и уже их вьювите и контролите.

Ну и, чтобы разбавить это "много-букофф-ниочем" - боевой пример.

Делаем простейшую экспертную систему в Экселе (без регистации и смс).

Вот таблица:

Поваренная книга Экселиста #0 - Пару слов об Экселе Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных, Субд

Найдите глазами столбец "% win". В зависимости от нее - строка перекрашивается в другой цвет, меняется шрифт, ну и чем значение больше - тем больше вероятность покупки. Цифры относительны, получены крайне простым способом - телефон в руки и обзвон разных групп клиентов. Вычитаем оттуда погрешность "на идиота" и вуаля.

Сама формула:

=VLOOKUP(I109/J109*1000;_tech!$F$2:$G$10;2;TRUE)+
IF(K109="";0;IF(K109>43689;15;VLOOKUP(DATEDIF
(K109;"12/8/19";"D");_tech!$J$2:$K$9;2;TRUE)))

А теперь понимаем как это сделать.

Конкретно этот пример - фитнес. Далее мы ищем данные. Конкретно в моем случае искалось так:

- Таблица с клиентами - CRM (оттуда выгрузка в эксель, листы 62_кк)

- СКУД - с сервера СКУД (да, в CRM она интегрирована криво + карты можно и "забыть" внести в саму CRM, потому что некогда админам) (лист "посещения_приведенные", и "пос_")

- Статистика звонков - с телефонии (потому что оттуда она тупо информативнее, и мне не интересно мнение менеджера о клиенте. Если менеджер говорил более минуты - значит клиенту интересно, просто ему предлагали не то, что ему нужно). (лист 4)

- Дополнительные покупки - CRM (лист "Этот")

- Экспертная оценка - эмпирически-добытые факты. (лист _tech)
(часть данных потер, ибо прайваси-все-дела + обработку специально взял аж с августа, с того времени их было еще версий 20)

Теперь включаем чем думать:

Клиент ходит постоянно -> его все устраивает

Клиент купил карту за сумму N -> сумма его устраивает (то есть предложи столько же или меньше, но не больше)

Клиент покупает что-либо еще -> деньги у клиента не кончились

Карта заканчивается в течение квартала -> купит сейчас, если пункты 1-3 соблюдены.

Клиент женщина 30+, на дворе август, клиент не ходит с июня -> предложить карту и детские занятия со скидкой

Клиенту не звонили с сервисными звонками в течение месяца и пункты 1-3 соблюдены -> звонить в ПЕРВУЮ очередь (не задрочен)

Клиент ходил, сейчас не ходит, возраст до 21 -> звонить и предлагать рассрочку, свалил на лето.

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

Все просто, да? А теперь попытайтесь внедрить это в CRM за неделю) А за две? А за месяц? Да фиг выйдет.. В экселе заняло чуть больше 2х часов.

Коэфы задаем отдельно, даже цвета для выделения строк - тоже задаем через "техническую" вкладку.

Поваренная книга Экселиста #0 - Пару слов об Экселе Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных, Субд

И да, это тоже можно сделать по другому. А можно и не делать, ведь зачем нужно что-то делать, когда можно просто обсудить все в комментах, неправда ли?))

Показать полностью 2
99

Поваренная книга Экселиста #1 - Преобразовываем ФИО

Привет всем, моему одному подписчику - отдельное трямс)
Подумалось - надо чуть поделиться некоторыми наработками, которые собрались в голове за последние лет эдак 20 работы с различными БД (к коим я с легкостью отношу Эксель). В связи с чем будет ажно целый ряд статеечек на тему, в первую очередь, оптимизации рабочего времени (ну в смысле сделал за 10 минут и дальше листаешь пикабушечку). Поехали с достаточно частой задачи:

РЕЦЕПТ 1:  Преобразовываем ФИО в Ф / И / О, без использования VBA и прочей нехристи.
Для начала чуть теории - большинство, наверняка, сталкивались с такой задачей - есть ФИО в одном столбце, а нужно вытянуть только имя, ну или два столбца ИМЯ и ФАМИЛИЯ. Да или даже поменять местами, задачи разные - суть одна. Теперь давайте разбираться что имеем - имеем строку с N-количеством слов, разделенных одинаковым символом " " (ну или чуть сложнее с массивом, содержащим N-количество элементов, в роли разделителя " "). Тут важно понять сам смысл - все, что имеет закономерность, подлежит автоматизации. В нашем случае закономерность будет вот такая - СЛОВО" "СЛОВО" "СЛОВО, следовательно мы ИЛИ должны "выбрать" нужное нам СЛОВО из всей строки, или чуть схитрить) Но начнем с выбора.

ВАРИАНТ 1: Средствами экселя. Определяем нахождение разделителей по длине строки.
1. Создаем отдельную страницу (на всякий случай)
2. Вставляем данные - Допустим вид будет вот такой
ID | ФИО
1  | Иванов Иван Иванович (b2)

3. Выбираем ПЕРВОЕ СЛОВО, ячейка (c2)
=TRIM(LEFT(B2;FIND(" ";B2;1)))
Что сделали - нашли первое вхождение символа " " в строку, и резанули все, что после него.

4. Выбираем ВТОРОЕ СЛОВО, ячейка (d2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1))+1;FIND(" ";B2;FIND(" ";B2;1)+1)-FIND(" ";B2;FIND(" ";B2;1))))
Что сделали - указали Экселю на "координаты" первого и второго разделителей, скорректировали координаты (порезали длину строки на лишний символ " ").

5. Выбираем ТРЕТЬЕ СЛОВО, ячейка (e2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1)+1)+1;LEN(B2)))
Что сделали - то же самое, что и со ВТОРЫМ СЛОВОМ, только так как нам не нужно резать по второй координате - за нее взяли длину строки.

На выходе получили вот так:

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

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

СПОСОБ 2: Подмена разделителя
1. Выделяем столбец с ФИО.
2. Открываем текстовый редактор (дада, Блокнот / TextEdit), вставляем туда. ОБЯЗАТЕЛЬНО переведите его в формат Plain Text/ просто текст (формат сохранения .txt) (!)
3. Находим функцию "Найти и заменить".
3.1. В поле найти введите символ " " (то есть просто поиск по одиночному пробелу).
3.2. В поле заменить вставьте символ TAB. Для этого на первой строке нажмите TAB, выделите его, cmd+c,  cmd+v (или control, у кого какая религия)

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

3.3. Примените, скопируйте, вставьте в Эксель... И собственно так, если не знали - встречайте, TAB - символ переноса на следующую ячейку))

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

Надеюсь данные способы вам потребуются) И, главное, на забывайте - работает не только на ФИО)
Понравилось? Что-то интересно? Прошу в комменты)  Ну и там лайк/ подписка / кошелек / очки / мотоцикл)

Показать полностью 3
5838

Картинка в диаграмме Excel

Недавно делал презентацию и захотелось внести разнообразия в простые диаграммы Excel, и я решил добавить картинку в фон диаграммы:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

Для построения такой диаграммы нам нужна таблица, где формула =ОКРВВЕРХ.МАТ(МАКС($B$3:$B$7);1000)-B3 будет добавлять в отдельном столбце недостающую сумму от округлённого максимального значения столбца «В» (это необходимо для заполнения диаграммы):

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

1. Выделяем нашу таблицу, на вкладке «Вставка» выбираем диаграмму «Гистограмма с накоплением»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

2. Настраиваем формат оси, назначив предельное значение 6000

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

3. Удаляем сетку и легенду:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

4. В значке «Фильтры диаграммы» выбираем «Выбрать данные», затем «Изменить», где в открывшемся поле выделяем диапазон «Года»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

5. Кликаем мышкой по центру диаграммы и в открывшемся меню вставляем рисунок:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

6. Ставим боковой зазор 0%

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

7. Выделяем оранжевый фон и заменяем его на белый:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

8. Затем выделяем столбцы диаграммы и выбираем «Нет заливки»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

9. Определяем границы диаграммы выбрав белый цвет и ширину 3 пт

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

10. Меняем название диаграммы нажав знак «равно» и указав ячейку «Доходы»

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

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

Показать полностью 10
1408

Выпадающие списки с удалением

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

Этот приём удобно применять при распределении дежурств (товаров), чтобы не запутаться и не назначить одного человека дважды, а также наглядно видеть тех, кто остался в запасе. В итоге у нас получится вот такая таблица:

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

1. Сперва необходимо подсчитать, кто из имеющихся сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к серой таблице ещё один столбец, в который введём формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2)

=COUNTIF($B$2:$B$8;E2)

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

Эта формула вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.


2. Далее выявим свободных сотрудников. Добавим ещё один столбец и введём в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1)

=IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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

=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;

НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))


=IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;

SMALL($H$2:$H$10;ROW(E2)-1)))

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

Эта формула выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.


4. Далее создаём именованный диапазон сотрудников нажав Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», где из столбца списка E2 зададим название «Имена», а в строке «Диапазон» введём формулу:

=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))


=OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-

COUNTBLANK(Лист1!I$2:I$10))

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

5. Создадим выпадающий список выделив ячейки B2:B8 и на вкладке «Данные» - «Проверка данных» - «Список» - «Источник» =Имена

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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

Показать полностью 4
471

Связанные выпадающие списки в Excel

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


1. С помощью функции ДВССЫЛ


ДВССЫЛ (INDIRECT) преобразовывает содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Мир», то функция выдаст ссылку на именованный диапазон со словом Мир.


Итак, начнём. Выделяем весь список «Певец» (с ячейки А2 и вниз до конца списка) и нажав Ctrl+F3 или на вкладке Формулы - Диспетчер имён – Создать задаём имя диапазону (Певец). Затем таким же способом проделаем то же самое с остальными списками «Группа» и «Дата».

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

После нажатия на ОК первый выпадающий список готов:

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Дальше создадим второй (зависимый) выпадающий список, в котором будет отображаться содержимое списков в зависимости от выбранной категории. Аналогично предыдущему способу, в окне «Проверка данных» в поле «Источник» вводим формулу =ДВССЫЛ(E2), где E2 – адрес ячейки с первым выпадающим списком.

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Минусы данного способа:

- Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нём есть текст с пробелами, то придётся их заменять на подчёркивания с помощью функции ПОДСТАВИТЬ, =ДВССЫЛ(ПОДСТАВИТЬ(E2;" ";"_"))

=INDIRECT(SUBSTITUTE(E2;" ";"_"))

- Надо вручную создавать много именованных диапазонов (если у нас много значений).


2. Для следующего способа нам нужна таблица, разбитая по категориям и видам:

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Как и в предыдущем способе через Ctrl+F3 создаём именованные диапазоны каждой категории (без заголовков). Т.е. сначала «Мебель», затем «Техника» и так каждую по отдельности.

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Теперь создаём первый выпадающий список по категориям. Для этого на вкладке «Данные» - «Проверка данных» - «Тип данных» выбираем «Список» и в поле «Источник» вводим названия диапазонов (Мебель;Техника)

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Далее для второго зависимого списка тем же способом для ячейки «Вид» в поле «Источник» вводим формулу =ДВССЫЛ($B$14), а для «Тип» =ДВССЫЛ($B$15). В результате у нас получится следующее:

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

3. С помощью функций СМЕЩ и ПОИСКПОЗ


Для использования этого способа необходим отсортированный список с отдельным образцом имеющихся значений:

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Далее нажимаем Ctrl+F3, где задаём имя диапазону D1:D3 (Товар). Затем на вкладке «Данные» - «Проверка данных» - «Тип данных» - «Список» и в строке «Источник» указываем =Товар или просто выделить ячейки D1:D3 (если они на том же листе, где список).

Чтобы выпадающий список автоматически пополнялся новыми данными из категории «Товар», открыв «Диспетчер имён» в строке диапазон вписываем формулу =СМЕЩ($D$1;0;0;СЧЁТЗ($D$1:$D$400);1) где $D$400 количество ячеек необходимое для образца выпадающего списка.

=OFFSET($D$1;0;0;COUNTA($D$1:$D$400);1)


Для зависимого списка товара создадим именованный диапазон с функцией СМЕЩ, который будет динамически ссылаться только на ячейки товара определенного вида. Для этого нажав Ctrl+F3 создаём новый именованный диапазон с любым именем (например, Вид) и в поле «Диапазон» в нижней части окна вводим следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)


Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов.


Функция СМЕЩ выдаёт ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. Вот так: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)


Таким образом:

- начальная ячейка – берём первую ячейку нашего списка, т.е. А1

- сдвиг_вниз – считает функция ПОИСКПОЗ, которая выдаёт порядковый номер ячейки с выбранным товаром (G2) в заданном диапазоне (столбце А)

- сдвиг_вправо = 1, т.к. мы хотим сослаться на Вид в соседнем столбце (В)

- размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ, которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – товара (G2)

- размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с Видом товара


В итоге у вас должно быть так:

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост
Добавляем выпадающий список на основе созданной формулы к ячейке G3, нажав на вкладке «Данные» команду «Проверка данных», где выбираем «Список» и в качестве «Источника» указываем =Вид
Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 10
838

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

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

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


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


1 способ


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

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

2 способ


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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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


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


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

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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


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

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

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

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

Выпадающие списки в Excel 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 будет спрашивать:

Выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Показать полностью 10
604

Защита данных в Excel

Приветствую всех! В этот раз мы рассмотрим способы защиты данных в Excel, от примитивных приёмов, до более серьёзных с паролированием.


Спрятать содержимое ячеек


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

Нажимаем Ctrl+1 или выбираем «Формат ячеек», в открывшемся меню «Число»-«Все форматы»-«Тип» вводим подряд 3 точки с запятой без пробелов.

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

В результате, выделив диапазон ячеек можно скрывать содержимое применив данные символы. Для отображения прежних значений там же выбираем «Основной».

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


Защита ячеек листа от изменений


В случаях, когда необходимо защитить не весь лист, а только некоторые его части, оставив пользователям возможность вводить информацию в определенные ячейки, нужно выделить ячейки, которые не надо защищать (если такие есть), нажимаем Ctrl+1 «Формат ячеек»-«Защита» и снимаем флажок «Защищаемая ячейка».

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

По умолчанию этот флажок всегда включён для всех ячеек.

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

Этот приём необходим т.к. в Excel команда «защитить диапазон», отсутствует, а есть только «защитить лист» и «защитить книгу», поэтому таким образом мы решаем эту проблему.


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

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Например, необходимо оставить пользователям:

- возможность помечать ячейки маркером (но не менять их содержимое) –включаем флажок «Форматирование».

- использование фильтрации и/или сортировки (через Автофильтр) – включаем флажки «Использование автофильтра» и/или «Сортировка». Только перед включением защиты сперва создайте сам фильтр на вкладке «Данные».


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


Выборочная защита диапазонов листа для разных пользователей


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

Чтобы это сделать, выбираем на вкладке «Рецензирование» кнопку «Разрешить изменение диапазонов».

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

В появившемся окне нажимаем кнопку «Создать» и вводим имя диапазона, адреса ячеек и пароль к нему:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

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

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


Защита листов книги


С помощью кнопки «Защитить книгу» на вкладке «Рецензирование» защищаем документ от удаления, переименования, перемещения листов в книге, изменения закрепленных областей (зафиксированной «шапки» таблиц и т.п.), возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Флажок «Структуру» отмечен по умолчанию, т.к. он подразумевает стандартный набор функций защиты листов от удаления, переименования, копирования.

Флажок «Окна» не обязательный и необходим для запрета пользователю сворачивать и/или изменять размеры окна книги внутри окна Excel или изменять закрепление областей.


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

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Шифрование книги


В Excel имеется возможность зашифровать весь файл книги, используя алгоритм шифрования AES 128-bit. Взломать его напрямую невозможно, а автоматический подбор пароля осложнён низкой скоростью перебора вариантов, поэтому не забывайте свои пароли.


Данную защиту можно задать при сохранении книги, выбрав «Файл» – «Сохранить как» или клавишу F12, в окне сохранения нажать «Сервис» – «Общие параметры».

В появившемся окне можно ввести два разных пароля – на открытие файла (только чтение) и на изменение:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Если задать первый пароль (для открытия), то пользователь не сможет даже открыть файл для просмотра.

Если же пользователь знает первый пароль, но мы задали и второй (для изменения), то пользователю надо будет либо ввести пароль для изменения, либо ограничиться просмотром, нажав на кнопку «Только для чтения».

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

Кстати, в Microsoft Word этот способ тоже работает.


Суперскрытый лист


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

Чтобы улучшить защиту у нас должно быть открыто минимум два листа (т.к. один лист мы скроем), затем открываем редактор Visual Basic, на вкладке «Разработчик» или нажатием Alt+F11, выбираем «Visual Basic».


Нажатием Project Explorer или Ctrl+R откроется меню

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

В верхней части выделяем наш Лист1, а в нижней части находим свойство Visible и делаем его xlSheetVeryHidden.

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Можно переименовать Лист2 на какое-нибудь слово, чтобы не палиться о наличии Листа1 и теперь узнать о его существовании можно только в редакторе Visual Basic, в других местах он отображаться не будет.


Берегите свои данные, чаще сохраняйтесь Shift+F12 и вы никогда ничего не потеряете.

Показать полностью 9
248

Секционные диаграммы

Приветствую всех! Это последний пост про Excel в этом году, в новом году будет много других полезных и интересных постов)


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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Копируем названия стран из столбца А1 в любое свободное место вставив как «Значения», затем на вкладке «Данные» нажимаем на кнопку «Удалить дубликаты», в результате все пустые ячейки удалятся.

Далее копируем и вставляем названия в ячейку С1 выбрав «Транспонировать».

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Также на вкладке «Конструктор» можно изменить тип диаграммы на «Линейчатую с накоплением»

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

В результате получим удобную и понятную диаграмму

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Узловые диаграммы


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


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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Кликаем правой клавишей мыши по диаграмме и выбираем «Выбрать данные», в открывшемся окне нажимаем «Изменить»

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Далее для ввода адреса в строку выделяем содержимое диапазона столбцов А и В и нажимаем ОК

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 8
272

Диаграмма «Шкала»

Диаграмма «Шкала» может применяться для демонстрации достижения цели, однако в стандартном наборе Excel она отсутствует, поэтому мы создадим её сами. Для этого нам понадобится вот такая таблица со значениями, из которой мы установим параметры для диаграммы:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Выделяем столбцы А и В, затем на вкладке «Вставка – Диаграммы» выбираем «Гистограмма с накоплением», в результате она выдаст каждое значение по отдельности:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Нам нужно объединить все значения в один столбец, для этого на вкладке «Конструктор» нажимаем кнопку «Строка/столбец»:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Кликаем на чёрный цвет и в диалоговом окне «Формат ряда данных» нажимаем на «По вспомогательной оси» и ползунком настраиваем «Базовый зазор», чем он меньше, тем шире чёрная полоса:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Теперь настроим «Цель», для этого нажав правой клавишей мыши на бардовый цвет, выбираем «Изменить тип диаграммы для ряда», в открывшемся окне выбираем «точечную» диаграмму:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

В результате она превратится в маленькую точечку, которую мы настроим на вкладке «Конструктор» - «Добавить элемент диаграммы» - «Пределы погрешностей» - «Стандартная погрешность»:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Теперь задаём ей параметры «Фиксированное значение», чем больше, тем полоска становится длинней. Также настраиваем линии, сделаем их потолще, саму точку можно удалить здесь же на вкладке «Маркер» - «Параметры маркера» - «Нет»:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Остаётся последний штрих, удаляем лишнее значение диаграммы нажатием «Delete», оставив только нужное:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

В итоге получаем вот такую диаграмму «Шкала»:

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Диаграмма «Шкала» Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 8
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: