Excel на XXL

Генератор случайных фильмов

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

Собрал базу данных из фильмов IMDb и Кинопоиска, добавил учет жанра и десятилетия, выделил 3 категории рейтинга и 4 категории длительности, получилось 6289 фильмов!

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

Пришлось перенести таблицу в google sheets, потому что формула работала не на всех версиях Excel. Зато теперь таблица точно будет работать на любом устройстве!

Если вас заинтересовала таблица и вы хотите её приобрести – пишите в личные сообщения @exceland🦝

Кстати, на видео есть один из моих любимых фильмов🙂

💡 Excel не только для работы!

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

1️⃣ Планировщик привычек – сделай таблицу с днями недели и отмечай ✅ выполненные задачи (например, спорт, чтение, 2 литра воды в день). Можно добавлять цвета, чтобы видеть свой прогресс

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

3️⃣ Генератор случайных решений – составь список блюд, фильмов или идей для отдыха, а затем пусть Excel сам подскажет случайный вариант. Больше не нужно ломать голову!

Какую из этих таблиц вам интереснее всего разобрать подробнее? Пишите в комментариях!

А ещё западные блогеры продают такие таблички по 30 долларов за штуку, поэтому если вам интересно – я могу сделать шаблон, который можно будет приобрести😈

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

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

Допустим, проверяемая ячейка – B2, а проверочный диапазон – A2:A10

Итак, что же с этим делать?
1. Так как нужно искать слово или словосочетание из отдельного столбца внутри текста (а не проверять соответствие всего текста целиком), основной функцией здесь будет либо ПОИСК, либо НАЙТИ. Функция НАЙТИ не подойдет, так как она учитывает регистр искомой буквы, а для нас наоборот важно искать все слова без учета регистра
Получаем формулу =ПОИСК(A2:A10;B2)

1.1 Сразу же проверяем, как эта формула работает. В ячейке B2 введем какое-нибудь словосочетание, например "синий еж". А в диапазон A2:A10 – разные слова, одним из которых будет слово "еж". Видим, что, так как проверяется столбец, результатом этой формулы будет массив – несколько ячеек. При этом во всех ячейках массива, кроме пятой сверху, у нас выскакивает ошибка #ЗНАЧ!, а в пятой сверху - число 7. В слове "синий" 5 букв, плюс пробел – 6, получается "еж" у нас начинается с 7 символа ячейки

2. При большом количестве проверочных строк будет неудобно искать, где у нас есть соответствия в возвращаемом массиве. Гораздо удобней будет иметь 1 ячейку, в которой мы сразу видим, есть совпадения с текстом или нет. Здесь нам пригодится наша функция СУММПРОИЗВ, которая и суммирует значения массива. Но если мы составим формулу =СУММПРОИЗВ(ПОИСК(A2:A10;B2);0), то получим результатом ошибку #ЗНАЧ!, так как сумма ошибки и числа выдает ошибку

3. Поэтому сначала нам надо наложить функцию ЕСЛИОШИБКА. Получаем =ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0). В массиве ошибки заменились нулями. Теперь можем накладывать нашу функцию. Получаем формулу =СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0))

3.1 Функция возвращает число 7. Проверим, что будет, если удалить слово "еж" из столбца А. Функция возвращает 1. Не понимаем, откуда взялась 1, если в столбце нет ячеек с текстом из B2. Проверяем, что будет, если ввести другое слово на место ежа. Функция выдает 0, все в порядке. Пробуем удалить еще какую-нибудь ячейку, снова получаем 1. Так, видимо функция ПОИСК выдает 1 при поиске пустой ячейки в тексте. Идем чатджипитишить – так и есть, оказывается Excel рассматривает каждую строку как содержащую пустое значение в начале, поэтому если у нас в диапазоне А2:А10 будут пустые ячейки, формула будет считать, что у нас всегда есть совпадение

Что с этим делать? Первый вариант: задать ограниченный диапазон и написать примечание для пользователей о том, что из диапазона нельзя удалять ячейки. Но вы сами понимаете, как это неудобно. Второй вариант: у нас уже есть прекрасная функция СУММПРОИЗВ, а неделю назад мы научились с помощью нее проверять соответствие. Добавим в функцию проверку на то, что ячейка в диапазоне не равна 0 (не является пустой). Получаем: =СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0)*(A2:A10<>0))

