Yamamoto was actually a very good admiral, and reasonably successful, given the restrictions under which he operated. He was diametrically opposed to the war to begin with, and knew before it was started that Japan could not hope to win. Most of the "failures" under Yamamoto were one time failures of subordinates, some of which were understandable mistakes. He accurately predicted, and promised his "superiors", that he "could run wild for 6 months, and after that, promise nothing". From Pearl Harbor to Midway was about 6 months.
Under the circumstances, Yamamoto had two choices, refuse to participate in the war and resign, which would probably have resulted in him being assassinated by Tojo and his followers, or do as he did, do the best possible job given the task assigned him and the restrictions he operated under. He was a wise and honorable man, forced into something he did not desire or believe in. The Japanese navy was far worse off without him than it was with him.