Mr.Privet

пикабушник
пол: мужской
поставил 141 плюс и 19 минусов
проголосовал за 0 редактирований
сообщества:
4785 рейтинг 2073 подписчика 178 комментариев 16 постов 9 в "горячем"
1 награда
более 1000 подписчиков
29

Прячем папку

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

Прячем папку Рабочий стол, Windows, Папка, Excel, Длиннопост

А она есть. Вот эта вот рамочка. Давайте попробуем сделать такую. Создаем новую папку: правый щелчок на рабочем столе -> Создать -> Папку. Получаем новую папку, которая так и называется. Теперь убираем иконку папки: правый щелчок по папке -> свойства -> настройка -> сменить значок… и ищем вот это вот

Прячем папку Рабочий стол, Windows, Папка, Excel, Длиннопост

Теперь пробуем поменять название папки: правый щелчок -> переименовать -> пробуем ставить пробелы. А вот и первый хрен вам. Винда у нас умная и не дает ставить нам пробелы, которых не видно. Что же, это не единственный символ которого не видно, есть еще « » который не равен « » и для наших целей подходит. Идем в Эксель на новый лист, пишем в ячейке =СИМВОЛ(160), копируем эту ячейку, в соседнюю вставляем значения, проваливаемся в нее (во вторую), выделяем то, что в ней в верхней строке состояния и копируем.

Прячем папку Рабочий стол, Windows, Папка, Excel, Длиннопост

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

Прячем папку Рабочий стол, Windows, Папка, Excel, Длиннопост

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

Прячем папку Рабочий стол, Windows, Папка, Excel, Длиннопост

Вот такая вот хреновая конспирация!

P.s. в комментариях знающие люди подсказали что вместо любви с экселем можно при вводе имени папки зажать alt и набрать 0160 или 255 на доп. клавиатуре.

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

Рисуем в 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
1536

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Волшебная формула

Всем привет. Продолжаем постигать Excel. Если Вы не знакомы с моими предыдущими уроками, то советую ознакомиться. Сегодня я хочу продолжить тему, поднятую в предыдущем посте https://pikabu.ru/story/kak_ya_delayu_shablonyi_6002481 о составлении шаблонных форм. Однажды мне потребовалось сделать такой лист, содержимое которого зависит от названия листа. Я нашел одну чудесную формулу и хочу ею с Вами поделиться. Также дополнительно я хочу показать, как в простом листе запутать формулы так, чтобы потом в них было практически невозможно разобраться. Спросите, зачем это нужно? Иногда бывает полезно – скидываешь человеку таблицу и если в ней нужно что-то переделать, то он опять обращается к Вам, только не забудьте сохранить у себя исходник. Но обо всем по порядку.

Сначала немного теории.
Рассмотрим работу функции ЯЧЕЙКА (тип_сведений;[ссылка]), она имеет 2 аргумента: тип сведений и ссылку на ячейку соответственно. Сейчас нас интересует тип «имяфайла», выбираем его и ссылаемся на саму ячейку, где мы это пишем (остальные типы вам на самостоятельное изучение). Для корректной работы этой функции необходимо чтобы файл был сохранен где-нибудь. Итак, в имени файла мы видим непосредственно имя файла и после него имя листа, на котором прописываем эту функцию (точнее на лист, куда ссылаемся ссылкой). Давайте попробуем вырезать то, что нам нужно, а именно имя листа. Для этого нам нужно знать длину текста в ячейке с именем файла, получаем ее с помощью функции ДЛСТР(),затем нам нужно найти позицию закрывающейся квадратной скобки, которая ограничивает имя файла при помощи функции ПОИСК, разница этих чисел и будет длиной имени листа. Отрезаем справа от первоначальной ячейки эту длину и получаем ячейку с именем файла. В результате у нас должно получиться нечто подобное.

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

Формулы при этом выглядят так:

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

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

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

