1632

ТОП-30 горячих клавиш в Excel нужно знать каждому

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

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

30 горячих клавиш, которые сберегут уйму вашего времени, кратно увеличат скорость работы, сделают ее приятнее и комфортнее ⬇⬇⬇ :

CTRL+N
– создать новую рабочую книгу;
CTRL+O – открыть существующую книгу;
CTRL+S – сохранить активную книгу;
F12 – вызвать диалоговое окно Сохранить, как;
CTRL+W – закрыть активную книгу;
ALT+TAB – переключение между открытыми приложениями;
CTRL+C – копирование выбранного элемента (-ов);
CTRL+X – вырезание выбранного элемента (-ов);
CTRL+V – вставка скопированного или вырезного ранее;
CTRL+ALT+V – окно Специальная вставка;
CTRL+HOME – возвращение к началу рабочего листа (ячейка A1);
CTRL+END – переход к последней заполненной ячейке текущего листа;
CTRL+SHIFT+ ⬅ ⬆ ⬇➡ – выделение данных на листе по стрелкам;
CTRL+A – выбор всех элементов в документе или окне;
CTRL+SHIFT+L – установить фильтр на таблицу;
CTRL+K – окно Вставка гипперсылки;
CTRL+1 – окно Формат ячеек;
CTRL+SHIFT+% – применить процентный формат к выбранному диапазону;
SHIFT+F2 – вставить примечание в ячейку, если его нет, редактировать примечание, если есть;
ALT+ENTER – новая строка в той же ячейке (перенос строк);
SHIFT+ (+)/(-) – добавление столбца(ов) / удаление столбца(ов);
CTRL+ (+)/(-) – добавление строк(и) / удаление строк(и);
CTRL+E – применить Мгновенное заполнение;
CTRL+Q – окно Быстрого анализа (анализируемый диапазон должен быть выделен);
CTRL+T(L) – окно Создание таблицы;
CTRL+F/H – окно (Найти)/(Заменить);
CTRL+P – окно Печать;
CTRL+Z – отмена последнего действия;
CTRL+Y – повтор последней команды или действия;
ESC – отмена текущего действия.


Лайк +100 к карме)

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

+39
Большая половина сочетаний работает во всех приложениях
ещё комментарии
+29

Ctrl + 2 - жирный шрифт в ячейке
Ctrl + 3 - курсив
Ctrl + 4 - подчеркнутый
Ctrl + 5 - зачеркнутый
Ctrl + Shift + ~ - общий формат ячейки

Ctrl + Shift + 1 - числовой формат с двумя знаками после запятой

Ctrl + Shift + 2 - формат дата-время
Ctrl + Shift + 3 - формат дата

Ctrl + Shift + 4 - денежный формат

Ctrl + Shift + 5 - процентный без знаков после запятой

Ctrl + Shift + 6 - экспоненциальный

Shift + F11 - создать новый лист в книге
Так же можно нажать Alt и посмотреть, какие дальше буковки нажать, чтобы активировать действие с ленты. Примеры для Excel 2013:
Например, Alt -> я -> ч -> л - удалить текущий лист (или группу листов, если они выделены)
Или Alt -> я -> т -> ф -> п - переименовать текущий лист

раскрыть ветку 5
+18
Самая полезная в Excel F2. Позволяет 'провалиться' в ячейку для редактирования
+3
Когда в комменте полезного больше чем в посте
0
На ленту нужные действия можно закрепить и через альт и цифру вызывать тоже удобно
раскрыть ветку 1
0
Да, тоже шикарно)
-10

Да, сочетаний много больше 30, все познается в процессе. Спасибо за дополнение)

ещё комментарий
+74

Лови кармадрочера!

раскрыть ветку 1
+22
Да вообще пиздец какой-то. Пост как будто дешевый SMM-щик писал. Для полного набора только эмодзи через слово не хватает.
+6

Топ горячих клавиш! А какая из них всё-таки самая лучшая?

раскрыть ветку 6
+9
F4. Повторяет последнее действие. Если было последним действием удаление строки/столбца, то это повторяется, если до этого покрасил ячейку, то красит. Сэкономило мне много времени, когда перекрашивал ячейки и лень было тянуться вверх вниз.
+7
F2 - позволяет отредактировать значение ячейки, а не переписывать ее или тянуться к мышке
ещё комментарии
-9

Вопрос не однозначный, все зависит от действий, которые вы делаете чаще всего, но периодическое нажатие CTRL+S – сохранить активную книгу (если не настроено Автосохранение), может сберечь ваши труды. CTRL+C CTRL+V классика)

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

Видимо, мой сарказм был слишком завуалирован...

ещё комментарии
+12

ТОП-30 горячих клавиш в Excel нужно знать каждому ???
Я водитель трамвая, нахрена мне знать эти горячие клавиши? ))))

раскрыть ветку 1
+2
Я программер, мне они тоже нахрен не сдались =D
+26

ALT+F4

раскрыть ветку 16
+10
Win+R, "cmd" "format c"
раскрыть ветку 11
+4
"enter"
ещё комментарии
0
format c: /fs ntfs
раскрыть ветку 2
0

вы написали тупость, сэрр

-1

тогда уж

Win+R, "cmd" "echo Y | format c:"

раскрыть ветку 4
0
Давай без читов
-17
Да, так тоже можно закрыть программу, все же Ctrl+W тянуться ближе, хотя если пальцы легко садятся на шпагат))) то гуд
раскрыть ветку 2
+2

Ctrl+W закрывает один только один текущий  файлик

ещё комментарии
ещё комментарии
+3
CTRL+END – переход к последней заполненной ячейке текущего листа;

Ну не чавчем.

Как пример (у меня, правда LO а не MSO):

На листе заполнены ячейки А221 и АР2.

CTRL+END позиционирует курсор на ячейку АР221, хотя (технически) она не заполнена.

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

раскрыть ветку 2
+1
Однако, она является последней ячейкой в заполненной части листа )
раскрыть ветку 1
0

Между

последней заполненной ячейке текущего листа

и

последней ячейкой в заполненной части листа

есть мааааааленький нюанс...