3.1.1 Снова проверяем. Теперь пробуем удалять разные ячейки из диапазона А2:А10, больше такая проблема не возникает

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

Все это делается с помощью функции ЕСЛИ. Мы помним, что наша формула выдает 0 при отсутствии совпадений и любое другое число в случае, если совпадение есть. Поэтому зададим условие, что при 0 наша функция будет выдавать "" (пустую ячейку), а в остальных случаях – "СОВПАЛО"
Получаем: =ЕСЛИ(СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0)*(A2:A10<>0))=0;"";"СОВПАЛО")

Готово, мы создали формулу, которая ищет совпадения в столбце. Как бы вы её ещё доработали?

Практическое применение СУММПРОИЗВ

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

Ладно, давайте рассмотрим на примерах то, как она работает
___________________________

Пример 1: Сколько яблок продано в регионе Юг?
Допустим, у нас есть таблица с продажами, столбцы:
A: Регион
B: Товар
C: Количество

Для того, чтобы узнать, сколько яблок было продано в регионе Юг, необходимо ввести следующую формулу:
=СУММПРОИЗВ((A2:A10="Юг")*(B2:B10="Яблоки")*(C2:C10))

A2:A10="Юг" – проверяет, относится ли строка к региону Юг
B2:B10="Яблоки" – проверяет, является ли товар яблоком
(A2:A10="Юг")*(B2:B10="Яблоки")*(C2:C10) – суммирует количество только для подходящих строк
___________________________

Пример 2: На какую сумму проданы яблоки в регионе Юг?
Предположим, есть еще один столбец:
D: Цена за единицу

Нужно узнать, на какую сумму проданы яблоки в регионе Юг. Вот формула:
=СУММПРОИЗВ((A2:A10="Юг")*(B2:B10="Яблоки")*(C2:C10)*(D2:D10))

Все просто, к предыдущей формуле добавился один множитель
*(D2:D10) – домножает наше количество на цену
___________________________

Пример 3: Общая сумма с учётом цены товара, упаковки и количества
Теперь рассмотрим более сложный пример. Допустим, есть ещё один столбец:
E: Стоимость упаковки

Нужно посчитать общую сумму (цена товара + стоимость упаковки) только для яблок, проданных в регионе Юг. Вот как это сделать:
=СУММПРОИЗВ((A2:A10="Юг")*(B2:B10="Яблоки")*(C2:C10)*(D2:D10+E2:E10))

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

А вы уже использовали СУММПРОИЗВ для работы с таблицами? Делитесь в комментариях своими примерами или задавайте вопросы, если что-то непонятно!

СУММПРОИЗВ

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

Что это такое?
Функция СУММПРОИЗВ перемножает элементы массивов и складывает результаты. Например, если у вас есть два столбца — количество товаров и их цена — вы можете легко посчитать общую стоимость всех товаров. Функция перемножает их построчно, если у вас в 1 строке информация про яблоки, то функция находит произведение количества яблок на их цену, а затем складывает с таким же произведением для огурцов из второй строки

Формула выглядит так:
=СУММПРОИЗВ(C2:C4;D2:D4)

Где:
C2:C4 — это цены товаров
D2:D4 — это количество
В результате получаем общую стоимость всех товаров
________________________________________

Синтаксис СУММПРОИЗВ: точка с запятой и знак умножения
Синтаксис функции может немного сбивать с толку, давайте разберёмся:

1. Основной синтаксис:
Когда вы вводите аргументы в функцию, они разделяются точкой с запятой (или запятой в английской версии). Например:
=СУММПРОИЗВ(C2:C4;D2:D4)
Здесь Excel перемножает соответствующие элементы массивов C2:C4 и D2:D4, а затем складывает результаты

2. Использование условий:
Если вы хотите добавить условия (например, посчитать только те строки, где регион = "Юг"), то нужно использовать знак умножения (*). Это работает так:
=СУММПРОИЗВ((B2:B4="Юг")*(C2:C4))

Здесь:
• (B2:B4="Юг") — это логическое выражение, которое возвращает ИСТИНА (1) или ЛОЖЬ (0) для каждой строки
• (C2:C4) — это числовой массив
• Знак умножения (*) перемножает логические значения на числа, чтобы оставить только те, которые соответствуют условию
Если регион не Юг, то первый аргумент превращается в 0, и произведение становится равным 0