назавем этот лист  «База».
Здесь рассмотрим еще одну полезную функцию Excel, которая называется именованные области (или как-то так). Выделяем область к которой нам нужно будет часто обращаться, в нашей таблице это столбцы от А до G на листе «База», и жмакаем на строку которая расположена слева от значка формулы

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

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

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

помним что в ячейке D5 у нас название листа, которое будет искаться в первом столбце базы, то есть для создания карточки сотрудника нам нужно будет назвать лист как фамилию нужного нам сотрудника. Я решил не просто тупо подставлять значения из таблицы (это же скучно), а склеить ФИО, вместо дня рождения выводить сколько полных лет, а вместо даты приема на работу стаж в годах. Но это просто в образовательных целях. Можно и эти данные в формуле прописать в базе.
Теперь проверим, назовём лист «Иванов»:

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

Во как!
на основании такого листочка и нехитрого макроса я на пример делал много отчетов с разбивкой по пятидневкам или по дням из месячной базы. Но создание макросов я намеренно не рассматриваю в своих статьях (сам только учусь их писать)
Теперь перейдем ко второй части нашего балета. То есть запутаем эту формулу так, чтобы сами не могли ничего разобрать. Смотрите, у нас в ячейке D1  прописана формула, ссылающаяся в принципе на любую ячейку этого листа, для удобства поменяем чтобы она ссылалась на ячейку A1. Теперь посмотрим, кто как у нас на этом листе ссылается: ставим курсор на A1, идем в пункт формулы, нажимаем «зависимые ячейки» несколько раз, получаем:

Волшебная формула Excel, Урок, Для чайников, Шаблон, Длиннопост

видим что все формулы зависят от А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...
На этом пока все, в следующий раз наверное все таки будут сводные таблицы (по многочисленным просьбам)

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

1000 подписчиков

1000 подписчиков Excel, Прикол, Урок

Наблюдал за счетчиком подписчиков и решил отметить этот маленький юбилей этим маленьким постом. Представляю вам универсальный генератор рифмы. Вводим любое существительное в ячейку В2 и получаем универсальную рифму в ячейке В3. Кстати некоторые музыканты уже давно взяли на вооружение этот генератор.
https://drive.google.com/file/d/0B8QwhfN2Dgusd01zTkowOFo1dmV...

Спасибо, что вы со мной!
Буду пилить следующий пост…

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

606

Как я делаю шаблоны

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

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

-кнопка (ее использовать не будем, она в основном нужна для запуска макросов, в макросы я пока не лезу)
- поле со списком (компактный выпадающий вариант списка)
-флажок (вкл или выкл)
-счетчик(стрелочки для «накрутки» какого либо показателя)
-список (выбор из нескольких элементов без выпадения списка)
-переключатель (кружечек, который имеет только одно активное положение)
-полоса прокрутки (как счетчик только еще с бегунком)
Итак, все эти элементы можно привязать к какой ни будь ячейке, в которую будет выводиться результат наших манипуляций, а поля из списка нужно подтягивать из нескольких ячеек.
Вытащим какой любой элемент, и посмотрим на формат объекта (правый клик по элементу), нам интересен раздел «Элемент управления». Для списка и поля со списком есть такие свойства:
-Форматировать список по диапазону (здесь мы указываем список из возможных значений для выбора диапазона)
-связать с ячейкой (выбираем ячейку в которую выводится результат)
также в выпадающем списке есть: -Количество строк списка (сколько всего будет строк в выпадающем списке, если вбить меньше количества элементов списка, появятся стрелочки для перемотки)
а в обычном списке есть выбор одного значения, либо набора, либо списка нескольких значений (к своему большому стыду я так и не разобрался зачем нужны последние 2 пункта, так как при их выборе изменения в списке никак не влияет на изменений в связанной ячейке)
Для счетчика и полоски прокрутки есть свойства мин, макс значения, шаг изменения и связь с ячейкой, тут я думаю все наглядно.
Для флажка и переключателя интересны только связи с ячейкой, причем я не смог добиться от Excel того чтобы на одном листе можно было сделать несколько списков переключателей, если один привязываешь к ячейке все другие переключатели на листе сами туда привязываются. При изменении состояния флажка в ячейку передается номер ячейки, в той последовательности, что вы их добавляли на лист
Итак, для начала накидаем шаблон.

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

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

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