+3

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

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

ДА КАК ЖЕ ПИЧОТ с этим ша... из всех углов, все тренеры по чертовой эффективности рассказывают про то как важно печтать в слепую и при этом упоминают это г...но, которое по эффективности обучения соспоставимо с зубрением учебников в дореволюционных деревенских школах. Есть другие программы, которые ставят печать буквально за несколько дней, а не мучают унылыми упражнениями в-перемешку с не менее унылыми байками.

раскрыть ветку 6
0
Подскажите, какие. Понятно, что "соло" не идеал, мне просто под руку попался, ну и раскручен, да.
раскрыть ветку 1
-2

Лично учился давно, ибо не мальчик, пишите что за софт, "динозавры" требуют новых идей)

раскрыть ветку 3
-9

Да вы правы, 10пальцевому набору еще в школе надо обучать. В ваших силах дать детям хороший задел для будущего  могу посоветовать простую программку https://stamina.ru/ для вашего сына. Хотя прогресс не щадит ничего, уже есть приложения пишущие под диктовку.

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

а вот подскажите, обычный коммент и у него 9 минусов, это за что его так? за упоминание стамины что ли?

-2

Или "Соло на клавиатуре" Шахиджаняна. Я по нему освоил. Работает методика.

-1

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

раскрыть ветку 1
-5

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

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

Я без курсов, сам просто текст набирал в слепую, месяц печатал и ругался, однако, потом пошел прогресс

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

да-да-да, это именно для более серьезного отношения. хотя и сам курс как минимум очень неплох.

ещё комментарии
+1
Чел нормально заливает. Пользуюсь большинством этих шорткатов, гораздо быстрее, чем мышкой щёлкать. В основном работаю в excel, word придумал диавол, или просто конечный мудак, блэт! Когда сломал левый мизинец и был в гипсе, с ctrl и shift приходилось сложно — хреначил пол-клавы по привычке 🙂
+1
"CTRL+L – окно Вставка гипперсылки;"
Должно быть CTRL+K вроде

Некоторые сочетания не знал, хотя вполне могут пригодиться в работе. Осталось настроить себя на их использование. Спасибо за пост
ещё комментарии
+1
@Moderator, а разве выклянчивание плюсиков не попадает под "призыв к накрутке/скрутке рейтинга"?
0

Вы забыли самое главное - SCROLL LOCK. Это выключить "Аааа, что за неведомая ебаная хуйня творится с моим экселем меня взломали вирус комп сгорел памагити!!!!"

0
бляяя, а есть горячая клавиша "show details"? чтобы схлопнутые сгруппированные строки разворачивать
раскрыть ветку 1
0
Попробуйте Alt + Shift + = (и сворачивает и разворачивает)
0
лучше скажите как закрепить ячейку горячей клавишей при добавлении в формулу, вместо того чтобы в ручную $ ставить.
раскрыть ветку 1
-1
Выделите ссылку на ячейку в формуле, которую нужно закрепить и нажмите F4, при нажатии несколько раз ссылка, строка или столбец можно сделать абсолютным
0

CTRL+(стрелки) - переход на последнюю по направлению заполненну./пустую ячейку
CTRL+SHIFT+ (стрелки) - выделение всех заполненных ячеек по направлению или выделение всех пустых ячеек до первой заполненой.
Сочетание клавиш зависит от того какая ячейка выделена заполненная или пустая.

0

Всем привет. Вопрос к знатокам. Подскажите, как строки, где есть ячейки со значениями через запятую, дублировать в уникальную строку с новым значением? На примере колонка #7 

Иллюстрация к комментарию
0
Самое нужная клавиша о которой я узнала это F4-повторение последнего выполненного действия. Работает в Экселе 100%.
0
Моя основная боль это как переключаться между листами? Все время пытаюсь alt+tab сделать)
раскрыть ветку 2
+1

CTRL+PAGE DOWN переключение между вкладками книги слева направо

CTRL+PAGE UP переключение между вкладками книги справа налево

раскрыть ветку 1
0
Спасибо!!!! Столько лет мучений!!!
0
Самую главную клавишу не написали.
Shutdown
0
Альт+таб не относится к екселю. А вот кнтрл пэйдж ап и даун я бы добавил
раскрыть ветку 1
-2

Сочетание общее для Windows, но как помогает переключаться между соцсетями и рабочим процессом)

0

Ты забыл про F4 !

раскрыть ветку 1
-2

Повтор последнего действия, согласен полезно

0

Сохраню, чтобы никогда не пользоваться ))

раскрыть ветку 1
-3

Предупреждён — значит вооружён)

0
Есть сочетание, позволяющее удалить ячейку со сдвигом вверх?
раскрыть ветку 2
-4

CTRL + "-" не подходит?

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

нет. эта комбинация уменьшает масштаб страницы

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

Переключение между листами в книге:

Ctrl +PageUp

Ctrl +PageDown

0

А кто подскажет, как сделать горячую клавишу на кнопку "объединить ячейки"???

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

Выделить ячейки, которые нужно объединить, нажать Alt и последовательно Я – Щ – (Б, Д, Ъ, И)

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

Это получается в новом, а в экселе 2007, там же нету такого?

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

ооо, за F12 и Ctrl+1 благодарю! А так, многие уже знал.

0

Выделить столбец CTRL+ПРОБЕЛ

Выделить строку SHIFT+ПРОБЕЛ

0

Не знаю зачем, но я сохранил

раскрыть ветку 1
+1
Пригодится
0
Злоебучая вставка текущей даты
нужно чтобы при открытии таблицы была англ раскладка. Тогда ctrl+Ж вставит дату
0

Win+v журнал копирования буфер обмена

0
Как при помощи клавиатуры выбрать предлагаемую формулу, которая выскакивает одним из вариантов?
раскрыть ветку 2
-4

Если, верно понял вопрос, вы имеете ввиду функцию. Формулу нужно начать вводить со знака =, далее пишите название функции,если такая функция существует выйдут варианты, стрелками выберите ту, которая нужна, если их несколько и лень писать до конца.Для выбора функции и нажмите клавишу TAB. Далее выберите диапазон и нажмите ENTER для подтверждения.