Когда использовать СУММПРОИЗВ?
• Если нужно посчитать сумму с несколькими условиями
• Если вы хотите избежать сложных формул с массивами
• Если нужно быстро проанализировать данные без лишних шагов
________________________________________

Продолжение следует!
В следующем посте я покажу, как применять СУММПРОИЗВ на практике с реальными примерами. Не пропустите!

ПРОМЕЖУТОЧНЫЕ.ИТОГИ

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

Если мы просто введем функцию =СУММ(B2:B9), то в сумму попадут не только видимые строки, но и те, что скрыты фильтром. Для того, чтобы вывести сумму только видимых значений, подойдет функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Синтаксис функции такой:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(

Первый аргумент – номер функции. Это число от 1 до 11, которое отвечает за нужную функцию. Например, нам нужна сумма отфильтрованных строк, поэтому пишем 9. Если бы нам нужно было среднее значение отфильтрованных строк, мы бы выбрали 1. Помимо 1-11, можно также указывать 101-111, функции будут те же самые, но учитываться будут не только отфильтрованные, но и вручную скрытые строки

Список всех функций с их номерами:
1 – СРЗНАЧ
2 – СЧЁТ
3 – СЧЁТЗ
4 – МАКС
5 – МИН
6 – ПРОИЗВЕД
7 – СТАНДОТКЛОН
8 – СТАНДОТКЛОНП
9 – СУММ
10 – ДИСП
11 – ДИСПР

Итак, для того, чтобы получить сумму видимых значений, мы пишем:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;

Второй аргумент – ссылка1. Это ссылка на диапазон, к которому должна быть применена функция суммирования. В нашем случае это будет B2:B9

Третий и последующие аргументы – дополнительные диапазоны, необязательные аргументы

Получаем формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B9)

Формула возвращает сумму только видимых строк, то есть 50. Если бы мы написали =СУММ(B2:B9), то получили бы 3050. В нашем случае можно было бы написать =СУММЕСЛИ(A2:A9;"Айфон";B2:B9) и тоже получить результат 50. Но функция с условием сложнее, писать ее дольше, тем более, если в таблице несколько фильтров

В последних версиях Excel автосумма в отфильтрованной таблице по умолчанию возвращает функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; а не функцию СУММ

Обратите внимание!
1. Если вы уберете фильтр с таблицы, то результат функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ изменится и станет таким же, как и у функции СУММ. Поэтому будьте внимательны, если используете это значение в дальнейших вычислениях
2. Так как функцию СУММ применили к отфильтрованной таблице, она не учитывает все строки исходной. Строка 10 здесь не используется в сумме, а сумма всех значений должна составлять 4050, не 3050

Такие вот итоги года у этого канала :)

Благодарен каждому из вас за то, что вы подписаны

С наступающим Новым годом!

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

Наверняка вы сталкивались с тем, что при поиске картинки без фона вам попадаются png-обманки. Картинки, притворяющиеся png, но имеющие фон с квадратами.

Для того, чтобы упростить поиск картинок без фона, можно сделать так:
Нажимаете "Инструменты"
В появившемся меню – "Цвет"
Выбираете "Прозрачный"

Готово, теперь у вас картинка без фона!

А я напоминаю, что сегодня есть последняя возможность приобрести мой замечательный курс по экселю со скидкой в 15%! По промокоду XL2025 на сайте. А лучше по номеру телефона, можете написать в лс:)

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

Сегодня хочу немного подробней рассказать про свой курс

После прохождения обучения вы станете профессионалом в работе с Excel!

🔥Хорошее знание программы позволит вам:
• автоматизировать деятельность и забыть про ручную работу в таблицах
• быстрее собирать отчеты с большим количеством данных
• улучшить навыки анализа данных и прогнозирования
• ускорить свою работу примерно на 30%

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

В курсе есть следующие модули:
1. Настройка программы,
2. Горячие клавиши,
3. Форматирование,
4. Масштабирование и печать,
5. Формулы,
6. Диаграммы и графики.

🎯Защита от прокрастинации: каждый урок длится менее 10 минут, поэтому вы точно найдете время на изучение экселя. Уроки в записи, вы сами выбираете темп обучения

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

