Сообщество - MS, Libreoffice & Google docs
MS, Libreoffice & Google docs
120 постов 7 117 подписчиков
34

Excel в стиле Material Design

Для начала - добрейшего утречка)
С одной стороны - эксель это инструмент в первую очередь для работы, и "хочу красиво" к нему не очень относиться,  а с другой... а почему бы и нет, черт подери?) Тем более все достаточно просто - нужно только желание сделать красиво и один раз потратить немного времени.
Оптимально для составления справочников/ файла с константами.
В развернутом виде:

Excel в стиле Material Design Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

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

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

Excel в стиле Material Design Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

Остается только "шлепнуть печать" и написать "Утверждаю"

Все сокращения нативно понятны для сотрудников, но на всякий случай есть шпаргалка:

Excel в стиле Material Design Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

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

Картинки мои, файл мой, циферки тоже сам рассчитывал)
Если будет интерес - покажу еще парочку применений в справочниках

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

Настройка строки состояния ms word

Доброго времени суток!

Нужна маленькая помощь.

Мама на пенсии занялась копирайтингом, рерайтингом или как там всё это называется, поэтому пишет много текста.

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

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

Третий день ищу решение и никак. Сам в этом не очень разбираюсь.

Настройка строки состояния ms word Microsoft Word, Копирайтинг
Настройка строки состояния ms word Microsoft Word, Копирайтинг
Настройка строки состояния ms word Microsoft Word, Копирайтинг
Показать полностью 2
148

О пользе продвинутого знания Ёкселя и о программировании

Надо внести в онлайновую базу, по работе, 180 записей. Каждая из 15 полей. Обычно мы это делали через CTRL+C/CTRL+V из Ёкселя, куда данные вбивались заранее. Т.к. обычно вносилась одна запись в неделю-две, редко больше.- А тут целых 180, причём надо, как обычно, "вчера".

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

===

Где-то минут за 40 неспешной и творческой работы наваял простенький конвертер, не выходя из Ёкселя:

- страница с настройками и константами

- страница, куда копипастится блок из рабочего файла Ёкселя, который надо экспортнуть

- страница с результатами, которая тупо сохраняется в CSV.

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

===

Затем за пару прогонов через утилиту перегнал в XML, подписал ЭЦП и загнал на сайт. Ошибок: 0.

- Это заняло у меня ещё минут 15, вместе с проверкой исходных данных и результата, подписью ЭЦП и проверки, что всё успешно залилось и открывается онлайн.

===

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

- Т.е. CTRL+C ALT+TAB CTRL-V TAB ALT-TAB Right... и так много раз подряд.

- Это 15*180=2700 секунд или 45 минут непрерывной, неотрывной работы.

- Это без учёта необходимости кликнуть в браузере "Добавить запись" и в конце "Сохранить" и промотать колонки в Ёкселе, т.к. их порядок не совпадает с порядком полей на сайте.

- И без учёта возможных косяков при копипащении.

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

===

Ну и да, я НЕНАВИЖУ монотонную работу. Для меня и 10 записей подряд внести напряжно. Так что сидел бы я пару рабочих дней точно...

===

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

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

- Попробовал смоделировать её скорость работы (мышкой) - получилось секунд 10 на одно поле. Т.е. 7.5 часов, опять же - непрерывной, - работы.

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

===

Мораль простая. По возможности, изучайте программирование в целом и формулы MS Excel в частности.

- Это реально ОЧЕНЬ помогает в работе.

- И ОЧЕНЬ экономит нервы.

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

Выпадающие списки в Excel

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

Рад видеть в нашем тематическом сообществе интересные и полезные посты авторов @Petrov210217, @navferty, @zoodes, @FelixJanssen, @zoixnet, @kda2495, @avvacat, продолжайте в том же духе))


Итак, по просьбе @LordTachanka выкладываю пост, как создать выпадающие списки разными способами.


1 способ


В пустой ячейке под списком нажимаем сочетание клавиш «Alt+стрелка вниз», либо в меню правой кнопки мыши «Выбрать из раскрывающегося списка» и выбираем ранее введённые значения, которые будут отсортированы в алфавитном порядке.

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

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

2 способ


Создаём список с данными из которых будет сформирован выпадающий список. Выделяем диапазон, где будет отображаться выпадающий список, затем на вкладке «Данные» – «Проверка данных» – «Тип данных» выбираем «Список». Далее ставим курсор в поле «Источник», куда указываем диапазон с образцовыми значениями списка:

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

Плюсом является также то, что можно задать уведомление об ошибке при вводе товара, отсутствующего в списке:

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

В результате будет выходить такое сообщение:

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

Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».


С помощью макроса (с возможностью добавления новых элементов)


Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.

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

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

Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная - Форматировать как таблицу, чтобы создать "умную" таблицу:

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

Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем списке. Для этого выделим в списке уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Легенды):

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

После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.

Проверить диапазон можно на вкладке Формулы - Диспетчер имен:

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

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


Теперь создаём выпадающий список в ячейке

Выделяем зелёные ячейки и жмём на вкладке "Данные" кнопку "Проверка данных".

Далее выбираем из выпадающего списка "Тип данных" позицию "Список" и вводим в строку" Источник" ссылку на созданный именованный диапазон =Легенды:

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

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

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

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

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

Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем "Просмотреть код". В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:

Private Sub Worksheet_Change(ByVal Target As Range)

Set p = Range("Легенды")

If Target.Cells.Count > 1 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("D2:D11")) Is Nothing Then

If WorksheetFunction.CountIf(p, Target) = 0 Then