раскрыть ветку 1
0
Именно то, что я имел ввиду. Как же я табом не догадался воспользоваться. Спасибо
0

ALT+F8 запуск макроса

ещё комментарии
0

ктрл-инс, шифт-инс, шифт-дел имхо удобнее, нежели ктрл-с, ктрл-в, ктрл-х

раскрыть ветку 3
0
Ваш вариант предполагает либо делать двумя руками либо очень длинные пальцы
раскрыть ветку 1
0
большой+указательный или большой+средний палец вам в помощь)
0

дело вкуса и личного удобства

0
Вместо ctrl+v можно использовать shift+insert
-1

И нахрена все это знать любому пользователю? Любому пользователю вообще далеко не факт что нужен excel

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

Чёй-то "каждому"? Мне вот всегда хватало Alt-F4

Не нужен мне excel

-1

Вот именно из-за этих сочетаний не приемлю сочетание Ctrl-Shift для переключение раскладки.

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

ВПР и числа-как-текст

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.


Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Как и множество других функций и инструментов Excel, ВПР считает числа как текст не равноценными этим же числам в нормальном виде, поэтому выдаёт ошибку #Н/Д, сигнализируя, что искомое значение не обнаружено, хотя визуально оно, вроде бы, есть.


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


Примечание: Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда - нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа - по правому.


Вариант 1. Числа-как-текст в искомых значениях


Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.


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

- умножения или деления на 1

- прибавления или вычитания 0

- двойного знака минус перед артикулом (равносильно двойному умножению на -1)


Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 2. Числа-как-текст в таблице, где ищем


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

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

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


Вариант 3. Пропадание начальных нулей


Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах


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

Звучит страшно, но решается легко - нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция прокачает обе версии ВПР - "текстовую" и "числовую" - и выдаст ту, которая не приводит к ошибке #Н/Д:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вот и всё - и никаких больше ошибок :)

Источник

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

Суммирование в Excel сложение, мастер функций, автосумма, горячие клавиши

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

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


Сумму чисел можно найти двумя путями:

1. Сложением ячеек или констант,через знак "+" в формуле;

2. Используя встроенную функцию СУММ.


Функция СУММ(SUM) – математическая функция Microsoft Excel, позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.


Имеет вид:

=СУММ(число1;[число2];…), где:


Число1 (Обязательный аргумент), первое число для сложения. Может быть число 4 (константа), ссылка на ячейку, например, G6, или диапазон ячеек, например, A2:A10.

Число2 – 255 (Необязательный аргумент ), второе число для сложения. Можно указать до 255 чисел.


Функцию можно вызвать:


1. Выберите в строке меню Главная ► Редактирование ► Автосумма:

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

2. Строка меню Функции ► Библиотека функций ► Автосумма или в разделе Математические:

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

3. С помощью мастера функций, нажав на иконку в строке формул:

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

Окно мастера функций:

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

или в строке меню Функции ► Библиотека функций ► Вставить функцию:

Суммирование в Excel  сложение, мастер функций, автосумма, горячие клавиши Excel, Отдел кадров, Бухгалтерия, Аналитика, Офис, Продуктивность, Таблица, Видео, Длиннопост

4. Самый быстрый способ, сочетание клавиш ALT+ =


☝ Функция СУММ(SUM) не работает!?!


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


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


Коллега переслал файл, в котором суммы при протяжке формул не меняются?

Проверьте стоит ли автоматический пересчет в файле, на строка меню Формулы ► Параметры вычислений ► Автоматически.


В этом видео собраны все возможные варианты суммирования, от самых не продуктивных, до быстрых и эффективных ⬇⬇⬇

Показать полностью 5 1
350

Поиск и удаление повторяющихся значений в Excel

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Работая с данными, в программе Microsoft Excel, возможно вы сталкивались с ситуациями, когда нужно найти и удалить повторяющиеся данные в таблице.

Давайте разберемся, как это сделать быстро.


Инструмент Удаление дубликатов


1. Выделите ячейку таблицы, в которой необходимо удалить дубликаты.

2. Выберите на вкладке Данные ►Удалить дубликаты:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

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

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

В результате список сократится до уникальных значений:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

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


1. Скопируйте данные или CTRL+С

2. Вкладка меню Главная ► Вставить ► Специальная вставка ► Траспонировать или вызов окна CTRL+ALT+V


Для корректной работы инструмента с полями формата Дата убедитесь, чтобы все поля имели одинаковый формат даты, например 01.01.2020


Удаление дубликатов при помощи Расширенного фильтра


Операцию выше можно сделать при помощи инструмента Расширенный фильтр.

Актуально, если у вас версия Excel 2003, в которой еще нет инструмента Удалить дубликаты.


1. Выделите шапку таблицы или всю таблицу (CTRL+A), в которой необходимо найти дубликаты.

2. Выберите на закладке Данные ► в разделе Сортировка и фильтр Дополнительно:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

3. В окне, выберите вариант обработки Скопировать результат в другое место.

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

5. Поставьте галку Только уникальные записи:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

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

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Вам необходимо выделить уникальные значения в исходной таблице?

Скройте повторяющиеся значения при помощи Расширенного фильтра:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

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

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

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Поиск дубликатов при помощи Сводной таблицы


1. Добавьте в вашу таблицу дополнительное поле для проверки:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

2. Создайте Сводную таблицу, вкладка Вставка ► Сводная таблица.

Настройте поля:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

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

4. Уберите промежуточные итоги Конструктор ► Промежуточные итоги:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Выделение повторяющихся значений при помощи условного форматирования для Excel 2007+


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

2. На вкладке Главная ► Условное форматирование ► Правила выделения ячеек ► Повторяющиеся значения:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

3. В окне Повторяющиеся значения, при необходимости выберите формат выделения дубликатов:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Данные будут подсвечены:

Поиск и удаление повторяющихся значений в Excel Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Таблица, Продуктивность, Ms Office, Видео, Длиннопост

Как удалить дубликаты и разбить текст по столбцам, смотрите видео ⬇⬇⬇

Показать полностью 13 1
131

