файлы эксель для тренировки
MS Excel Сводные таблицы + Практический файл
Курс MS Excel. Продвинутый уровень. На этом уроке создаем СВОДНЫЕ ТАБЛИЦЫ! Не смотря на то, что все кажется легко, рекомендуется скачать ПРАКТИЧЕСКИЙ ФАЙЛ и проделать те же шаги (а может и больше) с тестовыми данными. Обязательно попрактикуйтесь!
Практический файл можно скачать здесь.
Еще один урок про настройку сводных таблиц:
Офисные будни
2.8K поста 12.9K подписчиков
Правила сообщества
— добавляйте посты связанные с тематикой сообщества;
-делитесь опытом организации жизни в офисе и проживания на работе;
-делитесь управленческим опытом;
— не нарушайте правила Pikabu и чтите закон.
Большое спасибо за файл для тренировки и начала освоения сводных таблиц!
М-м-м. Сортировка. Итоги. Вычисляемые поля. ТС, ты не раскрыл и 20% сводных таблиц.
Отдельный батхерт вызывает ютуб-формат. У тебя проблемы с изложением своих мыслей, что ты не можешь всё обычным текстом с картинками изъяснить?
Позитивное «Подписывайтесь, пожалуйста, на канал. «
Основы Excel. Форматирование данных
В этом видеоуроке автор разбирает, что представляет собой форматирование в Excel, и как им следует грамотно пользоваться.
Сводные таблицы
Продолжаем совершенствоваться в фехтовании данными любимого Excel. Сегодня, по многочисленным заявкам, мы капнем на пол штыка такую удобную штуку как сводные таблицы. Почему так не глубоко? Да потому что тема большая и ее придется растянуть на несколько постов. Что же это такое сводная таблица? Справка говорит нам, какие задачи решают сводные таблицы:
Запрос больших объемов данных различными понятными способами.
Подведение промежуточных итогов и вычисление числовых данных.
обобщение данных по категориям и подкатегориям
создание пользовательских вычислений и формул
Развертывание и свертывание уровней представления данных для выделения результатов и выполнение тщательного анализа сводных данных по интересующим вопросам.
Перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных.
Фильтрация, сортировка, группировка и условное форматирование наиболее важных подмножеств данных для концентрации внимания на нужных сведениях.
Представление кратких наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
В общем, какое то волшебство. Вполне большой и удобный перечень функционала для анализа и наглядного представления информации, не правда ли.
Также хочу отметить, что возможно вы знаете более удобные инструменты работы с базами данных, но мы имеем то, что имеем, то есть Excel, который тоже довольно таки удобен.
Крайне удобно работать сводной таблицей с большими объемами данных, представленных в формате простейшей таблички без всяких там объединенных ячеек (очень не люблю объединенные ячейки), а также лучше всего, чтобы все ячейки таблицы были заполнены данными. Дело в том, что хотя для наших глаз объединенные ячейки относятся к нескольким столбцам, Excel видит их так, как бы они выглядели, если снять объединение, то есть вся информация пишется в первой ячейке, а остальные пустые.
Вот эти пустые ячейки сводная таблица и не любит, особенно в заголовках столбцов. Первое правило сводной таблицы – (никому не говорить о сводной таблице) все столбцы исходных данных, из которых мы формируем сводную таблицу, должны быть озаглавлены, и озаглавлены понятно и по-разному.
Итак, давайте разбираться сначала и на примере. Есть у нас какой-то большой объем данных, для примера я накидал такую таблицу.
Не обращаем внимания на цены, которые взяты с потолка, и на то, что таблица не такая уж и большая, это пример.
Вот такой тип данных наиболее удобен для дальнейшей обработки сводной таблицей. Если бы имелись объединенные ячейки в заголовки столбцов, либо в строках, как если бы в примере столбец «вид продукта» эти виды были бы объединены, то нам пришлось бы сначала привести таблицу к виду «как положено». Как это сделать побыстрее расскажу отдельно, если кому будут желающие слушать.
Сводную таблицу можно формировать где угодно, хоть в другой книге. Для удобства сформируем ее на отдельном листе. Заходим во вкладку «Вставка», в разделе «Таблицы» нажимаем «Сводная таблица»
Если в дальнейшем планируется добавлять данные в нашу базу то лучше диапазон выбрать до конца листа, тогда нужно будет «обновить» сводную чтобы данные добавились. Единственное нужно будет в фильтре сводной выкинуть эти пустые ячейки. Также если в таблице есть промежуточные и конечные итоги их стоит убрать, чтобы сводная таблица их не учитывала. На крайняк их тоже можно будет выкинуть в фильтре сводной таблицы. У нас появилась такая картина:
Что, же уважаемый Excel, вызов принят! выбираем поля «вид продукта», «количество на складе». Получаем
красотень
жмакаем номер склада, немного не то что хотел, перетаскиваем поле № склада из раздела «Итоги» в раздел «Названия столбцов».
Если задействовать более 2 полей в сводной таблице в названиях строк появится подкатегории, и можно будет сворачивать и разворачивать разделы
Теперь у нас «Продукт» в отдельном столбце, появился Итог по приправам отдельной строкой, вся таблица немного изменилась и больше похожа на классическую таблицу. Может кому то такой вид больше пригодится, но позже я расскажу Вам как его можно использовать.
Вот ссылка на гугл диск https://drive.google.com/file/d/0B8QwhfN2DgusNDNtRjloN1E5MWV.
На этом давайте пока остановимся, продолжение следует.
Волшебная формула
Всем привет. Продолжаем постигать Excel. Если Вы не знакомы с моими предыдущими уроками, то советую ознакомиться. Сегодня я хочу продолжить тему, поднятую в предыдущем посте Как я делаю шаблоны о составлении шаблонных форм. Однажды мне потребовалось сделать такой лист, содержимое которого зависит от названия листа. Я нашел одну чудесную формулу и хочу ею с Вами поделиться. Также дополнительно я хочу показать, как в простом листе запутать формулы так, чтобы потом в них было практически невозможно разобраться. Спросите, зачем это нужно? Иногда бывает полезно – скидываешь человеку таблицу и если в ней нужно что-то переделать, то он опять обращается к Вам, только не забудьте сохранить у себя исходник. Но обо всем по порядку.
Сначала немного теории.
Рассмотрим работу функции ЯЧЕЙКА (тип_сведений;[ссылка]), она имеет 2 аргумента: тип сведений и ссылку на ячейку соответственно. Сейчас нас интересует тип «имяфайла», выбираем его и ссылаемся на саму ячейку, где мы это пишем (остальные типы вам на самостоятельное изучение). Для корректной работы этой функции необходимо чтобы файл был сохранен где-нибудь. Итак, в имени файла мы видим непосредственно имя файла и после него имя листа, на котором прописываем эту функцию (точнее на лист, куда ссылаемся ссылкой). Давайте попробуем вырезать то, что нам нужно, а именно имя листа. Для этого нам нужно знать длину текста в ячейке с именем файла, получаем ее с помощью функции ДЛСТР(),затем нам нужно найти позицию закрывающейся квадратной скобки, которая ограничивает имя файла при помощи функции ПОИСК, разница этих чисел и будет длиной имени листа. Отрезаем справа от первоначальной ячейки эту длину и получаем ячейку с именем файла. В результате у нас должно получиться нечто подобное.
Формулы при этом выглядят так:
Пробуем поменять имя листа и видим что содержимое нашей ячейки меняется. Для чего же это можно использовать. Давайте сделаем шаблон, в который будет подставляться данные по средствам функции ВПР, искомым значением которого будет эта наша ячейка с именем листа, а столбцы, из которых будет подставляться значения будут у нас разные и завесить от необходимой информации
подтягивать данные будем из такой таблицы:
назавем этот лист «База».
Здесь рассмотрим еще одну полезную функцию Excel, которая называется именованные области (или как-то так). Выделяем область к которой нам нужно будет часто обращаться, в нашей таблице это столбцы от А до G на листе «База», и жмакаем на строку которая расположена слева от значка формулы
теперь пишем там название нашей области «база».
Все, теперь в формулах можно вместо ссылки на область эту область писать просто база! Пишем в лист с волшебной формулой следующие формулы:
помним что в ячейке D5 у нас название листа, которое будет искаться в первом столбце базы, то есть для создания карточки сотрудника нам нужно будет назвать лист как фамилию нужного нам сотрудника. Я решил не просто тупо подставлять значения из таблицы (это же скучно), а склеить ФИО, вместо дня рождения выводить сколько полных лет, а вместо даты приема на работу стаж в годах. Но это просто в образовательных целях. Можно и эти данные в формуле прописать в базе.
Теперь проверим, назовём лист «Иванов»:
Во как!
на основании такого листочка и нехитрого макроса я на пример делал много отчетов с разбивкой по пятидневкам или по дням из месячной базы. Но создание макросов я намеренно не рассматриваю в своих статьях (сам только учусь их писать)
Теперь перейдем ко второй части нашего балета. То есть запутаем эту формулу так, чтобы сами не могли ничего разобрать. Смотрите, у нас в ячейке D1 прописана формула, ссылающаяся в принципе на любую ячейку этого листа, для удобства поменяем чтобы она ссылалась на ячейку A1. Теперь посмотрим, кто как у нас на этом листе ссылается: ставим курсор на A1, идем в пункт формулы, нажимаем «зависимые ячейки» несколько раз, получаем:
видим что все формулы зависят от А1. Теперь идем в D5, копируем все что там после знака «=», нажимаем поиск с заменой (Ctrl+H), пишем Найти D5, заменить на то что скопировали ПРАВСИМВ(D1;D4). Заменяем везде, затем идем в D4, копируем все что после знака «=»…. и так повторяем пока не придем в ячейку А1. После этого ячейки D1-D5 можно удалить, они у нас были как бы промежуточные. В итоге лист у нас работает также как и раньше, а что же в формулах? Заглянем в B4: «@=ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;1;0)&» «&(ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;2;0)&» «&ВПР(ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1));база;3;0))»
жуть какая! ничего не разберешь. но мы то с вами знаем что формула ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1);1)) просто показывает нам имя листа. И это у нас относительно простая зависимость была изначально. Посредством таких вставок с заменой мы убираем промежуточные вычисления, что очень сильно ухудшает читаемость формулы.
вот пример на гугл. докс., но только он не работает потому что файл не сохранен на диск, сохраните и ковыряйте
https://drive.google.com/file/d/0B8QwhfN2DgusTzIxQ1ZHTlZtclc.
На этом пока все, в следующий раз наверное все таки будут сводные таблицы (по многочисленным просьбам)
Как я делаю шаблоны
Всем добра. Продолжаем совершенствоваться в овладении великим и могучим Excel.
Сегодня я решил показать один из способов, как в excel`е можно сделать заполняемые формы. Тут есть что то из старого материала (смотри предыдущие уроки), ну и кое-что новенькое покажу. Итак, допустим у нас есть какая либо форма, которую периодически нужно заполнять (на пример какие ни будь заявления, приказы, объяснения и т.д.). Данные в форме немного варьируются, некоторые слова немного меняются, но основной текст остается прежней, некоторые слова (имена, фамилии) нужно выбирать из списка, какие то значения вбивать и т.д. В таком случае можно просто брать шаблон и вбивать туда данные, но при частом использовании взгляд «замыливается» и возможны допущения ошибок. Так что сделаем такой шаблон, который можно было бы «настраивать». Мой пример будет иметь малое практическое применение, я просто в нем хочу показать варианты использования элементов управления формами. Для начала давайте их найдем. Находятся они на вкладке «Разработчик». Если ее не видно идем в параметры и включаем ее во вкладке «Настройка ленты». Жмакаем вставить и смотрим что нам тут есть полезного (функционал я расскажу сразу в примере):
Теперь накидаем пункты, которые будут переключаться.
в столбце H стоят вот такие формулы (есть волшебная комбинация клавиш Ctrl+ё, которая на листе показывает вместо значений формулы в ячейках):
которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек:
Отслеживайте свои цели в области здоровья и фитнеса в Excel
Создайте план диеты и упражнений
если вы корректируете свою диету для лучшей сердечи, подходите ли она для более здорового сердца или просто хотите выглядеть на все 10, первый шаг — спланировать ее. План поможет вам установить достижимые цели и позволит вам продумать ваши потребности. Ознакомьтесь с некоторыми из ресурсов Excel для планирования диеты и фитнеса ниже.
График амортизации калорий, чтобы управлять вашим весом
Независимо от того, теряете ли вы вес, получаете или сохраняете вес, важно отслеживать потребление калории. Это расписание амортизации калорий поможет вам разобраться. Просто введите свой возраст, рост, вес, пол, уровень активности и целевой вес, чтобы рассчитать ежедневное потребление калорий, которое будет реалистичным и легко достижимым.
Запланируйте свою программу фитнес-тренировок и запишите свои тренировки с помощью этого доступного шаблона. Используйте отдельные вкладки для планирования категорий упражнений, весов и повторений.
«У моего плана есть небольшие победы, чтобы помочь мне пройти финишную черту.»
Прежде чем планировать, посоветуйтесь с врачом, чтобы вы знали, на что нужно обратить внимание, способствует ли изменение вашей диеты для снижения уровня холестерина или выполнение упражнений для укрепления мышц.
Совет: Дело не в том, что вы «сбрасываете», а в том, что вы получаете.
Сосредоточьтесь на положительных выгодах для здоровья, чтобы создать план, который будет устойчивым и безопасным. Больше энергии от питательных продуктов, мышц, чтобы оставаться сильными, и меньший риск сердечных заболеваний.
Еженедельный планировщик еды
Введите то, что вы будете есть каждый день недели, а затем просматривайте его на ходу с помощью мобильного приложения Excel. Планирование еды заранее означает более продуманную диету, отсутствие еды в последнюю минуту, снижение затрат и сокращение отходов.
Отслеживайте хода выполнения упражнений и тренировок
Тренировка — это не просто то, что нужно. Сильные мышцы помогают предотвратить травмы, улучшить баланс и стабильность, а также дать вам здоровье для достижения новых целей. От походов с друзьями до работы в офисе, сильное тело является ключом к получению максимальной отдачи от жизни.
Отслеживайте все свои упражнения
Легко отслеживайте все различные упражнения для ваших тренировок (силовые тренировки, кардио и т. д.) Затем откройте вкладку обзора, чтобы увидеть удобную диаграмму, которая отображает вашу общую активность.
Хорошо иметь сбалансированные упражнения, которые прорабатывают все ваше тело. Слишком сильное сосредоточение на одной группе мышц может привести к чрезмерной компенсации вашего тела в других. Не забудьте включить различные типы и группы мышц в ваш план.
Совет: Не забывайте. Разогревайтесь и остывайте
Разогрейте мышцы, двигаясь и растягиваясь, чтобы снизить риск получения травм. Не забудьте остыть и после окончания тренировки.
Совет: Прислушивайтесь к своему телу
При выполнении упражнений будьте внимательны и сосредоточьтесь на том, что говорит вам ваше тело. Вы чувствуете странное ощущение колена при растяжке? Ослабьте немного. Это важные сигналы для безопасного наращивания силы.
Отслеживайте свою диету и питание с помощью Excel
Легко рассчитывайте жирность % вашей еды
Записывайте продукты, которые вы едите каждый день, вместе с их калориями и граммами жира, и этот доступный шаблон подсчитает процент жира в вашем рационе, чтобы помочь вам сделать оптимальный выбор питания.
Посмотреть тренды для диеты и физических упражнений
Задайте цель своего веса, задайте потребление пищи с калориями, сахарами, сахарами и калориями, чтобы создать дневник своих блюд. Вы также можете отметить продолжительность и количество сожженных калорий для ваших тренировок.
«Графики, которые показывают мне, насколько последовательно я выполняю свои диетические цели, являются очень удовлетворительными!»
Совет: Сначала поговорите со своим врачом о том, каковы ваши различные диетические потребности. Когда вы работаете над своими диетическими целями, важно убедиться, что вы достигаете того, что ваше тело должно быть здоровым. Принимайте разнообразные продукты и проконсультируйтесь с врачом, чтобы убедиться, что вы сосредоточены на правильных областях.
Отслеживание измерений и изменений с помощью вашего тела в Excel
Если одной из ваших целей в области здоровья и фитнеса является снижение веса, Excel предлагает множество шаблонов, которые помогут вам отслеживать и визуализировать свой прогресс. Проверьте наши любимые шаблоны отслеживания потери веса и измерения тела ниже.
Диаграмма хода выполнения тренировок для мужчин
Измерьте свой вес и измерения тела с этой диаграммой для мужчин. Ваш индекс массы тела (ИМТ), предполагаемый процент жира в организме и мышечная масса тела будут рассчитываться автоматически.
«Одежда подходит мне лучше, чем раньше. хотя я немного тяжелее.»
По мере того, как вы наращиваете мышечную массу и теряете жир, вы можете заметить, что вес на ваших весах, возможно, не сильно изменился или даже увеличился. Вместо того, чтобы фокусироваться на шкале, проведите измерения вашего тела и отслеживайте свой ИМТ, чтобы увидеть, насколько вы здоровы.
Диаграмма хода выполнения тренировок для женщин
Измерьте свой вес и измерения тела, чтобы отслеживать свой прогресс с помощью этого доступного шаблона для женщин. Измерения являются метрическими, а ИМТ и жир тела рассчитываются автоматически.
Другие способы следить за своим здоровьем в Excel
Журнал приема основных лекарств — отслеживайте прием лекарств с помощью этого комплексного шаблона журнала, который содержит сведения о дозировке, цели и способе применения, побочных эффектах, враче, аптеке, а также другие данные.
Отслеживание уровня глюкозы в крови — отслеживайте свое кровяное давление и уровень глюкозы за определенный временной интервал. Просто введите свои номера вместе с днем и временем, и условное форматирование предупредит вас, если вы выйдете за пределы настраиваемых параметров.
Учёт для тренера в Excel
Приветствую Вас, читатели Пикабу!
Много полезного и интересного я черпаю из этого сообщества, и поэтому сейчас, когда у меня появилось чем поделиться, делаю это с удовольствием.
Я люблю Excel. Работа с таблицами, изучение новых (для меня) его возможностей и реализация возникающих идей доставляет мне удовольствие. Процесс превращается в решение увлекательной (или не очень) задачи/ квеста.
Недавно в разговоре с моим тренером узнала, что ей нужна для учета оплат и посещений табличка в excel, но у неё самой что-то не получается это сделать. Мне данная задача показалась интересной (она такой и была), и я приступила к её выполнению.
Сначала подумала над структурой.
— список клиентов (есть постоянные и разовые);
— несколько групп по разным направлениям;
— 4 типа абонементов (групповые, разовые групповые, индивидуальные), с параметрами по срокам действия и количеству занятий;
Что необходимо было реализовать:
— Учет клиентской базы;
— Табель учета посещений + чтобы оплаченный период в нем подсвечивался цветом;
Процесс реализации (излагаю то, что помню).
1. Для учета клиентов и последующего вызова информации по ним в различных таблицах и формулах решено завести им УИН (уникальные идентификационные номера). Это облегчит поиск. Это было начало таблицы «Клиенты».
2. «Для подтягивания» информации по группам, типам абонементов, был создал лист «Справочники». В таблице «Группы» так же проставляются данные по дням недели, в которые проводятся занятия у группы. Это необходимо для форматирования внешнего вида табеля. Так же тут применена функция «Проверка данных» (чтобы в ячейку можно было внести только значения из списка).
3. Лист «Учет оплаты». Здесь в первой графе в ячейке выбирается УИН (через проверку данных), в ФИО через функцию ВПР подтягиваются данные ФИО. Дата проставляется вручную. Тип абонемента выбирается из списка (проверка данных) и с помощью ВПР подтягивает данные о количестве занятий и сроке действия абонемента в соответствующие ячейки.
4. На основе таблицы на листе «Учет оплаты» сделана сводная таблица (лист «Свод_оплата»)по оплате, с группировкой по месяцам. При желании таблицы и отчет можно дополнить типом оплаты – наличные либо безналичный перевод.
5. «Табель». Вот тут я попыхтела конечно. Если с помощью ВПР легко реализовала подтягивание информации по клиенту в графу ФИО, с помощью СЧЕТЕСЛИ графы сколько занятий посещено, сколько пропущено, то в процессе реализации подсвечивания оплаченного периода я повысила свой уровень знания экселя.
Тут хотела бы остановиться чуть подробнее.
Для подсвечивания актуального оплаченного периода необходимо выбирать последнюю оплату (максимальную дату) из таблицы по учету оплат. Я перерыла свою настольную книгу «Excel 2016 Библия пользователя». Потом я перерывала интернет. Находила аналогичные задачи, которые решали через формулы, при адаптации которых в моей книге ничего не получалось. Так я корпела дня два, в процессе которых нашла формулу для построения горизонтальной диаграммы Ганта через условное форматирование.
Пару дней назад, вечером, когда я уже свернула ноутбук, убрала книгу, и пошла в ванну, вспомнила, что в сводной таблице можно делать выборку максимального значения!
Попробовала реализовать – ПОЛУЧИЛОСЬ. Я запрыгала и затанцевала!
Простое и элегантное решение было найдено!
Оставалось только его из сводной таблицы отправлять в табель. Тут в течение дня и докопалась до ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. В книге «Библия пользователя» данная функция описана весьма скудно, и благо, что интернет всегда под рукой и там полно умных людей, кто знает excel гораздо лучше меня.
После этого дело оставалось за малым – оформить книгу под N-е количество групп и сделать условное форматирование.
В итоге получился вот такой табель.
С чем еще предстоит разобраться.
Даты в табель выводятся вообще все подряд. Пришлось их группировать по 10 дней.
Хочу разобраться как на основании заданных даты 1го дня занятий в году и дней недели, по которым проходят занятия, создавать последовательность дат для табеля, чтобы эта «колбаса» уменьшилась в 2-3 раза. Если кто-то знает, как это можно сделать – напишите, пожалуйста, в комментариях. Буду очень благодарна.
Ссылка на скачивание описанного в посте файла