Помощь по Excel

[Lockheed]

Прежде, чем задать вопрос в этой теме, попробуйте почитать справку Microsoft Excel, часто помогает...

[SOLO]

camope3
Пример файла в студию.

[gubskymax]

Здравствуйте форумчане, нужна консультация по работе в Excel. В общем, в течение месяца заполняется таблица одного и того же типа, содержащая даты выхода из строя и наименования поломанных устройств. Уникальных наименований устройств около 200. Вопрос такой, можно ли сделать так, чтобы при появлении в течение месяца повторяющихся наименований устройств (повторения 2 и более раз), в отдельную табличку рядом выводилось автоматически название устройства и количество его поломок? Заранее спасибо за ответ)

[SOLO]

Легко: http://www.planetaexcel.ru/techniques/8/130/
https://youtu.be/WOpV9_zeB4Q

[404]

Цитата (gubskymax) »

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

Это задача на выборку уникальных значений средствами Excel (аналог SELECT DISTINCT в SQL).

Можно сделать без использования инструмента "Сводные таблицы":

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

Если нужно выбрать не просто все уникальные названия устройств, а ещё с каким-то условием ("встречается 2 и более раз" и т. п.), включите это условие в формулу "индекса", см. приложенный файл №2.

Единственная проблема — в Excel размер итоговой таблицы фиксирован. Поэтому в "лишних" строках будут стоять ошибки #Н/Д. Лечится проверкой превышения максимального номера "индекса" и т. п.

[nobana1]

Приветствую всех!
Есть файл состоит из множества одинаковых листов - названия листов - дата (04.12.2017), но не каждый день - вот такой вид (отправка почты)
Организация + Индекс + Нас. Пункт + Адрес


На одном листе изредка попадается одна орг-я 2-3 раза.
Почти все листы имеют одинаковое кол-во строк, после которой в колонке A (организация) пишется

10 ноября 2017 - дата
Главный бухгалтер


ЗАДАЧА
на отдельном листе сделать таблицу с сылками на ВСЕ листы
Фирма 1 = 124 раза (писем)
Фирма 2 = 17 раза (писем)
и т.д.

чтобы не переписывать на отдельном листе названия фирм А чтобы авто- видело - что в колонке A есть
на одном листе
Фирма 1 = 1 раз
Фирма 2 = 2 раза
+
на другом листе
Фирма 2 = 1 раза
Фирма 3 = 1 раза
Фирма 4 = 1 раза
Фирма 5 = 1 раза


и т.д.

[DarkJoney]

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

[Smirnoff]

Цитата (nobana1) »

Есть файл состоит из множества одинаковых листов

Импортировать в Access, все названные задачи там решаются на 1-2...

[nobana1]

DarkJoney
ну примерно так...

1 лист
наименование + индекс + нас.пункт + адрес
фирма 1 + 244000 + г.Светлов + ул.Светлова, 1/45 (!)
фирма 2 + 245000 + г.Светлов + ул.Цветкова, 21/5

2 лист
фирма 1 + 244000 + г.Светлов + ул.Светлова, 1-45 (!)
фирма 3 + 247000 + г.Светлов + ул.Светлая, 11/15

на отдельном листе
фирма 1 = 2 раза
фирма 2 = 1 раз
фирма 3 = 1 раз

и т.д.

Добавлено через 7 минут

Цитата (Smirnoff) »

Импортировать в Access, все названные задачи там решаются на 1-2...

при этом структура листа не должна нарушаться и легко выводиться на печать (но к сожалению на 2м пк где подключен принтер я не ставил Acces)... на моем пк есть - я его не ставил НО обновки прилетали и я ставил на 10ке все скопом

я пробую открыть из А файл E - фигня выходит... как экспортировать-импортировать - не знаю

[SOLO]

Access не нужен, сейчас excel умеет в функционал субд при помощи Power Query (для 2010 и 2013 он идёт надстройкой, с 2016 встроен изначально).
См. вложение, делается за несколько кликов мышкой.

[SOLO]

Рассказываю для 2016-го excel:
Сперва на каждом листе сделать список "умной таблицей" (встать курсором внутрь таблицы - "Вставка" - "Таблица" или "Главная" - "Форматировать как таблицу".)
Далее для каждого листа надо создать "подключение", по сути - запрос. Встать курсором в таблицу, вкладка "Данные" - "Из таблицы" - "Закрыть и загрузить" - "Закрыть и загрузить в..." - "только создать подключение" - "Загрузить". На выходе получим готовый запрос для листа. Повторить с каждым листом.
Далее идём на вкладку "Данные" - "Создать запрос" - "Объеденить запросы" - "Добавить" - "Три таблицы или более" - Добавить все доступные запросы в область "Таблицы для добавления" - "Ok" - "Группировать по" - выбираем столбец для группировки (с названием фирмы), "Операция" - Считать строки - "Ок" - "Закрыть и загрузить" - "Закрыть и загрузить в..." - "Таблица" - "Новый лист" - "Загрузить".
Готово. При изменении исходных данных надо просто обновить таблицу - "Данные" - "Обновить".

