Закрепляй пока молодой

В детстве нам говорили: "Одень шапку, иначе останешься без денди / барби / блэкджека". Во взрослой жизни ничего не поменялось. Только говорят нам: "Закрепи шапку таблицы".

Что это? Давай разберемся далее.

В Excel есть замечательная функция о которой не знает 50% пользователей - Закрепление областей таблицы. И зря не знают.
Она фиксирует часть таблицы при прокрутке листа.

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

Функция находится на вкладке “Вид” - “Закрепить области”. Внутри 3 варианта.

▫️Закрепить верхнюю строку - фиксирует самую первую строку на листе.
▫️Закрепить первый столбец - фиксирует первый и самый левый столбец на листе.

Всё это неплохо, но не срабатывает если фиксируемая область не в одну, а в две строки. Или 2 столбца.
В этом случае спасает 3-ий вариант.

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

Давай на примере: Ставишь курсор на ячейку C3, нажимаешь “Закрепить области”. Система понимает, что тебе нужно закрепить все строки выше 3-ей строки и все столбцы левее столбца С.

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

Добавь последнюю функцию на панель быстрого доступа. Вот не пожалеешь.

Этот же материал с видео в Telegraph: 2.2 Закрепление областей таблицы
Если не открывается, используй запасную ссылку
Сложность: 2/10
Обратная связь: @excelstudybot

#уровень2

Привет всем в этот прекрасный субботний день!

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

А после отдыха возвращайтесь сюда, на @exstudy. Скоро осень, и это отличное время для самообразования и новых хобби.
Вот и я не отстаю: готовлю потрясающий цикл материалов по формулам excel, где потребуется ваше участие. Стартуем ближе к сентябрю.

Теперь к делам насущным.

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

1️⃣ - Telegraph работает.
2️⃣ - Telegraph иногда не работает. Нужна запаска.
3️⃣ - Обучающий материал в ленте канала - вот это гуд.

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

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

Удаление дубликатов в таблице

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

Материал: 2.3 Удаление дубликатов
Если не открывается, используйте запасную ссылку
Сложность: 2/10
Обратная связь: @excelstudybot

#уровень2

Текст по столбцам

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

И как сказал бы Цезарь: "Разделяй и властвуй".

Материал: 2.4 Текст по столбцам
Если не открывается, используйте запасную ссылку
Сложность: 2/10
Обратная связь: @excelstudybot

#уровень2
#текстпостолбцам

Формулы, функции, инопланетяне

Всем привет!
Вот мы и подошли к самой животрепещущей теме в Excel - формулы и функции. Об этом и будет блок 3.

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


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

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

Let’s go!

Материал: 3.1 Формулы и функции
Если не открывается, используйте запасную ссылку
Сложность: 3/10
Связь: @excelstudybot

#уровень3
#формулы

Простые вычисления и фиксация адресов ячеек

Всем привет!

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

Материал: 3.2 Простые вычисления и фиксация адресов ячеек
Если не открывается, используйте запасную ссылку
Сложность: 3/10
Связь: @excelstudybot

#уровень3
#формулы

Функции суммы, среднего и расчета кол-ва значений

Доброго дня!
Ловите материал об использовании функций суммирования значений, поиска среднего и расчета кол-ва значений. Они же СУММ, СРЗНАЧ и СЧЁТ. Ну и узнаете, что сниться аналитику в страшных снах.

Материал: 3.3 Функции суммирования, среднего и расчета количества значений
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

Всем привет!

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

Поиск минимального значения

Формула поиска минимума указывается так: = МИН (А2:А11)
В скобках как всегда указываем тот диапазон ячеек, где хотим найти минимальное значение. В примере это А2:А11.
После нажатия Enter Excel вернет самое минимальное значение в диапазоне.

Несвязанные диапазоны или значения указываем через точку с запятой “;”
= МИН (А2:А11; С20:С24)
Система будет искать минимум среди А2:А11 и С20:С24

Можно поэкспериментировать сравнить диапазон и числовое значение.
= МИН (А2:А11; 151)
Система будет искать минимум среди А2:А11 и числа 151.

Поиск максимальных значений