Применение Временной шкалы и Срезов в Excel

Использование стандартных фильтров в Excel не всегда удобно и ограничено одной таблицей. Для увеличения продуктивности в Excel существуют удобные инструменты, такие как Временна шкала и Срез. Они позволяют быстро выбирать данные по заданным параметрам, в том числе для нескольких таблиц сразу.

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Срезы можно использовать для "Умных" и Сводных таблиц, Временную шкалу только для Сводных.


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


Срез


Срез представляет собой фильтр, вынесенный в отдельный графический элемент. Добавить один или несколько Срезов, для "Умной" или Сводной таблицы, можно на вкладке Анализ ► Вставить срез:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

В появившемся окне выберите столбец или несколько столбцов по которым будем построен фильтр:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Либо, на закладке Вставка ► Срез.


Внешний вид Срезов:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Временная шкала


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

Чтобы добавить шкалу на лист, выберите на вкладке Анализ ► Вставить временную шкалу:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Так же можно добавить через вкладку Вставка ► Временная шкала.


В открывшемся окне установите галку на против Дата ► ОK:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Внешний вид Временной шкалы:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Чтобы производить фильтрацию Временной шкалой:


1. Нажмите на стрелку рядом с отображаемым временным уровнем и выберите нужный вариант (год, квартал, месяц или день):

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

2. Перетащите полосу прокрутки временной шкалы к периоду времени, который вы хотите выбрать;


3. В элементе управления отрезком времени нажмите левой кнопкой на плитку периода времени и зажав перетащите ее, будет выбран период.


Чтобы очистить Временную шкалу или Срез, нажмите на шкале в правом верхнем углу кнопку Очистить фильтр.


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


Настройка вида Временной шкалы и Среза


Временную шкалу или Срез можно переместить расположив в более удобном месте, изменить их размер или стиль.


Чтобы переместить фигуру, просто перетащите ее в нужное место на листе.


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


Чтобы изменить стиль, нажмите на фигуру, отобразится меню Инструменты временной шкалы, выберите нужный стиль на вкладке Параметры.


Использование Временной шкалы и Срезов для нескольких Сводных таблиц


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


Щелкните на Временную шкалу или Срез, а затем выберите на вкладке Параметры ► Подключения к отчетам. В открывшемся окне выберите Сводные таблицы, которые вы хотите добавить:

Применение Временной шкалы и Срезов в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Видео, Длиннопост

Для использования Временной шкалы для "Умной" таблицы преобразуйте её в Сводную, на вкладке Конструктор ► Сводная таблица или Вставка ► Сводная таблица. Это очень удобно и даёт больше вариантов для маневра.


Подробнее о Временной шкале и Срезах смотрите в видео ⬇⬇⬇

Показать полностью 8 1
240

"Умные" таблицы в Excel

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

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


Разве данные в Excel имеющие структуру таблицы – это не таблица?
Отвечу вам: Нет.

То что вы считаете таблицей, представляет собой Рабочую область листа.


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


Создание Таблицы


Для создания Таблицы встаньте на Рабочую область листа содержащую данные для создания таблицы и нажмите сочетание клавиш CTRL+T(L). В появившемся окне вы можете изменить диапазон выделения данных для построения таблицы, нажмите ОК.

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

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


Переименование Таблицы


В вашем файле планируется несколько Таблиц? Есть смысл присвоить для каждой Таблицы свое имя. Это облегчит их дальнейшее использование (например, при работе в Power Query или Power Pivot).


Для переименования установите курсор в любую ячейку Таблицы, в появившемся окне Конструктор в разделе Свойства введите новое имя в поле Имя таблицы:

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

Свойства Таблицы


1. Заголовки таблицы берутся из первой строки исходного диапазона.

При прокрутке вниз названия столбцов Таблицы (шапки) заменяют названия столбцов листа, следовательно дополнительное закрепление столбцов не требуется:

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

2. В таблицу по умолчанию добавляется фильтр, который можно убрать на вкладке Конструктор или сочетание CTRL+SHIFT+L:

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

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


4. При добавлении в таблицу новых столбцов и строк они автоматически включатся в Таблицу. При наличии формул они автоматически применяются для новых данных.


5. Добавить строку итогов (суммирование, среднее и др.) для столбцов Таблицы можно сочетанием клавиш CTRL+SHIFT+T:

"Умные" таблицы в Excel Excel, Аналитика, Бухгалтерия, Продуктивность, Ms Office, Офис, Таблица, Длиннопост

Настройка Таблицы


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


Для использования Временной шкалы для Таблицы преобразуйте её в Сводную, на вкладке Конструктор ► Сводная таблица или Вставка ► Сводная таблица.


Преобразование Рабочей области в Таблицу является обязательным условием для использования возможностей Power Query и Power Pivot.

Показать полностью 5
267

Редизайнер таблиц в Excel

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


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

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

- одна строка - одна законченная операция (сделка, продажа, проводка, проект и т.д.)

- без объединенных ячеек

- без разрывов в виде пустых строк и столбцов


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

из такой таблицы

Редизайнер таблиц в Excel Excel, Макрос, Vba, Таблица, Полезное, На заметку, Длиннопост

сделать

Редизайнер таблиц в Excel Excel, Макрос, Vba, Таблица, Полезное, На заметку, Длиннопост

В терминах баз данных нижнюю таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.


Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt+F11. Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

Sub Redesigner()

Dim i As Long

Dim hc As Integer, hr As Integer

Dim ns As Worksheet

hr = InputBox("Сколько строк с подписями сверху?")

hc = InputBox("Сколько столбцов с подписями слева?")

Application.ScreenUpdating = False

i = 1

Set inpdata = Selection

Set ns = Worksheets.Add

For r = (hr + 1) To inpdata.Rows.Count

For c = (hc + 1) To inpdata.Columns.Count

For j = 1 To hc

ns.Cells(i, j) = inpdata.Cells(r, j)

Next j

For k = 1 To hr

ns.Cells(i, j + k - 1) = inpdata.Cells(k, c)

Next k

ns.Cells(i, j + k - 1) = inpdata.Cells(r, c)

i = i + 1