Теперь будем «связывать». Я обычно делаю все на 1 «техническом» листе, потом «технические» ячейки скрываю. но можно их подтянуть на отдельный лист и скрыть весь лист.
небольшое удобство: если выбрать элемент управления правой кнопкой он выделяется, и его можно на пример передвигать, а в строке ввода формул появляется связанная ячейка. ее это та же ячейка что и в Формат объекта - > элемент управления ->Связь с ячейкой.
Флажок уважаемости связываем с ячейкой G4, переключатели адресата связываем с ячейкой G4, список месяца с G7, список числа с G6, полосу прокрутки времени опоздания с G8, список причины с G9.
Получаем такую табличку:

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

в столбце H стоят вот такие формулы (есть волшебная комбинация клавиш Ctrl+ё, которая на листе показывает вместо значений формулы в ячейках):

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек:

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост
Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост
Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост
Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

здесь мы формируем список для дней недели, формулы в ячейках О31-О33 проверяют выбранный месяц и в зависимости от того сколько в нем дней из столбца М прописывают нужное количество. (кстати если лень лесть в календарь и смотреть нужное количество дней в столбец М я прописал формулу =ДЕНЬ(КОНМЕСЯЦА(ДАТА(2018;J3;1);0)) которая в результате показывает количество дней в порядковом номере месяца из столбца J. это вам для самостоятельного изучения)

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

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

Как я делаю шаблоны Excel, Урок, Шаблон, Длиннопост

Файл Гугл докс лежит тут:

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

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

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

EXCEL для чайников. подбор параметра

Всем приветы.

Начал писать обзорный пост про сводные таблицы и понял, что сегодня не хочется писать такой большой объем. Допишу потом.  А пока небольшой обзор функции «подбор параметра». Рассмотрим работу на небольшом примере. Допустим, что у нас есть некое количество товара, которые мы покупаем, а потом продаем с надбавкой:

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формулы, Функция

закупочная цена у нас фиксированная, количество и надбавка вбивается значениями, а остальное у нас прописано формулами. Итак, нам захотелось навариться не на 1600, на 2000, при этом захотелось нам поиграть с розничной стоимостью компасов. Ну, в примере все просто, сначала решаем в уме: нам нужно увеличить выручку на 400 р., потом эти 400 р. раскидать на 3 компаса, то есть на каждый компас нужно продать дороже на 133 р., то есть за 733 рубля, что составляет от 500 рублей изначальной цены, где то 733/500, то есть 1,47, то есть надбавка должна быть где-то 47 %. На самом деле этапов вычислений может быть больше,  главное чтобы начальное и конечное значение было связано через формулы. Подставляем 47% и видим что у нас 2005 рублей. Это из-за наших умственных округлений. Трудно ручками подобрать так чтобы было ровно 2000. Теперь идем во вкладку Данные – Анализ «что если» - Подбор параметра. Вводим вот так:

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формулы, Функция

нажимаем ОК и радуемся.

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формулы, Функция

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

EXCEL для чайников. подбор параметра Excel, Для чайников, Длиннопост, Формулы, Функция

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

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

EXCEL для чайников.3.текст

Доброго времени суток, пикабушнички! Предлагаю продолжить работу по осёдлыванию могучего скакуна, по кличке Excel. Это мой третий пост. В предыдущих мы говорили о функциях времени https://pikabu.ru/story/excel_dlya_chaynikov2vremya_5352893 и ВПР https://pikabu.ru/story/excel_dlya_chaynikov1vpr_5337342 , этот же пост будет про работу с текстом. Некоторые сейчас подумают: «Что? Зачем работать с текстом в экселе, если есть ворд?» или даже «Ворд - для текста, ексель - для таблиц!». Однако, глупо отрицать, что бывают такие ситуации, когда текст нужен в таблице. И, выбирая между работой с таблицей в ворде и работой с текстом в екселе, вы вряд ли выберете первое. А если выберете… храни вас Бог.


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


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