[SOLO]

Ну или так, для наглядности со сводной таблицей. Тут в качестве костыля добавлен столбец, дублирующий имя листа:

[nobana1]

SOLO
вроде получается, но
название листа 12.01.2017 - не понимает точки.

Было бы не плохо когда присваивает таблице имя которое равно имени листа!
При добавлении нового запроса ИТОГ формируется... на новом листе... ничего страшного - пред можно и удалить, но было бы проще....


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

[SOLO]

Цитата (nobana1) »

название листа 12.01.2017 - не понимает точки.

По идее должен, я же дал такие имена листам в примере.

Цитата (nobana1) »

Было бы не плохо когда присваивает таблице имя которое равно имени листа!

Так их можно вручную именовать, не проблема.

Цитата (nobana1) »

При добавлении нового запроса ИТОГ формируется... на новом листе... ничего страшного - пред можно и удалить, но было бы проще....

Тут не понял...

Цитата (nobana1) »

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

Вот тут проблематично. Сделать автодобавление новых листов в объединённые запросы и сводные таблицы штатными средствами вряд ли получится без VBA. Можно попробовать макрос наколхозить, но не факт что получится. Более правильным выходом было бы изменение структуры исходных данных (сделать одну общую умную таблицу, с выводом на печать из сводной таблицы по фильтрам/срезам) либо использовать более подходящий инструмент субд - тот же access. У вас изначально проблема в выбранном формате хранения данных, он слабо подходит под задачи последующего анализа и группировки.

[nobana1]

SOLO
тут получается вот что -

Отправляется почта с организации (БелБизнесПочта - ну как-то так) по безналу, деньги загоняются платежкой, в авансовую книгу добавляется сумма - при отправке писем к примеру 08.12.2017 деньги списываются путем заполнения соответствующего листа в АК, также копируется лист с пред отправки и вносится инфа о текущей отправке почты - несколько писем, в т.ч. ЗАКАЗНЫМИ С УВЕДОМЛЕНИЯМИ (я начал делать пометку для таких поскольку они больше всего стоят, особенно с Минска на Россию - я об этом не говорил НО не плохо бы их тоже отобразить - я в принципе знаю некоторые постоянные клиенты что именно заказными почту получают - но лучше это показать = хотя... думаю не столь важно).

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

ПОМОГИ будь добр... что-нить такое ... чуть проще чем твой вариант - мне придется обработать на сегодняшний день (08.12.2017) ... 82 листа. Сам понимаешь - кайфа очень мало!

[SOLO]

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

Добавлено через 14 минут

Есть такая надстройка платная - PLEX, там это штатная функция (обьединение листов с присвоением префикса по имени листа). Я ее покупал пару лет назад, рублей 500 обошлась.

[nobana1]

Ну вот пример.
Обрати внимание на адреса фирм 1 и 18 - они вроде бы одинаковы НО адреса "разные" - поэтому выборка по названию - потому как в адресе или индексе может быть ошибка (в начале книги на 1х листах была)

[SOLO]

https://youtu.be/olNuXGQstao
Оно?

http://www.planetaexcel.ru/plex тут можно взять эту надстройку, ну или искать vba скрипт для объединения листов, гуглится просто.

[nobana1]

SOLO
А нельзя это все формулой сцепить? типа ВПР или выборка из массива...
пока самый 1й вариант мне более понятен НО он очень долгий ...
вот еще вариант... http://www.excel-vba.ru/multex/sbor-...vknig/#wbtools

[SOLO]

Цитата (nobana1) »

А нельзя это все формулой сцепить? типа ВПР или выборка из массива...

Технически это возможно: http://www.excel-vba.ru/chto-umeet-excel/vpr-s-poiskom-po-neskolkim-listam/, но на практике это долго, сложно и бессмысленно, т.к. есть более простые пути.

Цитата (nobana1) »

вот еще вариант... http://www.excel-vba.ru/multex/sbor-...vknig/#wbtools

Ну да, это то же самое, что делает plex.

Мой совет - надо один раз заморочиться, и собрать общую таблицу из уже имеющихся данных любым их вышеуказанных способов. А для дальнейшей работы менять формат на более удобоваримый. Пусть в том же excel, но _правильный_ Почитайте хотя бы азы про реляционные базы данных. Как правильно выбирать структуру и формат таблиц, про целостность данных и избыточность данных и пр.

[nobana1]

SOLO
все это кончено интересно, НО
1. таблица на каждую дату может быть с разным кол-во строк (писем организациям)
2. любой лист должен легко быть распечатать на листе A4 - поэтому менять форму расположения элементов на листе скорее всего нельзя будет.

А для дальнейшей работы менять формат на более удобоваримый. Пусть в том же excel, но _правильный_ НАПРИМЕР?