Next c

Next r

End Sub

После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик - Макросы (Developer - Macros) или нажав сочетание Alt+F8.


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


Также есть второй вариант для работы с большими таблицами

Sub Redesigner()

Dim inpdata As Range, realdata As Range, ns As Worksheet

Dim i&, j&, k&, c&, r&, hc&, hr&

Dim out(), dataArr, hcArr, hrArr

hr = Val(InputBox("Сколько строк с подписями данных сверху?"))

hc = Val(InputBox("Сколько столбцов с подписями данных слева?"))

Set inpdata = Selection

If inpdata.Rows.Count <= hr Or inpdata.Columns.Count <= hc Then Exit Sub

Set realdata = inpdata.Offset(hr, hc).Resize(inpdata.Rows.Count - hr, inpdata.Columns.Count - hc)

dataArr = realdata.Value

If hr Then hrArr = inpdata.Offset(0, hc).Resize(hr, inpdata.Columns.Count - hc).Value

If hc Then hcArr = inpdata.Offset(hr, 0).Resize(inpdata.Rows.Count - hr, hc).Value

ReDim out(1 To Application.CountA(realdata), 1 To hr + hc + 1)

Set ns = Worksheets.Add

For i = 1 To UBound(dataArr, 1)

For j = 1 To UBound(dataArr, 2)

If Not IsEmpty(dataArr(i, j)) Then

k = k + 1

For c = 1 To hc: out(k, c) = hcArr(i, c): Next c

For r = 1 To hr: out(k, c + r - 1) = hrArr(r, j): Next r

out(k, c + r - 1) = dataArr(i, j)

End If

Next j, i

ns.Cells(2, 1).Resize(UBound(out, 1), UBound(out, 2)) = out

End Sub

Редизайн таблиц сэкономит кучу времени, нервов и сил, чтобы в оставшееся время почитать пикабу))

Взято отсюда

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

Сравнение скорости работы функции ВПР

Сравнение проведено Николаем Павловым на таблице в 500.000 и 600 строк. Тестируемые функции:

1. ВПР

2. ВПР с выделением столбцов целиком

3. ИНДЕКС и ПОИСКПОЗ

4. СУММЕСЛИ

5. СУММПРОИЗВ

6. ПРОСМОТР

7. Новая функция ПРОСМОТРХ

8. Запрос Power Query

- Итоговая таблица и выводы

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

Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк).

Задача - подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.


Способ 1. ВПР

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

Здесь участвуют следующие аргументы:

B2 - искомое значение, т.е. название товара, который мы хотим найти в прайс-листе

$G$2:$H$600 - закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс

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

0 или ЛОЖЬ - переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце "B" в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

Время вычисления = 4,3 сек.

Способ 2. ВПР с выделением столбцов целиком

Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ($G$2:$H$600), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

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

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

Время вычисления = 14,5 сек.

Способ 3. ИНДЕКС и ПОИСКПОЗ

Следующей после ВПР ступенью эволюции для многих пользователей Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). Выглядит эта формула так:

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

Здесь:

Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

- Что нужно найти - название товара из B2

- Где мы это ищем - столбец с названиями товаров в прайсе ($G$2:$G$600)

- Режим поиска: 0 - точный, 1 или -1 - приблизительный с округлением в меньшую или большую сторону, соответственно.


Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

- Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).

- Можно извлекать данные, которые находятся левее столбца, где происходит поиск.

По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

Время вычисления = 7,8 сек.

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

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

... то результат получается совсем печальный:

Время вычисления = 28,5 сек.

Способ 4. СУММЕСЛИ

Если нужно найти не текстовые, а именно числовые данные (как в нашем случае - цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF). Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:

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

Здесь:

- Первый аргумент СУММЕСЛИ - это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ($G$2:$G$600).

- Второй аргумент (B2) - что мы ищем.

- Третий аргумент - диапазон ячеек с ценами $H$2:$H$600, числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.


Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле - придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.


В плюсы же можно записать удобство при поиске сразу по нескольким столбцам - для этого идеально подходит более продвинутая версия этой функции - СУММЕСЛИМН (SUMIFS). Скорость вычислений же, при этом, весьма посредственная:

Время вычисления = 12,8 сек.

При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ(G:G; B2; H:H) всё ещё хуже:

Время вычисления = 41,7 сек.

Способ 5. СУММПРОИЗВ

Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов :)


Суть этого метода заключается в использовании функции СУММПРОИЗВ (SUMPRODUCT), изначально предназначенной для поэлементного перемножения нескольких диапазонов с последующим суммированием полученных произведений. В нашем случае, вместо одного из массивов будет выступать условие, а вторым будут цены:

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

Выражение ($G$2:$G$600=B2), по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE) или ЛОЖЬ (FALSE), что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.


Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl+Shift+Enter, т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы - не очень:

Время вычисления = 11,8 сек.

К плюсам же такого подхода можно отнести:

- Совместимость с любыми, самыми древними версиями Excel.

- Возможность задавать сложные условия (и несколько)

- Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака "минус"). СУММЕСЛИМН таким похвастаться не может.


Способ 6. ПРОСМОТР

Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР - это использование функции ПРОСМОТР (LOOKUP). Только не перепутайте её с новой функцией ПРОСМОТРХ (XLOOKUP) - про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

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

Здесь:

- B2 - название груза, которое мы ищем

- $G$2:$G$600 - одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение

- $H$2:$H$600 - такого же размера диапазон, откуда нужно вернуть найденный результат (цену)


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

- Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.

- Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГЕДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

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

При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

Время вычисления = 7,6 сек.

Способ 7. Новая функция ПРОСМОТРХ

Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

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

Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником - функцией ПРОСМОТР (LOOKUP). Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

Время вычисления = 7,6 сек.

Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

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

... то скорость падает до совершенно неприличных уже значений:

Время вычисления = 28,3 сек.

А если на динамических массивах?

Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays). Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:

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

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


Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила - пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

Время вычисления = 1 сек.

Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми - время вычислений не больше 1 секунды! Фантастика.


А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались :(


Что с умными таблицами?

Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и "умными" таблицами. Я имею ввиду те самые "красивые таблицы", в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table) или с помощью сочетания клавиш Ctrl+T.