ДЛСТР(текст) – выдает длину строки (количество символов)


ЛЕВСИМВ(текст, [число_знаков]) – «отрубаем» нужное количество знаков слева


ПРАВСИМВ(текст,[число_знаков]) – «отрубаем» нужное количество знаков справа


ПСТР(текст, нач_позиция, число_знаков) – «вырубаем» нужное слово или несколько из текстовой ячейки


ПРОПИСН(текст) - ДЕЛАЕТ ВЕСЬ ТЕКСТ ПРОПИСНЫМ (для любителей КАПСА!)


ПРОПНАЧ(текст) - Делает Первые Буквы Слов Прописными


СТРОЧН(текст) – все буковки маленькие


ЗНАЧЕН(текст)- преобразует текст в числовое значение (полезно если откуда-то достались числа, сохраненные как текст, с которыми нельзя совершать математические действия)


ТЕКСТ(значение, формат) преобразует значение ячейки в текст нужного нам формата


СЖПРОБЕЛЫ(текст)- удаляет «ненужные» пробелы


ПЕЧСИМВ(текст)- убирает непечатные символы


КОДСИМВ(текст)- показывает код символа ANSI если это вам о чем то говорит (если символов несколько то код первого символа)


СИМВОЛ(число) пишет символ по его коду (это как КОДСИМВ наоборот)


ПОВТОР(текст, число_повторений) – повторяет текст нужное количество раз


СЦЕПИТЬ(текст1, [текст2], ...) сцепляет несколько текстовых ячеек в одну (вместо этой функции я предпочитаю использовать символ &, который аналогичен ей)


НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])ищем определенный текст в ячейках, результатом будет порядковый номер первого символа исходного текста (ниже будут примеры)


ПОИСК(искомый_текст, просматриваемый_текст,[нач_позиция])то же что и НАЙТИ но без учета регистра


ЗАМЕНИТЬ(стар_текст, нач_позиция, число_знаков, нов_текст) заменяет кусочек текста ячейки на другой


ПОДСТАВИТЬ(текст, стар_текст, нов_текст, [номер_вхождения]) заменяет одну часть текста на другую в нужном «вхождении» (терпение, примеры ниже)


СОВПАД(текст1, текст2) – сравнивает две ячейки на предмет их тождественности


T(значение)- если эта функция ссылается на текст, то возвращает его, если нет, то возвращает пустое место


РУБЛЬ(число, [число_знаков]) переводит число в денежный формат (с нужным округлением)