❗️В преддверии нового года я решил сделать скидку только для подписчиков этого канала:
По промокоду XL2025 вы получите скидку в 15% на любой из тарифов, достаточно ввести промокод на сайте excel-xxl.ru! Скидка действует только до 27 декабря. Доступно 5 промокодов.

⚡Цены со скидкой:
Тариф без обратной связи – 4.240 руб. (скидка 750 руб.)
Тариф с обратной связью – 5.940 руб. (скидка 1050 руб.)
Тариф с полным ведением – 16.990 руб. (скидка 3000 руб.)

Приобрести курс можно через сайт, либо переводом на Сбербанк или Т-банк по номеру телефона

А если у вас нет карты российского банка, можно сделать перевод на карту банков Казахстана

Чтобы закрепить скидку за собой, пишите мне в личные сообщения в телеграме @exceland

Ведение этого канала стало для меня важной частью 2024 года

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

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

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

А если вы хотите начать его с самосовершенствования, то у меня есть для вас небольшой подарок!

Это скидка на мой курс по экселю в размере 15%, которая действует до 27 декабря.
Для того, чтобы активировать эту скидку, введите промокод XL2025 на сайте excel-xxl.ru
Либо напишите мне в личные сообщения @exceland (могу отвечать с задержками, много встреч по основной работе)

С наступающим Новым Годом, дорогие друзья!
(завтра выложу еще один пост про скидку, прогревы всё-таки надо делать)

Автосумма

Допустим, вам нужно найти сумму значений во всем столбце

Можно вбить вручную функцию СУММ, а затем выделить диапазон

А можно не заморачиваться и одновременно нажать клавиши Alt =
У вас автоматически появится функция СУММ с уже выделенным диапазоном суммирования!

Для суммы по столбцу сочетание клавиш необходимо нажимать в ячейке ниже ячейки с данными.
А для суммы по строке – в ячейке правее ячейки с данными

Упрощаем работу с функциями СУММЕСЛИ и СУММЕСЛИМН

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

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

Теперь, когда мы вводим функцию СУММЕСЛИ, нам не нужно прописывать во втором аргументе критерий текстом, достаточно просто указать ссылку на ячейку из столбца D
Вместо =СУММЕСЛИ(A:A;"Продажа";B:B) получаем =СУММЕСЛИ(A:A;D3;B:B)

Почему это проще? Теперь формулу можно просто протянуть вниз, и не вписывать критерий каждый раз заново! Если у вас несколько строк с условием, это сэкономит время и нервы

Функция СУММЕСЛИМН

Я решил отдохнуть от ведения канала после того, как выложил трекер подарков, но пора возвращаться :) Продолжим изучение функций суммирования!

В прошлый раз я рассказал вам о функции СУММЕСЛИ, которая позволяет суммировать значения, которые удовлетворяют определенному условию. Функция СУММЕСЛИМН позволяет суммировать значения, которые удовлетворяют двум и более условиям!

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

Первый аргумент – диапазон суммирования. Если суммировать надо продажи – мы должны указать диапазон, содержащий информацию о продажах, то есть столбец «сумма продаж» D2:D10.

Второй аргумент – диапазон первого условия. Это тот диапазон ячеек, который будет проверяться на соответствие условию. Например, здесь это будет столбец «канал продажи», то есть В2:В10.

Третий аргумент – первое условие. Критерий, по которому функция определяет, какие ячейки суммировать. Условие может быть указано как число, текст, ссылка на ячейку или функция. В нашем случае можно написать “онлайн”, либо $В$5.

Четвертый аргумент – диапазон второго условия

Пятый аргумент – второе условие

И так далее. Всего можно ввести 127 условий. Сомневаюсь, что понадобится так много, но мало ли!

Для того, чтобы вывести сумму Айфонов, проданных онлайн, получим функцию:
=СУММЕСЛИМН(D2:D10;A2:A10;"Айфон";B2:B10;"Онлайн")
Сумму черных айфонов, проданных онлайн:
=СУММЕСЛИМН(D2:D10;A2:A10;"Айфон";B2:B10;"Онлайн";C2:C10;"Черный")