Если предварительно превратить наши отгрузки и прайс в "умные" (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

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

Здесь:

[@Груз] - ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.

Таблица2 - ссылка на прайс-лист


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


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

Время вычисления = 1 сек.

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


Бонус. Запрос Power Query

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

Итак:

1. Превращаем обе наши таблицы в "умные" с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table) или с помощью сочетания клавиш Ctrl+T.

2. По очереди загружаем таблицы в Power Query с помощью команды Данные - Из таблицы / диапазона (Data - From Table/Range).

3. После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная - Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение (Home - Close&Load - Close&Load to... - Only create connection).

4. После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос - Объединить запросы - Объединить (Get Data / New Query - Merge queries - Merge):

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

5. В открывшемся окне выберем исходные таблицы в выпадающих списках и выделим столбцы, по которым произойдет связывание:

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

После нажатия на ОК мы вернемся в окно Power Query, где увидим нашу таблицу отгрузок с добавленным к ней столбцом, где в каждой ячейке будет лежать фрагмент прайс-листа, соответствующий этому грузу. Развернем вложенные таблицы с помощью кнопки с двойными стрелками в шапке столбца, выбрав нужные нам данные (цены):

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

7. Останется выгрузить готовую таблицу обратно на лист с помощью уже знакомой команды Главная - Закрыть и загрузить (Home - Close&Load).


В отличие от формул, запросы Power Query не обновляются автоматически "на лету", а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh). Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data).

Время обновления = 8,2 сек.

Итоговая таблица и выводы

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

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

Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:

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

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

- Экзотические способы из прошлого типа СУММПРОИЗВ и СУММЕСЛИ - в топку. Они работают очень медленно и, вдобавок, не поддерживают динамические массивы.

- Динамические массивы и умные таблицы - это будущее.

Показать полностью 15
451

Ад Условного Форматирования в Excel

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


Этот пост будет полезен для тех, кто использует условное форматирование и сталкивался с проблемой, когда удалив/переместив/добавив лишь одну строку сбивались правила УФ и появлялся ад. Для примера рассмотрим простую таблицу продаж:

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

Для наглядности к таблице добавлены три правила условного форматирования:

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

Первое правило делает синие гистограммы на столбце с суммами сделок.

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

Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.


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

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

Красная линия между 2 и 3-м марта почему-то исчезла, а наше правило условного форматирования для разделения дат развалилось на два, причем одно из них с ошибкой #ССЫЛКА (т.е. не работает), а другое применяется к двум не смежным диапазонам A2:E8 и A10:E29 (не ко всей таблице!).

Шикарно, правда?!


Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.

Как вы поступите?

Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?

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

Ага, и получите в наследство вот такой бардак в правилах условного форматирования:

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

Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to).


Ну, и на десерт вставим пустую строку в середину таблицы, между 4 и 5-й строчками:

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

В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:

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

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


Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт "тупить".


Как же всё исправить?

Способ 1. Вручную


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


Для этого делаем следующее:

1. Выделяем в нашей таблице все строки кроме первой.

2. Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells).

3. Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.


Способ 2. Макросом


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

1. Жмём сочетание клавиш Alt+F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic).

2. В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.

3. Вставляем в созданный пустой модуль наш макрос:

Sub Fix_СF_Hell()

'создаем ссылки на диапазоны

Set rngAll = Selection

Set rngRow1 = Selection.Rows(1)

Set rngRow2 = Selection.Rows(2)

Set rngRowLast = Selection.Rows(rngAll.Rows.Count)

'удаляем все правила форматирования со всех строк кроме первой

Range(rngRow2, rngRowLast).FormatConditions.Delete

'копируем форматы с первой строки на все остальные

rngRow1.Copy

Range(rngRow1, rngRowLast).PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

End Sub

Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик - Макросы (Developer - Macros) или сочетанием клавиш Alt+F8.

И всё будет хорошо :)


P.S.

Не забудьте сохранить файл в формате с поддержкой макросов (xlsm).

Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).


Здоровья вам и вашим близким) материал взят отсюда

Показать полностью 5
193

Приблизительный поиск с помощью функции ВПР

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

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Если сотрудник проработал в компании меньше года – он не получает ничего. Если проработал от года до двух – получает 10% доплаты. Если от двух до трёх – 15%. Если от трёх до пяти – 25% и т.д. Максимальный бонус в 100% полагается тем, кто работает в компании больше 10 лет.


Выделяем первую ячейку (G2), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК. В появившемся окне вводим аргументы для функции:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Искомое значение – стажа сотрудника, для которого мы определяем бонус.

Таблица – сама таблица бонусов. Не забываем нажать клавишу F4, чтобы сделать ссылку абсолютной.

Номер столбца – порядковый номер столбца в таблице бонусов, откуда мы берем размер доплаты (у нас всего два столбца и номер, очевидно, 2).

Интервальный просмотр – этот аргумент нужно задать равным 1, чтобы Excel производил поиск ближайшего наименьшего числа в первой колонке таблицы. Для точного поиска используется значение 0.


Вот ещё примеры использования функции ВПР:

Найти нужную скидку в таблице скидок, если размер скидки зависит от количества купленного товара или его стоимости (от 1 до 5 шт. – скидки нет, от 6 до 10 шт. – скидка уже 3%, свыше 11 шт. – скидка 5% и т.д.):

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Определить цену билета для пригородной зоны, если известно, до какой станции (на какое расстояние) едет пассажир:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Определить, на какой стадии выполнения проекта мы на данный момент находимся:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 4
41

Wolfram Mathematica в МФТИ

В МФТИ с весеннего семестра 2019 года читается курс по Wolfram Mathematica — это программный пакет, полезный каждому, в первую очередь — учёным и студентам технических вузов.

Стримы проводятся каждую субботу с 18:30 до 20:00.

https://www.youtube.com/watch?v=bt2Z_nC-X7I&amp;list=PLFH0Z3...

179

Виртуальный класс для учителя

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

https://bb.support72.ru


Система развернута на моем сервере, за все заплачено. Пользуйтесь бесплатно.