ФИКСИРОВАННЫЙ(число, [число_знаков], [без_разделителей]) – округляем число и преобразовываем его в текст (разделители это такие пробелы между тысячами, миллионами и т.д., чтобы число лучше читалось.


Пример работы всех описанных функций:

EXCEL для чайников.3.текст Для чайников, Excel, Длиннопост, Текст

Комментарии к таблице в общем-то излишни, думаю все вполне наглядно. Поясню пару моментов:


1. Округление в функциях РУБЛЬ и ФИКСИРОВАННЫЙ я нарочно делал отрицательным, чтобы показать, что так можно, в этом случае округление идет до десятков (-1), сотен(-2), и т.д. Этот прием работает и с обычными округлениями ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ при разложении числа на десятки, сотни и т.д.


2. Число в ячейке В3 сохранено как текст, поэтому с ним нельзя будет выполнять каких либо арифметических действий пока мы не переведем его в значение, по сути функции ЗНАЧЕН(), РУБЛЬ() и ФИКСИРОВАННЫЙ() делают одно и тоже, только две последних меняют еще и формат ячейки.


Подробнее о форматах расскажу в следующий раз.


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

EXCEL для чайников.3.текст Для чайников, Excel, Длиннопост, Текст

теперь посмотрим какие формулы прописаны у нас в столбцах В-Н:


столбец В: =ДЛСТР(A3) – находим длину текстовой ячейки в столбце А;


столбец С: =ПОИСК(" ";A3;1) – ищем первый пробел, начиная с первого символа;


столбец D: =ПОИСК(" ";A3;C3+1) – ищем второй пробел, начиная со следующего символа после первого пробела, найденного ранее;


столбец Е: =ЛЕВСИМВ(A3;C3-1) – отрезаем фамилию, отрезая заодно один символ пробела;


столбец F: =ПСТР(A3;C3+1;D3-C3) – вырезаем имя из середины, от первого пробела на длину имени, равную разности позиций первого и второго пробела;


столбец G: =ПРАВСИМВ(A3;B3-D3) – отрезаем отчество, длина которого равняется разности позиции второго пробела и общей длинны текстовой ячейки;


столбец Н: =СЦЕПИТЬ(E3;" ";ЛЕВСИМВ(F3;1);".";ЛЕВСИМВ(G3;1);".") – набираем фамилию и первые буквы имени и отчества, разделяя их пробелом и точками;


столбец I: =ЕСЛИ(СОВПАД(E3;ПРОПНАЧ(E3));"ДА";"НЕТ") – проверяем совпадают ли значения ячейки с фамилией и такой же ячейки проведенной через функцию ПРОПНАЧ, которая делает заглавной первую букву, иными словами проверяем является ли в ячейке с фамилией первая буква заглавная а остальные строчные. Если является, то пишем «ДА»; если нет то «НЕТ».


На этом пока все, буду фантазировать над темой следующего поста…

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

EXCEL для чайников.2.Время

=”ПРИВЕТ”&” “&”ВСЕМ!”


Мой предыдущий пост https://pikabu.ru/story/_5337342 содержал несколько грубых педагогических ошибок. Почитав комментарии и сделав выводы, я решил двигаться дальше, побольше, так сказать, разъясняя. Готовьтесь, пост будет еще длиннее.


Сегодня я расскажу вам о времени. Ученые и философы испокон веков спорят о его происхождении, и даже о его существовании. В Excel оно точно есть и работать с ним можно и нужно. Итак, что же такое время в Excel? Возьмем число 42997,635216. Что оно может означать? Человек, работающий с датами в Excel, сразу поймет подвох. Дело в том что это дата и время, в Excel, взятые на момент написания поста функцией =ТДАТА() в формате обычного числа. Про форматы поговорим отдельно, сначала нужно разобраться с представлением дат и времени: 42997 – это количество дней, начиная с 1 января 1900 года, (так что если вы историк то работать с датами до 1900 года придется как обычным текстом, без вычислительных выкрутасов), Стоит так же отметить, что сейчас работать с датами можно вплоть до 31 декабря 9999 г. (тут писатели фантасты печально вздохнули и полезли за калькуляторами). Дробная часть 0,635216 это время от целых суток, то есть 1 – это 24 часа. Давайте проверим за Excel, все ли правильно он посчитал: 0,635216*24=15,245184 (15 это часы); 0,245184*60=14,71104 (14 это минуты); 0,71104*60=42,6624 (43 это секунды, округляем до целого). Теперь переводим ячейку с числом 42992,57046 в формат даты и времени и получаем 19.09.17 15:14:43, хотя функция =ТДАТА() уже изрядно убежала за время нашего расчета. Поверьте, нет смысла проверять за Excel. Не нужно тратить на это драгоценное время, он все считает правильно, ошибка может быть только по другую сторону монитора. Многим это покажется смешным, но у меня есть знакомые, которые проверяют Excel на калькуляторе.


Понимание того что целая часть - это дни, а дробное - это часы, минуты, и секунды, очень важно. Функция =ТДАТА() прекрасна и опасна. Ведь она выполняет расчеты относительно текущего времени, и, если это не нужно и об этом забыть, цифры в таблице поплывут. Они будут плыть пока мы не закрепим дату. Нужно взять ячейку с формулой, войти в режим редактирования и нажать F9, либо копировать её и вставить обратно в туже ячейку как значение. Ход времени в таблице остановится, данные перестанут обновляться.


Какие же еще функции есть в Excel для работы со временем? О, их великое множество, благо в основном названия функций в Excel «говорящие».


ТДАТА( ), СЕГОДНЯ( ) – текущее дата и время в первом случае, и дата без времени во втором.


ВРЕМЗНАЧ(“Текст”), ДАТАЗНАЧ(“Текст”) – переводит время либо дату из текста в числовой формат;


ВРЕМЯ(Часы; Минуты; Секунды), ДАТА(Год; Месяц; День) – «собирает» время либо дату из значений;


ГОД(Дата), МЕСЯЦ(Дата), ДЕНЬ(Дата), ЧАС(Дата), МИНУТЫ(Дата), СЕКУНДЫ(Дата) – «вырезаем» нужное из даты;


ДЕНЬНЕД(Дата, Тип) – выдает номер дня недели (наш тип недели 2, американской 1).


КОНМЕСЯЦА(Дата; Число_Месяцев) – это дата последнего дня месяца со смещением на нужное количество месяцев


ДАТАМЕС(Дата; Число_месяцев) – передвигает эту же дату на нужное количество месяцев вперед или назад


НОМНЕДЕЛИ(Дата; Тип) – номер недели с начала года (тип как в ДЕНЬНЕД)


РАБДЕНЬ(Дата; Количество; Праздники) – дата, которая будет или была через заданное количество дней (учитывая или нет праздники)Праздники задаются диапазоном ячеек


РАБДЕНЬ.МЕЖД(Дата; Количество; Выходной; Праздники) – то же самое, но с расширенной настройкой выходных данных. Можно задать строкой где 0-это рабочий день, 1-это выходной, на пример нормальная рабочая неделя выглядит так “0000011”


ЧИСТРАБДНИ (Дата1; Дата2; Праздники) – возвращает количество рабочих дней между 2 датами (с праздниками или без них)


ЧИСТРАБДНИ.МЕЖД (Дата1; Дата2; Выходной; Праздники) – то же самое, но с произвольным выбором выходных дней. (см. РАБДЕНЬ.МЕЖД)


ДНЕЙ360(Дата1; Дата2) – “Функция ДНЕЙ360 возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если система бухгалтерского учета основана на двенадцати 30-дневных месяцах.” © - взял из справки, в бухгалтерии не силен, ничего добавить не могу, кроме того, что это как-то связано с расчетом равномерности платежей в течении года. В общем, эти бухгалтера даже в году умудрились спереть 5 дней.


ДОЛЯГОДА(Дата1; Дата2; Базис) – это доля года между двух дат. Базис равен 1, если хотите считать по фактическим датам. В противном случае данное значение варьируется от 0 до 3, выбирайте то, что нужно, согласно пояснениям, содержащимся в справке.


Теперь давайте разберемся, как это работает. В столбце А я напишу формулу, а в столбце В, С, D я напишу результат этой формулы в разных форматах, в столбце E напишу комментарии.

EXCEL для чайников.2.Время Excel, Для чайников, Длиннопост
Еще раз обращаю внимание на то, что значения в столбцах B,C,D равны друг другу. Рассмотрим небольшой пример работы с рабочими днями. На пример давайте представим, что наша Госдума хочет сделать в 2018 году пятницу или понедельник выходным днем. Но для этого нужно убрать отпуск и праздничные дни чтобы компенсировать недостаток рабочего времени. Сколько же рабочих дней у нас получится? Находим праздничные дни в 2018 году (как оказалось их 14), вбиваем эти дни в таблицу. Я не учитывал переносы праздников с субботы на понедельник, так как в этом случае количество рабочих дней не меняется.
EXCEL для чайников.2.Время Excel, Для чайников, Длиннопост
Понедельников у нас в 2018 году на 1 день больше чем пятниц, так что результат разный. Напоследок, давайте сделаем какой-нибудь пример с использованием некоторых приемов. Допустим Вы начальник кадровой службы, у вас есть дата и время прихода и ухода сотрудников на работу. Нужно посчитать общее время, проведенное на работе и посмотреть, нет ли нарушений в распорядке трудового дня. Также есть начало и конец рабочего дня, которые задаются значениями в ячейках.
EXCEL для чайников.2.Время Excel, Для чайников, Длиннопост

Теперь посмотрим, какие формулы у нас стоят в ячейках в столбцах D-I, для удобства я их представил немного в другом виде, формулы представлены для 2-й строки, для остальных строк их нужно только «протянуть»


Время на работе (ч) =(C2-B2)*24 – тут мы вычитаем дату прихода из даты ухода, из суток переводим в часы, все просто.


Время на работе в рабочие дни (ч) =(ЧИСТРАБДНИ(B2;B2)*(C2-B2))*24 тут мы учитываем, был ли день рабочий. Я обнаружил, что если применить ЧИСТРАБДНИ с указанием одного дня эта функция в случае рабочего дня выдаст 1 и в случае выходного - 0, далее все как в формуле выше.


Время на работе в выходные (ч) =(НЕ(ЧИСТРАБДНИ(B2;B2))*(C2-B2))*24 тут мы «перворачиваем» функцию ЧИСТРАБДНИ логической функцией НЕ, которая из 1 делает 0 а из 0 делает 1, далее все как выше.


Недоработка/переработка (ч) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);D2-($K$2-$K$1)*24;F2) здесь используем функцию ЕСЛИ. Эта функция имеет 3 аргумента: логическое условие, результат при выполнении этих условий и результат, если логическое условие не выполняется. В данном случае мы проверяем по функции ЧИСТРАБНИ является ли день рабочим, если является вычитаем из фактически отработанного времени норму рабочего времени, которая в свою очередь получается из разницы конца и начала рабочего дня. Затем переводим все в часы, умножая на 24. если день выходной учитываем все время, проведенное на работе как переработку. Обратите внимание что ссылки на ячейки начала и конца рабочего дня мы «закрепили» символами $. Это делается нажатием клавиши F4 при нахождении курсора на ячейке, также есть возможность напечатать данный символ вручную. Смысл «закрепления» ячейки в том, что при протягивании формулы ссылки на «закрепленные» ячейки не будут смещаться относительно перемещения формулы по столбцам и строкам. Можно также закрепить отдельно либо столбец, либо строку, в нашем случае ссылка выглядела как $K2 при закрепленном столбце и как K$2 при закрепленной строке. При протягивании в таком случае меняется только незакрепленный фрагмент адреса ячейки, что бывает весьма полезно в некоторых случаях.