Функцию СУММЕСЛИМН можно использовать и с одним условием, тогда она будет работать, как функция СУММЕСЛИ. Например, получим сумму айфонов:
=СУММЕСЛИМН(D2:D10;A2:A10;"Айфон")

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

Новогодний трекер подарков

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

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

В США и Европе такие таблички продают за 10 долларов или евро (1030 рублей или 5210 тенге!), но мы с вами не прогнившие капиталисты, поэтому этот файл – мой подарок вам!

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

СУММЕСЛИ

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

Для этого пишем
=СУММЕСЛИ(

Первый аргумент - диапазон. Это тот диапазон ячеек, который будет проверяться на соответствие условию. Например, здесь это будет столбец «канал продажи», то есть В2:В10.
Если бы нашим условием было сложить только продажи выше 1000, проверялся бы сам столбец «сумма продаж», С2:С10

Второй аргумент - условие. Критерий, по которому функция определяет, какие ячейки суммировать. Условие может быть указано как число, текст, ссылка на ячейку или функция. В нашем случае можно написать “онлайн”, либо $В$5.
Если бы нам были нужны значения больше 1000, мы бы написали ">1000"

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

Третий аргумент - диапазон суммирования. Если суммировать надо продажи онлайн - мы должны указать диапазон, содержащий информацию о продажах, то есть столбец «сумма продаж» С2:С10.

Этот аргумент не обязателен в случае, если диапазон суммирования уже указан в 1 аргументе. При суммировании продаж свыше 1000, мы уже ввели в формулу диапазон продаж С2:С10 и нет смысла вводить его снова.

Итого получаем:
=СУММЕСЛИ(В2:В10;"онлайн";С2:С10)
Эта формула вернет нам сумму продаж через онлайн канал
Также можно записать как:
=СУММЕСЛИ(B2:B10;$B$5;C2:C10)

=СУММЕСЛИ(С2:С10;">1000")
Эта формула вернет нам сумму продаж выше 1000

Как работает функция СУММЕСЛИ на пальцах:
Формула ищет в диапазоне условия слово "Онлайн". Оно есть в ячейке В5 , поэтому дальше формула в диапазоне суммирования ищет 5 строку. Это ячейка С5, формула добавляет в сумму ее значение. В ячейках В7, В8 и В10 тоже указано «онлайн», поэтому в сумму попадают и С7, С8 и С10. Итого у нас сумма ячеек С5, С7, С8 и С10

Навигация по каналу

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

🔎 Блок о функциональных клавишах - несколько постов с подробным разбором сочетаний клавиш F1-F12

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

🍺 Пивной график - подробно разбираем создание необычного живого графика

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

➕ Функции суммы - несколько функций, которые позволяют суммировать значения разными способами
_________________________________
• ВПР
• Автоподбор ширины столбца и высоты строки
• Удаление лишних пробелов
• Быстрая активация фильтра
• Быстрая фильтрация по выбранному значению
• Добавление срезов для фильтрации
• ExcelGPT
• Удаление дубликатов
• Ограничение ввода данных
• Что такое XML
• Мини-гистограммы в ячейках
• Расчет числа дней между 2 датами
• Подсчет рабочих дней между 2 датами
• Определение даты через N число месяцев
• Скрыть строку
• Выделить лист
• Сортировка месяцев
• Поиск последнего значения в списке
_________________________________
Ну и самое главное:
🎯Лучший курс по экселю - вы освоите программу на профессиональном уровне!

Хочу поделиться с вами папкой с каналами по экономической тематике! Здесь информация об экономике, графиках, ну и конечно же экселе :)

Больше не нужно искать тематические каналы и по отдельности на них подписываться — мы сделали это за вас 👌

Вам остается только перейти по ссылке, сохранить папку себе и регулярно получать полезные ресурсы и знания из сферы
«Экономика»

https://t.me/addlist/x9rh7Wc5K2wxMzUy

Сегодня начнем блок по функциям суммирования

Первая функция здесь - СУММ, она позволяет складывать значения