Виртуальный класс для учителя Коронавирус, Дистанционное обучение, Учитель, Карантин, Обучение, Школа, Класс
211

Помогу освоить базу игрового движка Unreal Engine 4 бесплатно, ощущения после первого урока, дз, полезные ссылки к уроку

Всем спасибо за эфир!

Вы просто бомбически крутые!


Запись эфира доступна по ссылке на плейлист внизу поста.

Также все последующие эфиры будут также появляться там.

Второй стрим планируется на понедельник (20.04.2020), также в 19:30 по Москве.
Тема - "Импорт и настройка своих собственных моделей на движке"


ДЗ на закрепление 1-го урока:

Собрать свой уровень с материалами, освещением, атмосферой и возможностью в нем походить.


Присылайте ДЗ в общий чат с хэштегом #dz1

Мы потом перед следующим стримом поделимся с вами вашими работами в виде коллажа.)

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


Полезные ссылки для расширения и закрепления информации к первому уроку:

Редактор уровней - https://docs.unrealengine.com/en-US/Engine/UI/LevelEditor/in...

Редактор вьюпорта - https://docs.unrealengine.com/en-US/Engine/UI/LevelEditor/Vi...

Режимы работы редактора - https://docs.unrealengine.com/en-US/Engine/UI/LevelEditor/Mo...

Контент браузер - https://docs.unrealengine.com/en-US/Engine/Content/Browser/i...

Панель свойств - https://docs.unrealengine.com/en-US/Engine/UI/LevelEditor/De...

Гайд по начальному запеканию света - https://docs.unrealengine.com/en-US/Engine/Rendering/Lightin...

Настройки запекания Lightmass - https://docs.unrealengine.com/en-US/Engine/Rendering/Lightin...


Ссылка на плейлист с обучающими видео:

https://www.youtube.com/playlist?list=PLonRWR9mNAaSuTjrkWLDh...

Само видео:

До новый встреч в эфире!

Во имя клавиатуры, мыши и интерактивных технологий!

Ctrl+S!

146

Программа курса по 3DS max

Доброго всем дня.

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

На момент написания поста число желающих достигло 430 человек.

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


М 1. Основы 3Д моделирования

1. Знакомство с интерфейсом программы

Создание объектов. Группа объектов Стандартные примитивы, расширенные примитивы. Использование массивов

2. Параметрические модификаторы. Стек модификаторов. Лимиты.

3. Составные объекты. Про-булеан, булеан.

4. Сплайновое моделирование. Работа со сплайнами. Extrude, bevel, lathe

Bevel profile, sweep

5. Основы полигонального моделирования.

Способы комбинированного моделинга.

6. Основы полигонального моделирования.

Способы комбинированного моделинга.

7. Основы работы с материалами и текстурами

Понятие рендеринга, настройка, установка освещения, камеры

Основы визуализации

8. Основы работы с материалами и текстурами

Понятие рендеринга, настройка, установка освещения, камеры

Основы визуализации


М 2. Сложное моделирование

1. Продвинутое полигональное моделирование с использованием Edit mesh, edit poly

2. Продвинутое полигональное моделирование с использованием Edit mesh, edit poly

3. Продвинутое полигональное моделирование с использованием Edit mesh, edit poly

4. Продвинутое полигональное моделирование с использованием Edit mesh, edit poly

5. Продвинутое полигональное моделирование с использованием Edit mesh, edit poly

6. Создание 3Д-студии для моделирования. Моделирование предметов интерьера

7. Создание 3Д-студии для моделирования. Моделирование предметов интерьера

8. Комбинирование техник моделирования

9. Основы текстуринга объектов

10. Основы текстуринга объектов


М 3. Фотореалистичная визуализация и материалы

1. Интерьерная визуализация в Corona render

2. Продвинутый текстуринг и работа с материалами

3. Работа с лайт-микшером

Эффекты и экспозиция

Использование камер

4. Создание и визуализация интерьера

5. Создание и визуализация интерьера

6. Создание и визуализация интерьера

7. Создание и визуализация интерьера

8. Создание и визуализация интерьера


Обучение построим на двухкомпонентной основе:

1) Запись урока

2) Практическая составляющая с обсуждением в Дискорде


Сразу расставим все точки над «i»:

- Поскольку мой проект на данный момент не преследует никакой коммерческой ценности, я не буду отвечать на комментарии с необоснованными претензиями. Я никому ничего не собираюсь доказывать: кто хочет чему-то научится – учится. Я склонен все объяснять иногда максимально простым языком)

- Работа построена по принципу «быстрого начала»: в первых занятиях не будет детального разбора всех нюансов. Все тонкости будут накладываться в процессе обучения.

- Все видеоуроки будут размещаться на 2 недели.

- Время консультация в дискорде – не ограничено, но отвечать могу с задержкой…

- Меня уже обвинили в накрутке подписчиков, поэтому все ссылки на уроки будут публиковаться только в Дискорде. Однако, оставлю за собой право наиболее интересные уроки разместить на площадке Пикабу, если это не будет противоречить правилам.


Ссылку на дискорд: https://discord.gg/bPEmSX.


Для обучения подойдет любая версия 3ds max от 2018 и выше.

Можно скачать пробную версию (на 30 дней) или зарегистрировать ее как студенческую (сроком до 3 лет): https://www.autodesk.com/products/3ds-max/free-trial

По Corona-Render: скачивайте версии от 2,0 до 4,0 с официального сайта (до 45 дней тестового режима): https://corona-renderer.com/download/old

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

Первое занятие пройдет в пятницу!

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

Рисуем в Word/Excel

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