Опоздание (мин) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);ЕСЛИ(B2-ОКРУГЛВНИЗ(B2;0)<$K$1;"";ОКРУГЛ(((B2-ОКРУГЛВНИЗ(B2;0))-$K$1)*24*60;0));""). Также, как и выше, мы проверяем рабочий ли у нас день. Затем (это мое любимое) вычисляем время прихода сотрудника, без учета даты. Для этого я отнимаю из даты со временем значение той же даты со временем округленное вниз до целого значения с помощью функции ОКРУГЛВНИЗ. Выражение B2-ОКРУГЛВНИЗ(B2;0) у нас будет иметь значение 8:42 в формате времени, то есть время прихода сотрудника. В принципе мы могли бы написать =ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)), это аналогичное решение, которое собирает время из значений часов, минут и секунд, но первое решение мне нравится больше. Затем сравниваем это время с временем начала рабочего дня, если оно меньше - оставляем ячейку пустой (“”), если же больше - считаем что сотрудник опоздал и высчитываем опоздание в минутах: из времени фактического прихода отнимаем время начала рабочего дня и умножаем на 24 и на 60, чтобы получить минуты, затем округляем полученный результат до целого значения. В случае же если день выходной, то логическое условие функции ЕСЛИ не выполняется и ячейка остается пустая.