Работает функция так: вводите =СУММ( и выбираете необходимые ячейки для суммирования. Чтобы их выбрать нажмите левой клавишей мыши на первую ячейку диапазона, а затем, держа клавишу зажатой, ведите мышь в сторону последней ячейки диапазона. Способ для крутых: с помощью стрелок на клавиатуре встаньте на первую ячейку диапазона, затем зажмите клавишу Shift и стрелками двигайтесь до последней ячейки диапазона

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

Чаще всего используется сложение диапазонов, так как гораздо быстрее выделить диапазон В2:В6, чем вводить каждую ячейку по отдельности

И уж тем более не рекомендуется использовать в формуле значения: =СУММ(1308;2848;1440;8280;902). Может возникнуть опечатка, либо вы можете забыть изменить это значение в будущем, тогда формула будет работать некорректно

Оптимальный вариант: =СУММ(B2:B6). Формула будет работать правильно в случае корректировки данных, а также учтет изменения, если вы добавите новую строку с товаром

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

Мой курс «Excel на XXL» позволит вам стать профессионалом в экселе всего за месяц! (При желании курс можно пройти и за пару дней, но давайте не будем себе врать)

🔹В нем есть следующие модули:
1. Настройка программы,
2. Горячие клавиши,
3. Форматирование,
4. Масштабирование и печать,
5. Формулы,
6. Диаграммы и графики.

🔸Всего в курсе 38 уроков. Они содержат выжимку полезной информации, без воды! Каждый длится менее 10 минут, поэтому вы точно найдете время на изучение экселя. Уроки в записи, вы можете посмотреть их в любое удобное время

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

◻️Вы станете мастером экселя! Больше не нужно задерживаться на работе из-за проблем с таблицами. У вас пропадет волнение перед вопросом «расскажите о своих навыках в Excel» на собеседовании. Эксель станет вашей сильной стороной, вы будете знать его от и до!

✔️В курсе 3 тарифа:
- тариф без обратной связи стоит 4.990 руб.,
- тариф с обратной связью - 6.990 руб.,
- тариф с полным ведением - 19.990 руб.

✅Приобрести курс можно через сайт https://excel-xxl.ru, либо переводом на Сбербанк или Т-банк по номеру телефона (при этом вы получите дополнительную скидку в 2%!)

А если у вас нет карты российского банка, можно сделать перевод на карту банков Казахстана (цены в тенге продублирую)

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

Вчера подписывал договор аренды квартиры, поэтому не было времени на написание поста :(

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

Сначала выделим строку заголовков
Ctrl Shift ➡️ (стрелка вправо)

Alt Я

У кнопки выбора цвета фона ячейки опять видим сочетание буквы и цифры. Нажимаем последовательно Р 1

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

Итого:
Alt Я Р 1

В английской версии: Alt H H

Что бы вы ещё добавили в таблицу? :)
Например, шрифт заголовков хорошо бы сделать жирным

Чтобы таблица стала таблицей, ей нужны границы

Ctrl A

Alt Я

Опять видим, что у границ 2 буквы - ГГ

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

Итого:
Alt Я Г Г С

В Английской версии: Alt H B A

Таблица почти готова, остался последний шаг

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

Для этого:
Ctrl A

Alt Я
Видим, что у «формата» две буквы - ТФ. То же самое, что и в прошлый раз: просто жмем сначала Т, затем Ф. Теперь перед нами появилось меню. У каждого пункта есть буквы, нам нужен автоподбор ширины столбца. Нажимаем Б. Так как здесь 2 пункта меню с клавишей Б (тот, кто это придумал, должен быть уволен), нам нужно нажать Enter.
Итого:
Alt Я Т Ф Б Enter

В английской версии опять гораздо проще:
Alt H O I

Готово, таблица стала гораздо более читабельной

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

Для этого уберем переносы строк

Сначала выделяем всю таблицу через Ctrl A

Затем последовательно жмем наши стандартные Alt Я

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

В английской версии проще - Alt H W

Готово, уже стало похоже на нормальную таблицу! Что делаем следующим шагом?

Вчера мы отформатировали числа в таблице, но значение рентабельности везде стало равно нулю

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

Для этого сначала нужно выделить все значения рентабельности. Это делается так: встаем на верхнюю ячейку, а затем нажимаем одновременно Ctrl Shift ⬇️ (это стрелка вниз)

Теперь по очереди нажимаем клавиши:
Alt
Я
Й - для добавления процентного формата

Если у вас Excel на английском, то Alt H P

Готово, теперь мы можем увидеть значения рентабельности

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

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

Для этого нужно по очереди нажать клавиши:
Alt - для включения подсказок клавиш
Я - для того, чтобы перейти на вкладку «Главная» на ленте. Появятся подсказки клавиш на этой вкладке
Д - для добавления разрядности

Если у вас Excel на английском, то нажать нужно Alt H K

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

Для этого нажмем:
Alt
Я
9 - для уменьшения разрядности

В версии Excel на английском - Alt H 9

Так как нуля после запятой два, то и повторить эту комбинацию необходимо два раза

Готово, теперь числа выручки и чистой прибыли выглядят более презентабельно

Подсказки клавиш на ленте меню

Если вы работали в Excel или Word, то наверняка замечали эти буквы, которые появляются на ленте. Возможно, вас, как и меня раньше, они раздражали: вроде бы только что спокойно вводил данные, а тут начали появляться какие-то окна меню.

Так вот, это сделано специально! Подсказки появляются при нажатии клавиши Alt. А использование этих клавиш - самый полезный навык для пользователя экселя.

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

К примеру, чтобы открыть пункт Data на приложенном скриншоте, необходимо нажать Alt, а затем клавишу A

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

Если было интересно, ставьте 🔥

Клавиша F12

Одиночное нажатие F12 — отображение окна Сохранить как. Я пользуюсь этой функцией чаще, чем просто "Сохранить" с помощью Ctrl S. Объясню почему: когда вы сохраняете файл через "Сохранить как" - появляется отдельный новый файл, при этом старая версия тоже остается. Это удобно, если вам нужно поэкспериментировать с таблицами - вы всегда сможете вернуться к исходному файлу.

Ctrl F12 вызывает окно "Открытие документа". В нем можно выбрать файл из папки и открыть его.

Shift F12 - сохранение файла (как Ctrl S)

Продолжаем серию постов про функциональные клавиши!

Клавиша F11

Одиночное нажатие F11 — cоздание диаграммы на отдельном листе (для этого вы должны встать в одну из ячеек таблицы, либо выделить всю таблицу)

Сочетание Shift F11 вставляет в книгу новый лист

Сочетание Alt F11 открывает редактор Microsoft Visual Basic, в котором можно создать макрос

Как сделать живой график с картинкой?

На видео разобрал основные ошибки при создании такого графика, а ниже напишу пошаговый план)