Тут Америку не открою - всё то же самое, только функция называется МАКС

= МАКС (А2:А11)

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

Аттеншен: МИН и МАКС работают только с числовыми значениями. Если применить их на текстовых значениях, то вернется 0.

Материал: 3.4 Функции поиска минимума и максимума значений
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

Функция ЕСЛИ

Салют, подписчики!

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

Материал: 3.5 Функция ЕСЛИ
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

Вложимость формул

Всем привет!
Меня немного захлестнула пена дней и я выпал из рабочего процесса на неделю. Прошу меня извинить, теперь я в потоке👨🏼‍💻

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

Ну и сегодня расскажу о простой штуке - о вложимости формул на примере функции ЕСЛИ. О том как прятать греков в троянского коня.

Материал: 3.6 Вложимость формул
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

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

Как утверждал классик: "Краткость - сестра таланта". Не уверен насчет таланта, но то что краткость бережет много сил - подтверждаю.

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

И поэтому сегодня речь зайдет о лаконичном собрате ЕСЛИ - функции ЕСЛИМН.

Сразу обратите внимание, что эта функция доступна с Excel 2016 (по оф. информации Microsoft). Если у вас программа пониже версией, не отчаивайтесь. Позже я расскажу и про лаконичность в Excel 2013.

Начнем сокращать!

Материал: 3.7 Функция ЕСЛИМН
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

​​Тэк-с.
В прошлый раз мы говорили о функции ЕСЛИМН. Она роскошна и великолепна, но есть один жирный минус - функция доступна с Excel 2016.

Что делать если Excel ниже 2016-ой версии?

Не знаю о чем думали разработчики, но в старших версиях аналогов нет. Ну вот вообще. От слова совсем.

Натурально беспредел.

Единственное спасение: научиться правильно работать с вложениями в функцию ЕСЛИ. Давайте посмотрим прошлый пример с баллами и правильно распишем формулу.

Проверка суммы баллов в ячейках А1:А10.
▫️от 5 баллов - отлично.
▫️от 4 до 5 баллов - хорошо.
▫️от 3 до 4 баллов - удовлетворительно.
▫️остальное - неудовлетворительно.

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

= ЕСЛИ(СУММ(A1:A10)>5; “Отлично”;
ЕСЛИ(СУММ(A1:A10)>4; “Хорошо”;
ЕСЛИ(СУММ(A1:A10)>3;“Удовлетворительно”;
”Неудовлетворительно”)))

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

Детали:
▫️В значении если правда указан финальный результат. Нет никаких дополнительных условий - нет возможности запутаться. Рекомендуется для начинающих.
▫️В значении если ложь указывается следующее вложение - т.е. следующая функция ЕСЛИ.
▫️ Чтобы одно условие не конфликтовало с другим, я ставлю сравнения от большего к меньшему. То есть сначала сравниваю всё что больше 5, потом больше 4, потом больше 3.

Если было бы наоборот - сравнение в меньшую сторону, то я бы начал от меньшего к большему. Проверить значения меньше 3, потом меньше 4, потом меньше 5.

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

▫️Чтобы не запутаться в скобках, считаете сколько открытых скобок, и такое же кол-во ставите в конец.
▫️Если тяжело читать формулы, то рекомендую разбивать формулы на несколько строк с помощью комбинации Alt + Enter.

Вот и вся магия.

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

​​Тест

Внесу немного интерактива в обучение.
Какой результат вернет формула на скриншоте?

Не пугайтесь знаку равно, потому что:
▫️>= это знак больше или равно.
▫️<= соответсвенно знак меньше или равно.

Ответ

Всем салют!
Правильный ответ на задание: "Воскресенье".
Сумма в ячейках равна 7,5. Это число удовлетворяет самому первому условию ЕСЛИ.

А теперь разбавлю формулы постом про фильм↓

​​Moneyball

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

Однозначно это здорово: решать проблемы бизнеса с помощью анализа статистических данных.

А что с этим было в начале 2000-х? А что насчет статистики в командных видах спорта?

И тут я посмотрел отличное кино - "Человек, который изменил всё". В английской версии - "Moneyball".
Биографическая драма о том, как статистика, системный подход и вера смогли перевернуть мир бейсбола!