Ранний уход (мин) =ЕСЛИ(ЧИСТРАБДНИ(C2;C2);ЕСЛИ(C2-ОКРУГЛВНИЗ(C2;0)>$K$2;"";ОКРУГЛ(($K$2-(C2-ОКРУГЛВНИЗ(C2;0)))*24*60;0));""). Тут все аналогично предыдущему, за исключением того что учитывается время ухода, которое должно быть больше времени окончания рабочего дня.


Вот и все что я хотел рассказать про время, пост получился длинноватым. Надеюсь, Вы меня поняли. Буду думать, о чем рассказать в следующий раз. Помните, сначала Вы работаете в Excel, потом Excel работает за Вас!

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

EXCEL для чайников.1.ВПР

Добрый день!


Решил запилить пост про любимый Excel. Работают в нем многие, также и многие пользуются лишь минимальным набором функций, а это не правильно, поскольку в Excel‘е можно решить широкий спектр задач. Мне нравится автоматизировать некоторые рутинные процессы. Если тема получит положительный фитбэк буду продолжать писать, если есть какие то вопросы не стесняйтесь и задавайте. Тема сегодняшнего поста функция ВПР и еще немного вспомогательных функций. Итак начнем. Скажу, что самое сложное было придумать задачу… Допустим у нас есть некий реестр товаров и ID менеджеров, которые этот товар реализовали, а также есть реестры менеджеров 1 отдела и 2 отдела. для интереса пусть в реестрах будут только фамилии, а имена и отчества будут еще в одном реестре