1. Создаем гистограмму. При этом нужно создать не обычную гистограмму, а выбрать "Другие гистограммы" - "Гистограмма с группировкой" - затем выбрать вторую. То же самое можно сделать, если гистограмму создать не по всей таблице, а по одной ее строке, а затем растянуть (так я делал в видео в инстаграме)
2. Удаляем все лишние элементы диаграммы - ось, линии сетки, легенду и так далее.
3. Копируем нашу картинку и вставляем на график. При этом выделить надо именно столбец диаграммы, а не всю, иначе картинка вставится криво (показал в прикрепленном видео). При этом картинку желательно обрезать по контуру, иначе на графике будут лишние детали с фона)
4. Нажав на столбец правой кнопкой мыши, выбираем "Формат ряда данных", затем в пункте меню "Заливка и границы" выбираем "Размножить в масштабе". Теперь картинка вместо того, чтобы сжиматься, будет обрезаться.
5. В "Формате ряда данных" выбираем пункт меню "Параметры ряда", и устанавливаем "Перекрытие рядов" равным 100%.

Готово! Теперь при изменении процента, картинка переднего бокала с пивом будет обрезаться, а заднего - оставаться неизменной.

Выложил в инстаграме видео о том, как сделать такой график:

https://www.instagram.com/reel/DBno0mEND96/?igsh=MXN6bHo1N2t2NXdxOA==

Если честно, просто не успеваю сделать более подробный пост в этом канале сегодня, обязательно выложу завтра!! А пока буду рад, если поддержите реакциями🫶🏻🔥

Клавиша F10

Одиночное нажатие F10 — включение или отключение подсказок клавиш на ленте (аналогично с действием клавиши Alt). Использование этих подсказок - один из главных навыков в экселе. Если интересно узнать про них подробнее - напишите в комментариях, сделаю пост)

Сочетание Shift F10 отображает контекстное меню для выбранного элемента (аналогично нажатию правой кнопки мыши)