r = MsgBox("Добавить новое имя в справочник?", vbYesNo)

If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target

End If

End If

End Sub

Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:

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

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

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

Электронный ежедневник в Excel

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


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


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

Электронный ежедневник в Excel Excel, Vba, Gtd, Ежедневник

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


- файл оптимизирован для работы на FullHD мониторе;


- файл автоматически создает резервную копию рядом с собой (на всякий случай);


- в ячейке Подзадача для удобства можно выполнять вычеркивание через Ctrl+5, перенос строки через Alt+Enter, вставить текущую дату через Ctrl+Ж, текущее время - через Ctrl+Shift+Ж (это стандартно для Excel в целом);


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


Данный файл используется мной с 2015 года, и за это время было выполнено более 1000 задач, каждая из которых доступна для просмотра. Кроме того, значительная часть функций оригинального файла была переделана и добавлены новые функции, а потому с некоторой долей гордости предоставляю на ваш суд (с надеждой, что кому-то еще может пригодится). Код немного корявый, но, вроде, работает, как задумывалось. Скачать его можно по следующей ссылке: https://www.dropbox.com/s/qqhpqtugywzn26w/%D0%AD%D0%BB%D0%B5...

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

Если хочется избежать SUMIFS и COUNTIFS

Имеется простая таблица

Если хочется избежать SUMIFS и COUNTIFS Excel, Таблица, Полезное

Требуется посчитать разные комбинации, например "Север и Юг за месяц январь". Можно использовать формулы SUMIFS и COUNTIFS. Но мне они не нравятся, т.к. слишком громоздкие. Особенно если критериев много.


Поэтому часто использую следующий трюк. Работает на всех версиях Экселя, с операторами равен "=", больше ">", меньше "<", и не равен "<>". Сначала назначаем названия нашим ячейкам.


А дальше:


Счет продаж где месяц = "Январь" и регион = "Север" (Результат: 1)

{=SUM((Месяц="Январь")*(Регион="Север"))} 


Счет продаж где месяц = "Январь" и регион = "Север" или регион = "Юг" ( Результат: 2)

{=SUM((Месяц="Январь")*((Регион="Север")+(Регион="Юг")))}


Сумма продаж где месяц = "Январь" и регион = "Север" (Результат: 200)

{=SUM((Месяц="Январь")*(Регион="Север")*Продажи)}


Сумма продаж между 300 и 400 (Результат: 1350)

{=SUM((Продажи>=300)*(Продажи<=400)*(Продажи))}


Ахтунг! Формула вводится комбинацией клавиш Ctrl + Shift + Enter.

351

Таблица поверок приборов и оборудования в Эксель

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

- если до окончания поверки менее 10 дней, то ячейка "осталось дней до поверки" закрашивается желтым цветом и выскакивает сообщение;

- если срок поверки прошел, то ячейка подкрашивается красным цветом и выскакивает сообщение.

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

Как обычно залил на яндекс диск https://yadi.sk/i/LRYKx75yfHiyeA

Мыло, если есть вопросы Petrov210217@yandex.ru

Таблица поверок приборов и оборудования в Эксель Измерительные приборы, Прибор, Excel, Оборудование, Поверка
112

ВПР и прочие вычисления со сводной таблицы

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

ВПР и прочие вычисления со сводной таблицы Excel, Ms Office, Длиннопост

Параметры

ВПР и прочие вычисления со сводной таблицы Excel, Ms Office, Длиннопост

Формулы

ВПР и прочие вычисления со сводной таблицы Excel, Ms Office, Длиннопост

Убрать галочку с "использовать функции GetPivotData для ссылок в сводной таблице"

ВАЖНО:
в источнике, от куда вы берете данные для сводной таблицы, данные должны быть так же отформатированны как там, куда вы делаете ВПР, то есть либо везде числа, либо текст.

PS: не забудьте обновить данные:

ВПР и прочие вычисления со сводной таблицы Excel, Ms Office, Длиннопост
Показать полностью 2
18

VBA копировать значения в диапазоне ячеек из одной книги эксель в другую

Доброго времени суток! Вопрос к специалистам VBA.

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

(вот это:

C:\Накладные\Новая таблица.xls,

на вот так:

C:\переменная1\переменная2), а значения задать в книге1 на листе настройки.


вот код:


Sub import()

Workbooks.Open "C:\Накладные\Новая таблица.xls"

Workbooks("Новая таблица.xls").Sheets("Лист1").Range("D3:BA37").Select

Selection.Copy

Workbooks("Красный5.xlsm").Sheets("Лист1").Activate

Range("A1:BA10000").Cells(3, 3).Activate

ActiveSheet.Paste

Sheets("Лист1").Range("A1").Activate

End Sub

61

Как ссылку вставить в ячейку текстом. Настройки Excel

Ответ на комментарий sanalejandro:

"Уважаемый Весёлыйчел, есть у меня один вопрос по настройкам эксель... Поможите, если не затруднит.


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


Спасибо!"


При копировании ссылки из браузера, ее действительно можно по умолчанию вставлять как текст, а не как гиперссылку. Для этого нужно совершить буквально несколько кликов. У меня эксель 10-й, поэтому покажу на его примере, в остальных версиях логика действий должна быть похожа: "Файл"---"Параметры"---"Правописание"---"Параметры автозамены" и снять галочку с пункта "адреса Интернета и сетевые пути гиперссылками"--- ОК.


И это все! Теперь ссылки будут вставляться обычным текстом.

Как ссылку вставить в ячейку текстом. Настройки Excel Excel, Гиперссылка

Мыло для связи Petrov210217@yandex.ru

Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!