Реестр товаров и ID менеджеров

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

Реестр менеджеров 1 отдела

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

Реестр менеджеров 2 отдела

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

Реестр имен отчеств

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

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

=ВПР(C2;G:H;2;0), протягиваем до конца листа


у нас подтянулись фамилии из реестра первого отдела, идем дальше


в той же D2 пишем

=ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))


можно для начала делать ВПР в разных ячейках, потом их значения объединять в третьей ячейке при помощи функции ЕСЛИОШИБКА. на пример так

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

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


Так, теперь в столбце Е нужно указать Имя Отчество, снова ВПР… В ячейке E2 пишем

=D2&" "&ВПР(D2;M:N;2;0).

Здесь мы использовали символ & чтобы объединить 2 ячейки и поставить пробел между ними. При желании можно все забубенить в одну формулу для ячеек в столбце D


=ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))&" "&ВПР(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0));M:N;2;0)


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


=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))&" "&ВПР(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0));M:N;2;0);"менеджер не найден")


в случаи отсутствия ID в наших двух реестрах функция ЕСЛИОШИБКА вернет фразу «менеджер не найден»


результат нашего труда

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

Интервальный просмотр и с чем его едят:


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

EXCEL для чайников.1.ВПР Excel, Впр, Моё, Длиннопост

формулу в ячейке B2 =ВПР(A2;D:E;2;1) протянуть до конца таблицы.


Почему в меня не ВПРится ?!.


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


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

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

Мы ищем frontend-разработчика

Мы ищем frontend-разработчика

Привет!)


Мы открываем новую вакансию на позицию frontend-разработчика!

Как и в прошлые разы для backend-разработчиков (раз, два), мы предлагаем небольшую игру, где вам необходимо при помощи знаний JS, CSS и HTML пройти ряд испытаний!


Зачем всё это?

Каждый день на Пикабу заходит 2,5 млн человек, появляется около 2500 постов и 95 000 комментариев. Наша цель – делать самое уютное и удобное сообщество. Мы хотим регулярно радовать пользователей новыми функциями, не задерживать обещанные обновления и вовремя отлавливать баги.


Что надо делать?

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


Вам необходимо знать современные JS, CSS и HTML, уметь писать быстрый и безопасный код ;) Хотя бы немножко знать о Less, Sass, webpack, gulp, npm, Web APIs, jsDoc, git и др.


Какие у вас условия?

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


Ну как, интересно? Тогда пробуйте ваши силы по ссылке :)

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

Удачи вам! ;)

Показать полностью
Отличная работа, все прочитано!