Добрый день. Сразу хочу отметить, что знаю, что есть более удобные программы для рисования, но иногда проще и быстрее нарисовать что-то прям в самом офисе, чем рисовать где-то еще и потом вставлять в документ. Особенно если Вы знаете, что потом этот рисунок нужно будет немного отредактировать. В общем, все понимают, что рисовать Excel или в Word это то еще извращение. Однако инструменты рисования в ворде-экселе вполне себе разнообразные и в меру удобные.  Для начала обращу внимание на небольшие различия между этими двумя редакторами. Это чисто мои наблюдения, так что за точную достоверность не ручаюсь, в плане того что может быть оно совсем не так как я описал. Дело в том, что в Wordесть некая сетка, которая появится, если нажать Разметка страницы -> Выровнять -> отображать сетку. Если сетка отображается к ней можно привязывать объекты (если привязка к невидимой сетке выключена). Иногда это удобно, а иногда нет. Мне сетка помогает выравнивать объекты или наносить точные расстояния. В Excel же такой сетки нет, фигуры привязываются к ячейкам и при изменении размеров ячеек меняются и размеры фигур. Иногда это прям бесит. Так что рисовать в Excelе рекомендую тогда когда уже все остальное форматирование таблицы готово. Все остальное, что касается рисования более менее одинаковое, поэтому дальше будем рассматривать рисование в Word, так как я в нем больше рисовал. Настроить сетку можно в Разметка страницы -> Выровнять  -> параметры сетки

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

Здесь можно настроить привязку объектов между собой, настроить шаг сетки (стрелочками до 1 мм, ручками до 0,1 мм), выбрать начало сетки, отображение линий на экране и привязку к неотражаемой сетке.
Итак, что мы можем рисовать? Заходим в Вставка -> Фигуры

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

все перечислять не буду, думаю и так понятно. Если выделить фигуру у ней есть белые точки, которые меняют ее размер, для большинства фигур есть зеленая точка, которая поворачивает фигуры. Внимание лайфхак! Если перетаскивать размерные точки и зажать Shift то фигура будет изменяться пропорционально, если зажать Ctrl то также будут перетаскиваться точки на противоположной стороне фигуры (как бы симметрично)

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост
Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

При перемещении фигуры если зажать Shift то фигура будет перемещаться по 1 оси, если Ctrlто при отпускании кнопки мыши фигура скопируется.
При повороте нажатие на Shiftповорачивает фигуру «шагами» по 15 градусов.
Для некоторых фигур есть желтые ромбики которые меняют пропорции некоторых элементов фигуры.

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

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

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

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

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

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

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

Есть еще о чем рассказать в этом направлении, про заливки, про прозрачность, про кривые, если эта тема будет интересна расскажу поподробнее.
Я иногда беру какое то фото или картинку, подкладываю и «обвожу» многогранниками и линиями. Вот несколько моих «рисунков» в Word:
Эмблема с Бычком в сомбреро

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

https://drive.google.com/file/d/0B8QwhfN2DgusaGJNQm80bmE0SC1...

Стилизованный автомат AUG

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

https://drive.google.com/file/d/0B8QwhfN2DgusdjFnOFhxcmVxNmR...

проволока для Вейпа

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

https://drive.google.com/file/d/0B8QwhfN2DgusaUJTd0hWc2VBdDR...

Стилизованное изображение девушки

Рисуем в Word/Excel Обучение, Excel, Microsoft Word, Рисунок, Длиннопост

https://drive.google.com/file/d/0B8QwhfN2DgusOHFFM3ktbXZNQW9...

Бонус «клубничка»
https://drive.google.com/file/d/0B8QwhfN2DgusTzZDWGtDQWZ3WXZ...

Показать полностью 11
1546

Сводные таблицы

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

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

Запрос больших объемов данных различными понятными способами.

Подведение промежуточных итогов и вычисление числовых данных.

обобщение данных по категориям и подкатегориям

создание пользовательских вычислений и формул

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

Перемещение строк в столбцы или столбцов в строки ("сведение") для просмотра различных сводок на основе исходных данных.

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

Представление кратких наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.

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

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

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

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

Не обращаем внимания на цены, которые взяты с потолка, и на то, что таблица не такая уж и большая, это пример.
Вот такой тип данных наиболее удобен для дальнейшей обработки сводной таблицей. Если бы имелись объединенные ячейки в заголовки столбцов, либо в строках, как если бы в примере столбец «вид продукта» эти виды были бы объединены, то нам пришлось бы сначала привести таблицу к виду «как положено». Как это сделать побыстрее расскажу отдельно, если кому будут желающие слушать.
Сводную таблицу можно формировать где угодно, хоть в другой книге. Для удобства сформируем ее на отдельном листе. Заходим во вкладку «Вставка», в разделе «Таблицы» нажимаем «Сводная таблица»

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

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

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

Что, же уважаемый Excel, вызов принят! выбираем поля «вид продукта», «количество на складе». Получаем

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

красотень
жмакаем номер склада, немного не то что хотел, перетаскиваем поле № склада из раздела «Итоги» в раздел «Названия столбцов».

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

Вот! то что нужно было!
В общем у нас есть 4 области:
«Фильтры» - это фильтр для всей сводной, данные которые этот фейс-контроль не проходят не попадают в клуб «сводная таблица»
«Названия строк» - здесь то, что у нас будет в строках
«Названия столбцов» - то, что будет в столбцах
«Значения» - те значения что будут в самой таблице.
Поля по этим столбцам можно перетаскивать на ваше усмотрение, поэкспериментируйте. Если поле не нужно его можно выкинуть, перетащив мышкой за пределы таблицы, либо отжав галочку. Не стоит перегружать столбцами Значения и названия столбцов, так вы только запутаете того, кто будет смотреть эту таблицу. Если вам не нравится порядок результатов в столбцах или в строках их можно перетащить в самой таблице.

Классический макет

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

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

Давайте нажмем на правой кнопкой мыши на сводной таблице, выберем «параметры сводной таблицы», вкладка «вывод» , галочка «Классический макет сводной таблицы»

Сводные таблицы Excel, Обучение, Урок, Для чайников, Таблица, Длиннопост

Теперь у нас «Продукт» в отдельном столбце, появился Итог по приправам отдельной строкой, вся таблица немного изменилась и больше похожа на классическую таблицу. Может кому то такой вид больше пригодится, но позже я расскажу Вам как его можно использовать.
Вот ссылка на гугл диск https://drive.google.com/file/d/0B8QwhfN2DgusNDNtRjloN1E5MWV...
На этом давайте пока остановимся, продолжение следует.

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