Сочетание Сtrl F10 разворачивает окно на весь экран, либо наоборот восстанавливает исходный размер программы

Клавиша F9

Клавиша F9 пересчитывает формулы в текущей книге Excel. Программа обновит формулы на всех листах открытой программы. Например, при нажатии на клавишу F9 обновляется функция СЛЧИС в ячейках.

Сочетание клавиш Shift F9 пересчитает формулы только на открытом листе программы

А Alt Ctrl F9 пересчитывает формулы на всех листах всех открытых книг

Сочетание клавиш CtrlF9 сворачивает программу

Интересно, что если перейти в режим редактирования ячейки и нажать клавишу F9, то ячейка с формулой заменяется значением этой формулы

Клавиша F8

Одиночное нажатие F8 — переход в режим выделения и выход из него. Теперь после того, как вы будете нажимать на стрелки на клавиатуре, у вас будут выделяться смежные ячейки. Лично мне проще делать это с помощью зажатой клавиши Shift :)

Сочетание Shift F8 позволяет при помощи стрелок добавить к выделению несмежные ячейки или диапазон. Опять же, лично мне проще это делать левой клавишей мыши с зажатым Ctrl, но идеальный специалист по экселю работает без мыши !

Сочетание Ctrl F8 выполняет команду Размер, если окно не развернуто на весь экран. С помощью стрелок на клавиатуре можно менять размер окна. Попробуйте сами, интересная штука

Сочетание Alt F8 выводит на экран окно Макрос

Продолжаем серию постов про функциональные клавиши!

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

Ctrl F6 – переключение между разными окнами программы

F7 – отображение окна Правописание, позволяющего проверить орфографию

Сочетание Ctrl F7 выполняет команду Переместить, если окно книги не развернуто. Перевожу на русский: если окно программы у вас не открыто на весь экран, то при нажатии Ctrl F7 курсор мыши телепортируется в верхнюю часть программы, а если вы нажмете ЛКМ и начнете двигать мышью в сторону, окно будет перемещаться по экрану.

Клавиша F5

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

А при нажатии на кнопку Выделить откроется окно выделения группы ячеек, где вы можете выбрать ячейки по определенному критерию. Это может быть полезно, например, при удалении пустых строк (как тут: https://www.tiktok.com/@excel_rus/video/6967393730772929793)

Окно Переход помимо клавиши F5 можно вызвать сочетанием клавиш Ctrl G, что лично мне кажется более удобным)

Сочетание Ctrl F5 восстанавливает размер выбранного окна книги (вместо того, чтобы быть открытым на весь экран, окно уменьшится)

Сочетание Shift F5 открывает окно "Найти и заменить", которое куда проще открыть через сочетание клавиш Ctrl F или Ctrl H🙂‍↕️

Клавиша F4

Одиночное нажатие F4 — повторение последней команды или действия

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

Сочетание Ctrl F4 закрывает окно программы (если открыты несколько файлов Excel, закрывается 1)

Сочетание Alt F4 закрывает программу (если открыты несколько файлов Excel, закрываются все)

Клавиша F2

Нажатие клавиши F2 позволяет «погрузиться внутрь» активной ячейки. При этом появляется курсор. Это полезно, если вам нужно отредактировать содержимое ячейки, и вы не хотите заполнять все данные с нуля

Сочетание Shift F2 позволяет добавить или изменить комментарии к ячейке

Сочетание Ctrl F2 отображает область предварительного просмотра на вкладке Печать

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

Нажатие клавиши F1 вызывает окно справки

Сочетание клавиш Ctrl F1 отображает или скрывает ленту панели инструментов

Сочетание Alt F1 создает встроенную диаграмму

Сочетание Shift Alt F1 добавляет в книгу новый лист

Как быстро отфильтровать значения

Правой кнопкой мыши кликнуть по ячейке с нужным значением

В меню выбрать пункт «Фильтр» — «Фильтр по значению выделенной ячейки».

Как выделить весь лист в экселе?

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

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

Если же нажать Ctrl A повторно, то будут выделены все ячейки листа

Как быстро скрыть строку в экселе?

Для этого можно использовать сочетание клавиш

Ctrl 9 — cкрыть строку, в которой находится активная ячейка

Ctrl Shift 9 — наоборот, показать скрытую строку

кто ставит пальцы вниз😡