Скажу сразу, я далёк от бейсбола. Для меня хоумран, питчер, кэтчер, база - слова незнакомые.
Но. Это не помешало мне посмотреть этот потрясный фильм. И вам не помешает, если вы не ярый фанат бейсбола.
Фокус даже не в бейсболе.

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

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

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

Везде пишут, что критики называют эту роль Питта едва ли не лучшей. Я солидарен с этим мнением. Чертовски здорово сыграно.

Если описывать фильм 4-мя словами, то получится:
статистика, вера, бейсбол, успех.

Всем советую.
Жмите на мячик, если вам интересны подобные посты.

Функция ПЕРЕКЛЮЧ

Ciao!
Продолжим тему работы с условиями. Сегодня краткий материал о собрате ЕСЛИМН, функции ПЕРЕКЛЮЧ. Тоже доступна с Excel 2016.

Еще пару постов и переходим с вами к ВПРам, ГПРам и прочим интересным возможностям.

Материал: 3.8 Функция ПЕРЕКЛЮЧ
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень3
#формулы

Функции поиска значений

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

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

Сколько бы копий не ломалось и льдов не таяло, а функции поиска значений до сих пор остаются наиболее популярными при работе с Excel.
Если вы откроете какой-нибудь агрегатор вакансий, то с легкостью найдете должности, где в требованиях прописано капсом “УМЕТЬ ПОЛЬЗОВАТЬСЯ ВПР”.
Такие шутейки от HR, конечно, забавляют. Многим работодателям реально важно, чтобы человек умел пользоваться поисковыми функциями. Но капсом могли бы не писать.

Несмотря на популярность, для многих эта тема стала камнем преткновения в обучении Excel. И это не критично.
Вы здесь и вы готовы изучать новое. Это главное.

Что искать, шеф? Ищите женщину.

Когда я говорю о поисковых функциях, то я не имею в виду поиск через Ctrl + F.
Это про другое.

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

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

2 таблицы, 2 разных отдела. И тут являемся мы.

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

Копировать-вставить? Это можно сделать если один или 2 вкладчика. А если их 100 или 10 000? Как понимаете действовать вручную - утопия.
Здесь и приходят на помощь поисковые функции. Они позволяют притянуть информацию из одной таблицы в другую на основе искомого значения.

Этим значением обычно является то, что связывает данные в двух таблицах.
Например ФИО вкладчика. ФИО есть и в первой, и во второй таблице.
Поисковая функция видит в первой таблице Петрова Александра Ивановича, дальше находит его во второй таблице и позволяет взять данные по Петрову. Его дату рождения, адрес проживания и всё, что по нему есть во второй таблице.

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

И как говорится, stay tuned.
Следующий материал будет о, набившем аскомину, ВПР↓

Функция ВПР

Материал о ВПР, о том как с ним работать. Правда ли, что он всемогущ? Спойлер: нет.
После материала будет пару постов-тестов. Обратная связь в бота поддержки приветствуется.

Материал: 4.1 Функция ВПР
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы
#поискзначений
#ВПР

​​Тест по ВПР

Что нужно вписать в пробел формулы на месте курсора, чтобы подтянуть значения из второй таблицы по столбцу "Штук собрано"?

Upd: Только сейчас заметил, что счетчик ответов вводит в заблуждение. Первая цифра на кнопке - ответ. Вторая - кол-во ответов.

#тест

​​Тест по ВПР 2

Какие значения вернет формула ВПР на скриншоте ниже?

#тест

Ответы

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

2. Во втором тесте был подвох. Правильный ответ - "Нет данных".
Вся суть в ограничении ВПР: искомое значение должно быть первым столбцом в выделенной таблице.
У нас же функция собирается искать ФИО в столбце Рабочие часы, конечно ничего не найдет, и вернет "Нет данных".

Чтобы вытянуть простои персонала, нужно выделить таблицу, начиная с ФИО. В качестве номера столбца указать 4.

Ну а сегодня рассмотрим ВПР с интервальным поиском↓
Stay tuned.